Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sdkramer
Starting Member
4 Posts |
 Posted - 2005-10-01 : 17:35:14
|
| Okay I'm working with a bunch of data coming back from a table. Here's how the table was designed. (GroupID, DateTime, Variable, Value)there are about 320,000 records in the table like this. There are 10 or so variables I'm trying to get values for from a single group/datetime. I tried doing one query with several subqueries, and that timed out. So I made 10 views of each of the variables, and joined on datetime, and groupID. This works, but I'm wondering if it's the best way to do this? I'm familiar with SQL, but I'm not familiar with best practices, etc, and (what are to me) large datasets.Thanks any help or thoughts are appreciated,Seth |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-01 : 18:24:47
|
| The view approach is very good.Instead of one "complex" table you now have 10 or so "simple" ones, albeit derived tables (views).For each group is there an entry for every variable ?The table you described (GroupID, DateTime, Variable, Value) is what I would call a "dynamic" design,whnever you need a new variable, you just add a row in the variables table.However imo this design is not very good (generally). Specially if the variables are relatively static(i.e. you don't add remove any very often).The alternatives would be to havea) one big table with one column for each variable. (add a variable -> add a column)orb) one table for each variable. (add a variable -> add a table)What you have done logically with the 10 views, is to transform the original table into design b)If you join all the views, or use your subquery approach you are transforming the original design into a)------------------------------------------------------------------------------You might be interested in comparing the execution plans of your 2 approaches.Type both queries in QA(Query Analyzer) and press Ctrl-L (Query->Display Estimated Execution Plan)You will now see a graphic plan of how sql server thinks it will execute the 2 queries, and show estimated % of cost.It will be interesting to compare the amount (and what kind) of work the server will do to evaluate your 2 approaches. |
 |
|
|
sdkramer
Starting Member
4 Posts |
Posted - 2005-10-01 : 18:35:37
|
| I should have mentioned two caveats. 1) Modification of the table I described is something I can't do because it's a commercial off the shelf product. 2) I'm not sure if I can use a stored procedure (the data is being passed back to Crystal (version 6) and I'm not sure it will report stored procedure data.I'll look into this and post something more back very soon.Thanks, |
|
|
|
sdkramer
Starting Member
4 Posts |
Posted - 2005-10-01 : 20:38:39
|
| Nevermind. I think this as good as it's going to get. |
|
|
|
|
|
|
|
|
the best way?