Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: time performance TSQL Data , +
I have a TSQL stored procedure that can run for a few minutes and return a few million records, I need to display that data in an ASP.NET Grid (Infragistics WebDataGrid to be precise). Obviously I don't want return all data at once and need to setup some kind of paging options - every time user selects another page - another portion of data is loaded from the DB. But I can't run the SP every time new page is requested - it would take too much time.
 
What would be the best way to persist data from the SP, so when user selects a new page - new data portion would be loaded by a simple SELECT... WHERE from that temp data storage?
Posted 12-Mar-12 10:36am
Comments
skydger at 17-Sep-12 14:18pm
   
Have you considered to split data in that huge table to minor portions of data (100k - 300k of records) into global temporary indexed tables?
Trekstuff at 17-Sep-12 16:46pm
   
Actually the solution I ended up with: Initial query returns IDs only - all few million of records, but a single field - primary key only. In .NET code they're read via DataReader (which is very fast) and stored in generic list. in the grid when I request a current page of data I pass just the ids needed for current page and a query returns subset of data based on those IDs - which is pretty fast too.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Some suggestions:
You may want to review the indexes on the tables used if the select is too slow. With proper indexes, even millions of records are returned in a few seconds.
 
Secondly, you may want to reduce the amount of data that is returned if that is to be displayed to the user. As you already said, a good paging mechanism will help you out.
 
Also, the SQL server caches the query plan and consecutive selects should be quite fast. If that is not happening, you should review query plans and see if there is something wrong. Actually do check the query plan as they suggest you about the missing indexes. Review the suggestions and if required implement them.
 
You are talking about some way to temporary persist the data somewhere. It is called Caching. There are lots of factor which determine your application caching mechanism for example - how much data is returned by the database, freshness of the data (frequent edits/updates), time for the data should be cached and so on.
For example if you have a very powerful web server (or a web farm) and considering the edits/updates on the data is not very high, I would get all the records at one go and keep them in the cache and select it from there. Reads from cache is lightning fast!
 
The topic is huge and takes a lot of time and research to excel. Smile | :)
 
I would say search Google for 'sql server indexes' and '.net caching' to start with.
 
Hope this gives you an idea.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 363
1 Sergey Alexandrovich Kryukov 286
2 RyanDev 75
3 nv3 60
4 PhilLenoir 60
0 Sergey Alexandrovich Kryukov 6,691
1 OriginalGriff 6,119
2 CPallini 2,473
3 Richard MacCutchan 1,697
4 Abhinav S 1,560


Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 23 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100