|
Bill Asks:
When paging through a recordset, does the entirety of the query
get called to the Web server each time and then only the appropriate records
for the pagesize and absolute page are displayed? Or is it more efficient
than that? Is there a better way to page through multiple recordsets?
Trying to optimize things a bit and wanted to be clear on just what the
recordset was doing.
Paul Responds:
Yes, ado isn't too bright about paging...you get the whole recordset. If
you're using sql server you can do it with a temp table and only bring the
records you need with each page.
Try something like....
CREATE PROCEDURE PageSet
@PageSize integer,
@PageNumber integer
AS
SET NOCOUNT ON
CREATE TABLE #SearchResults(
result_number integer identity(1,1),
result_id integer)
SELECT [the pk of the table you want to page]
FROM ....
WHERE....
INSERT #SearchResults(result_id)
DECLARE @StartRecord int, @EndRecord int
SET @StartRecord = (((@PageNumber - 1) * @PageSize) + 1)
SET @EndRecord = (@StartRecord + @PageSize - 1)
SELECT SR.result_id...
FROM #SearchResults SR JOIN [the table to page]
ON SR.result_id = [the table to page].[the table to page's pk]
WHERE (SR.result_number >= @StartRecord) AND (SR.result_number <=
@EndRecord)
DROP TABLE #SearchResults
Michael R. Adds:
Easiest way: Re-Query the database each time, paging to the appropriate page.
Client-Side way: pass the data to the client (getstring / split) and go through the client.
Session way: Set the session timeout to a smaller 5 min or so, and register the
ADO free-threaded component, (vs. the both threaded),
This means ADO will only support free-threaded drivers (No Access).
Then you can use a disconnected recordset object in a session safely.
The main reason to not use ado is because the default is both-threaded, which is serialized in use.
The reason it is both threaded is to support single-apartment threaded drivers (Jet/Access, and a few others), if the driver being used is free-threaded (SQL, Oracle, and others), using the free-threaded ado dll won't serialize when used in asp.
David Suggests Something Else:
You could also persist the data to XML and page that.
Rob Shares His Thoughts:
store a recordset object in the ASP Session and your doomed to
fail.....kiss server scaleability (and your ass) goodbye!!!
BUT if it is limited to an intRAnet, you might be able to get away with it.
AND if you have your own custom session manager which is scaleable, you
could easily persist the disconnected recordset to ADTG (or XML) and
persist using that format within your session manager.
here's some resources:
http://www.aspfree.com/devlinks/search.asp?catid=123&file404=#Recordset_Paging
Steve Likes Java:
surprised no one mentioned this 4guys article.... well, that i saw anyway
8-)
http://www.4guysfromrolla.com/webtech/050901-1.shtml
Java client-side pagination, it is pretty slick!
Ben Tries Paul's Solution:
It works great, I have one question though. This method works well, until I start
dealing w/ recordset of over 50,000 records, then it takes up to 5 seconds
just to execute the query because it has to build the temp table each time.
Is there a way to speed this up, or to fill the temp table just with the
section of records I am working with? Has anyone tried to implement paging
on the server side with large recordsets? Any help would be greatly
appreciated.
Paul:
Possibly...firstly you could do SELECT TOP to always limit it to the maximum
possible records (PageSize * PageNumber). You could also set up triggers on
the table(s) to be paged to pre-emptively build an aggregate table with a
consecutive identity (an index, which would speed it up even more) to page
with instead of creating the temp table with every page request...
Rob Goes with XML:
If you want to get this crazy (which are very very excellent ideas), you
could also consider caching (and refreshing that cache) to disk each set of
X number of records in ADTG/XML format. If done in XML format, a simple
XSL would transform to display properly...This recognize some serious
performance improvements.
Ken Runs Some Tests
The stored procedure method doesn't seem to be scalable at all. The adOpenStatic/.AbsolutePage method does scale linearly, but it's very slow.
Fastest, if you're using SQL Server, is .getRows(numRecs) where you pull only numRecs records into the VBScript array (rather than the whole recordset).
eg .getRows(20) will give you a VBScript array with only 20 records in it.
Results here:
http://www.adOpenStatic.com/experiments/recordsetpaging.asp.
15Secs Editor Offers:
Also see this article on 15Seconds:
Paging: Use ADO, getrows, or a Stored Procedure?
http://www.15seconds.com/Issue/010308.htm
This conversation string was taken from the 15Seconds ASP Listserv on 6/6/01. If you have an ASP-related question or would like to share some of your knowledge with others, you may join the list by clicking here.
|
