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 |
|
ramdas
Posting Yak Master
181 Posts |
 Posted - 2002-09-23 : 10:37:31
|
| Hi Folks,I would like to get some thoughts on how to Drill down through a large data set roughly 10 mill rows. The situation is initially there will be 10 mill rows, then when the user applies a restriction I want to be able to return the result set with the restrictions. This process continues till the user has drilled down to the level desired.In short: The Query Q1 grabs all the data and gives the result (R1).Query Q2 with intersection of R1 gives a result set (say R2).R2 with intersection of Q3 gives R3 and so on.Any ideas....ByeRamdas NarayananSQL Server DBA |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-23 : 11:16:30
|
| There are as many ways to do this job as there are designers.Is performance such a problem with this table that you couldn't just extend the WHERE clause on each subsequent query to refine the search?SamC |
 |
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2002-09-23 : 11:21:17
|
| Hi,I m looking at storing the initial data set into a global temporary table. The on each drill down apply the where clause on the global temp table. The issue is whether such a startegy would give good performance with over 10 Million rowsByeRamdasRamdas NarayananSQL Server DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-23 : 11:27:08
|
| Just to clarify, is all of the data in one table, or is it some kind of hierarchy, such as an account...orders/sales...order details structure? Something like the pubs or Northwind database structures? If it's one table then the term "drill down" is a little misleading (for me anyway); "refining" the search condition may be more accurate.In any case, you need to reconsider the initial query of 10 million rows...you will never get good performance trying to return that many rows in a result, with or without paging records. There should be some kind of initial search condition that returns only a much smaller subset of the 10 million. Then the WHERE clause can be refined as SamC suggested. |
|
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2002-09-23 : 12:39:31
|
| Hi,Thank you for suggestion. The data is in one table. The user wants to be able to restrict the result set based on a filter criteria which the user will provide after he/she views the result set coming back. Thank youByeRamdas NarayananSQL Server DBA |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-09-23 : 14:36:54
|
| Can you identify the characteristics that differentiate each record.For example if the records have date entries maybe you could show records between choosen date ranges.You could also figure out all the commonalities and then offer filters based on all the possible combinations.ex:Body Color | Wheel SizeBlue 10Red 10Black 9............and so onYou could query each field and get the unique values.Color - Blue, Red, BlackSize - 10, 9 |
|
|
|
|
|
|
|
|
Drill Down Strategies