1,195 reputation
317
bio website
location Austin, TX
age 32
visits member for 2 years, 11 months
seen 2 days ago

Feb
17
comment How can I dynamically alias columns?
"User configurable" meaning there are hundreds or thousands of types, and/or aliases being changed frequently? If the aliases are fairly stable, I'd recommend creating a series of views.
Jan
31
answered Data Modeling for different roles
Jan
29
comment Normalizing data to 3NF
You're on the right track. The next step would be to split your Disease and HealthInsurance tables. There should be a table for diseases (with the name and appropriate codes) and another for diagnoses or treatments, relating Patient, Doctor, Hospital, and Disease.
Jan
28
comment Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
Can you show us the full merge query?
Jan
20
comment In SQL Server, how do read locks work?
I'm confused. If the answer to "does the 1st query prevent the 2nd from running?" is "No", how can the answer to the second question be "Yes"? Can you clarify which questions you're answering, and expand on your answers?
Jan
16
comment Would it be better to do a sepparate query instead of a left JOIN?
MySQL is doing a table scan of glasses because you're using the whole table: both id and name. The alternative would be to seek the index a zillion times and do a row lookup for each, which won't be any faster in such a narrow table. You'll get a table scan when the system thinks that will be faster than the alternatives. Don't sweat it too much until and unless you get performance problems; just having a good normalized schema is the bigger part, and you have that.
Jan
16
comment Would it be better to do a sepparate query instead of a left JOIN?
I'm afraid that's a huge topic; there are shelves of books on the topic. I would start here: dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html.
Jan
16
comment Would it be better to do a sepparate query instead of a left JOIN?
Looks like a good normalized database. You should be fine. LEFT JOINs are not evil; I'd be more worried about table scans, and then only if a query actually does take a long time.
Jan
16
comment Would it be better to do a sepparate query instead of a left JOIN?
My comment to "try it and see" was glib, meaning more clever than helpful. I'm making the point that you can simply run the query, change it, and run it again. Which one runs faster? The less glib answer, meaning more helpful, is to analyze the query plan for your original query and see if it's efficient.
Jan
16
comment Would it be better to do a sepparate query instead of a left JOIN?
Try it and see. Less glib, what does the query plan look like? Honestly if you're querying recipes, you probably don't have enough records for it to matter much. You can often get to millions of records before you need to start second-guessing the optimizer.
Jan
15
comment General ETL principle - ETL from unknown number of tables?
You could combine the tables by taking a superset of all their fields, and later query data based on a dynamic list of columns. It would be ugly, but that's close to how NoSQL works every day, and it sure seems popular these days.
Jan
14
comment General ETL principle - ETL from unknown number of tables?
Generally it is, but there are exceptions. For example, I have a process which merges multiple CSV files and then imports them, so it accepts and arbitrary set of inputs. But they all have the same fields, it's just a big union. Can you tell us more about what the powers that be have asked for?
Jan
14
comment How can I use an ETL?
ETL is a process, not a thing. You probably have multiple ETL processes, and multiple ETL sources and destinations.
Jan
14
revised General ETL principle - ETL from unknown number of tables?
My original post errored out and got truncated
Jan
14
answered General ETL principle - ETL from unknown number of tables?
Jan
9
awarded  Nice Question
Jan
7
accepted Can I delegate SQL Agent rights without granting sysadmin rights?
Jan
7
answered Converting varbinary data to varchar in SQL (Just like converting bytearray to string using Encoding.Default.GetString in c#)
Jan
7
comment Converting varbinary data to varchar in SQL (Just like converting bytearray to string using Encoding.Default.GetString in c#)
I'm unclear. You're saying that you converted data from VARBINARY to VARCHAR, and when you try to convert it back to VARBINARY you're getting a different result? That should be a lossless process.
Dec
20
answered normalization of data and duplicate rows