Pulling small subset of data from large source in sorted order.

Hello,

Im having a large performance problem and I need to rewrite a very important part of my front end.

Heres my problem.


I pull a set of data... say the top 50 records of a 500,000 record table. once the user scrolls through that top 50 via a listview control it re-polls the database to get the NEXT 50 records... (should the user decide then to go back UP the program queiries again for the 50 records before the current 50)

What I do when I have these chunks of data is just completley reload the list view and highlight the first or last record (Depending on what direction the user is "Scrolling"

THe problem is the dataset has to be sorted by whatever criteria the user has selected... SO.. what I do is simple (Yet horribly slow)

I do the query based on the sort, then I put that data into a temp table so I can have the id of the record. so I can mathmaticly sort through that.

I.E. if I had a table with 10 records numbered 1..10 but sorted on say last name It could not go in numerical order.

tblData
UserID | Name | LName
-----------------------
1 | John | Williams
2 | Jessica | Bryan
3 | Bethany | Jackson
etc etc


So the sort could be... by last name the ID's would be in 2,3,1 or by first name 3,2,1 or by ID 1,2,3 or whatever.

So what my temp table does is provide me a reference point that I can say..

Give me the top 20 records where the TEMPTable ID is less than... whatever. and that gives me the results.

But like I said it is horribly horribly slow as every time it does this it has to sort a 500,000 record table.. put it in the temp table (Even though I only use a created identy column and the PK identity from the source table) and THEN pull the data for ID whatever to whatever.

Is there a simpler way and MUCH MUCH more efficient way to do this? I tried throwing ram at the SQL server (It now has 4 gigs in it) but that didnt even seem to help in the least.

I'm pretty desperate here.

I may not be fully understanding the problem, but wouldn't it be "more efficient" to return a sorted recordset, then simply move to the new start position, then loop thru say 50 records to display them on a listview. The recordset won't need to be re-sorted & you are just navigating through the recordset. Or did I misunderstand.

I may not be fully understanding the problem, but wouldn't it be "more efficient" to return a sorted recordset, then simply move to the new start position, then loop thru say 50 records to display them on a listview. The recordset won't need to be re-sorted & you are just navigating through the recordset. Or did I misunderstand.

That would be a HUGE recordset.. on the order of having 500,000 records in it.

True, but in my opinion it is better to poll it once, than polling it dozens of times. No? Aren't you doing that anyway? Sorting recordset & returning top 50? Might be worth testing and comparing the overall time it takes from start to finish from getting the initial 50 then scrolling through 500 records. Just a thought

I doubt anyone would need to manually look at 500,000 records, so why not just select the top, say 5000 and the last 5000. Adjust the grid to the appropriate recordset when they select whether or not to look at the top or bottom. Maybe even the Mean(Median, Middle, whatever) set too. However, now this is getting into more of a report than an on-screen view of data.

I doubt too, but they really do want the ability to scroll from the first to the very last record. I argued against it cause I didn't think it was practical. but in the end I lost that battle.

So you are consistently re-loading the database and re-populating the list as the user scrolls down? There is no fast or efficient way to do this. And, what if the user CONTINUES to scroll down? In the end, you are loading all 500,000 records then, correct? So, since you have NO idea how far down the user will go, and you are giving them COMPLETE scroll freedom, I don't see the point of your strategy.

How about this. At first have an option box or a heading on your grid. With this grid, have several options. You can create a very nice, GUI interface for record selection:

1) SELECT TOP 100, sort by 'x'
2) SELECT TOP 500, sort by 'x'
3) SELECT * RECORDS
4) SELECT ALL RECORDS BETWEEN <Date Box 1> and <Date Box 2>

I have done projects like this...basically give the user the ability to select their own result set. Then, based on their selection, you know EXACTLY what they want to see..then fire the appropriate query and load the records. See, there is no point in continuously adding records as the user scrolls..this will always be slow and look clunky. If the user selects what they want before hand, the population is clean and quicker. Then, if they decide to CHANGE their query, clean out the list, and re-populate again based off their new conditions, etc.