What's the best way to temporarily persist results of a long running SP?

Question

0.00/5 (No votes)

See more:

, +

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

Trekstuff

Add a Solution

Comments

skydger 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 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ankur^\m/ · Answer 1 · 2012-11-22T20:34:00

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. :)

I would say search Google for 'sql server indexes' and '.net caching' to start with.

Hope this gives you an idea.