Very large Dataset - Paging??

[LFCfan]

Hi

I need to query a SQL Server 2000 database and retrieve possibly a million (or even millions of) records, and then "do something" for a number of these records at a time.

i'm trying to figure out the most efficient way to do this:
- connect to the DB numerous times in a loop, and each time retrieve, say, 10,000 records?
- connect just once and bring back a huge dataset?

Is there some way of paging a large result so the network doesnt get a ridiculous amount of traffic in one go?

Any thoughts/pointers/links would be very much appreciated, i am completely stumped despite extensive googling. I've not got that much experience, so i beg forgiveness in advance for any stupidity...
Please let me know if i'm being totally unclear and you need more info!

TIA

 

[TerraSamba]

I am pretty much used to work with big datasets, but my experience is that once your set is bigger than, lets say a number, 25000 records the result is useless, your user will not find anything in such a big set.

The best you do in my opninion is to have some sort of resume of the result on which the user a sequential more detailed set. An other option is to prompt the user to supply more criteria when the recount > x

The gap between theory and practice is not as wide in theory as it is in practice.

 

[chiph]

I would suggest you follow up on this question in the MS Sql Server forum.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[sunaj]

Hi,

Here some additional hits...
The common way to do paging in T_SQL is to have an identity column, SELECT the data ORDERed by that column and then add a WHERE clause with 2 variables that define the interval of IDs that you want to select (so when you select the next 'page' you increase the upper and lower boundary variables with the page size).
If your intension is to show the data in a datagrid, the

http://support.microsoft.com/default.aspx?scid=kb;en-us;305271

solution actually works suprisingly well...
Good luck

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[LFCfan]

Dear All,

thanks very much for your responses
It looks like I will be going with a variation of sunaj's suggestion, ie:

bring back datasets of about 25000 records to the datagrid in one go (displaying 10 records per page with the in-built datagrid features), and sorting out the pagenumbers so that they look like there's more pages there. Then when a pagenumber is selected that no records "exist" for, re-connect to the DB and pull out the relevant 25000 records

I havent thought this through properly, but i think that's the road i'll be travelling down.

Again, thanks to all for your help

PS: chiph - your suggestion to follow this up with the SQL Server forum: why do you think it might be helpful? Are there any tricks for this sort of thing on the SQLS side, rather than the VB side? Or do you just mean, "someone there is bound to have had to deal with this sort of issue before, and might have some good suggestions"?

Thanks (yet again!)

 

[sunaj]

Ifcfan
I'm quite sure that the guys in the SQL forum will point you toward the solution that I outlined above. But it might be worth it anyway.

If you end up with a nice 'closed' (class?) solution - could you post it here?

Thx,



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'