October 15, 2007 at 1:31 am
Hi,
The requirement of my application is to fetch data from a couple of tables which have millions of records. Is it possible to send the resultset from SQL batch by batch? i mean... sending first 5000 records and then the next 5000 and so on... This is because, the StoredProc takes nearly 5 minutes to fetch the data all together. So i want to split it up and append the data as they come batch by batch. Is it possible?
October 15, 2007 at 4:19 am
I don't see why not - tricky without full details - but it's an approach I've used many times when working with large sets of data. You need a parameterised proc called from within a loop, this could be in another proc.
I'd have to say that you should check your query has useful indexes first just in case this resolves your problem.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 15, 2007 at 5:28 am
this is the scene... i have to fetch from an SP which is going to return me millions of records. As obvious as it seems to be, its taking more than 2 minutes for execution. I don want the UI to wait till then to show the data on the screen. So, i want the SP to return the resultset in batches. I mean, it should send teh first 50000 first and then the next 50000 and so on... or any number of record as and when it finds a match.
October 15, 2007 at 5:35 am
I agree with colin; a master procedure to handle things in batches is a great idea....I'd question whether a user interface really needs to look at a million records, or especially to have to do something/manipulate the million records.
what is the procedure really doing? maybe there's a hole in your logic, and a simple WHERE statement could trim the list down to the records that need to be affected.
you mention "or any number of records when it finds a match"
that is exactly what SQL server can do for you; I'm thinking you are doing Row-By-Agonizing-Row processing (RBAR), and you should take advantage of SQL Server to do the matching for you instead.
Lowell
October 15, 2007 at 5:40 am
The UI is not going to look into millions of records if proper filters are provided to the SP. But if no filters are provided, the UI will have to show at least 1 million records at a time. Moreover, the tables joined have at least 5 million records. So this SP is a tiem consuming one. And i dont want the user to wait till all the matching records are fetched. I want the user to see a grid in which data gets appended as and when the SP returns data.
October 15, 2007 at 6:11 am
Apart from the technical feasibility...
As already mentioned, for a GUI to receive 1 Mio records to display in a list is less than desireable and makes no practical sense. Are the users looking for most recent entries? Then perhaps provide the most recent 1000 records or so. Providing aggregated views of the data might help as well.
We have similar scenarios on some of our GUIs and we give the users lots of filter options but a maximum number of records to be returned of a few thousand at a time. It simply does make no sense to populate a list on a GUI with more data.
If the user chooses a filter combination that yields more than the maximum number of records we do display the maximum and display a message to the effect that more such records exist but please narrow your search criteria...:)
October 15, 2007 at 6:35 am
well first thing i would do is at the top of the procedure, Id issue this command:
SET ROWCOUNT 10000
that way, no matter what filters/conditions were passed or not, i return 10,000 reocrds at a max.
the right way to do this is on the client side however.if the records areordered on an identity field for example, client side could know the highest value for that field, and just keep selecting batches of 10K WHERE id > LastMaxIdentityFromGrid
I've got a vb6 example from a search routine I could post, but it doesn't resolve the issue where you've decided a UI needs to be able to view every record (potentially), even if they are paging thru batches of 50,000 records.
what is the UI doing that the client needs to view and manipulate 50K in records?
I was just doing a dictionary clieanup project, and I had to read definitions for a batch of less than 5000 records that were incorrect....reviewing a tenth as many as you are proposing, but with a strong eye on detail, and it was difficult;
i had to make multiple passes thru the data in order to correct the items in that case. since it was a one time run, no need to handle it differently, but it sounds like you expect day to day users to view that many records, and i still have trouble with that.
Lowell
October 15, 2007 at 7:56 am
Can anyone show me a single instance of any individual walking through 1m records one at a time? Every time I've been given the requirement "we need to see all 1 million rows" upon drill down you discover that, well, they don't really want to look at 1m rows of course. They just want to know that they're there and they'll look a the first 50 or so... Makes me insane, every single time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2007 at 2:37 pm
Have you looked at the custom paging abilities of vs.net 2005? A lot of those controls (gridview formview, etc...) have built-in paging functions that won't return more than x number of records at a time.
There are also a lot of custom paging scenarios described in BOL. Try here:
http://support.microsoft.com/kb/305271
Why fetch 1M when they're not going to look at them all?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2007 at 12:35 am
HOW is shown at this blog
N 56°04'39.16"
E 12°55'05.25"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply