June 11, 2003 at 10:05 am
What is a good way to implement paging of records in a stored procedure?
June 11, 2003 at 10:49 am
Use some key, order by the key, select the top XX where you are > last key.
Steve Jones
June 11, 2003 at 11:14 am
that won't work.
that will only work with a temporary table. Don't want that because we will be getting about 100 page hits/sec. Too much temp. table creation.
June 11, 2003 at 11:20 am
You might consider caching the dataset returned and using logic in your web page/ COM component to handle the paging...
Just a thought,
Jay
June 11, 2003 at 11:29 am
what if the recordset is very large?
what if the user chooses a record on the 1st page...thus caching is useless and not necessary.
We are dealing with about 50 calls/minute here.
June 11, 2003 at 11:34 am
If you are dealing with 50 calls/minute and returning very large datasets, I would imagine that paging will be the least of your concerns. I'm not quite sure what you mean by:
quote:
what if the user chooses a record on the 1st page...thus caching is useless and not necessary.
You could cache the object in some sort of session state that the user could return to through the use of a "Back to Search Results" type of function which would retrieve the cached search results or report and display the page that the user had clicked a record. Could you elaborate on the type of server technology you are using? There may be some folks out there that have specific experience in your area.
June 11, 2003 at 11:54 am
the reason for caching is to accomodate large data sets.
June 11, 2003 at 2:09 pm
I would agree with Steve...
Something like this:
SELECT TOP 10 * FROM MyTable WITH (NOLOCK) WHERE Trans_ID > @LastID ORDER BY Trans_ID
You can save some CPU cycles and drop the "order by" if you have a clustered index on the "Trans_ID" column. You may have to use the OPTION(MAXDOP 1) clause in order to ensure your results are in the proper order if you have a multi-CPU machine. To go backward you would just alter the > < signs and pass the first ID on the page. I tested this on a box under moderate load with a table containing 154,645,639 rows and it took < 15ms and only executed 5 read operations. If you don't have a sequential primary key/index, then this option may not be possible.
I'm not sure what you're talking about with the temp table. If you have to use one for some reason, then use a table var. (SQL 2000 only), it is much faster than a #table.
If this isn't what you're looking for, maybe we could get a few more details about the situation.
-Dan
-Dan
June 11, 2003 at 4:34 pm
I agree with Steve. Recently had to alter stored procedures to use paging because the amount of information return was in the millions of rows for a generic catch-all user account for our billing database. Use a key value or create one from fields being returned. Have the front-end capture the last key in the TOP XXX recordset you sent. and On the page-down request the front end sends you the last key value sent and you use a TOP XXXX ..... WHERE >@LastValue.
This method can also be used with a forward backward indicator that lets the user page down or up. If up use the first key value of the latest record set and do a TOP XXXX Where <@LastSent.
I know there is some page hits from this method but the performance is alot better than trying to send thousands or millions of records across a network and caching them in a desktop.
June 11, 2003 at 5:13 pm
jpipes maybe onto something. Can do asynch pre fetch for the next and prev 10 records.
When user click next, check if you have the next buffer. If Yes display and init next fetch for next 10. If not then fetch and display and init asynch fetch for next 10.
End of day still realtime data.
As far as the sp is concerned, Also agree with dj_meier.
Question how often do the data change?
June 12, 2003 at 6:06 am
check out the article here: there are some discussons and examples of how this can be done.
http://www.sqlservercentral.com/columnists/jwiner/moreonreturningasubsetofarecordset_1.asp
June 12, 2003 at 9:42 am
Here is a great article on the subject... http://www.aspfaq.com/show.asp?id=2120
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply