Efficient recordset paging 2

[TheInsider]

Hello,
I am looking for some advice on recordset paging. I have read several examples from books, tutorials, and FAQs, but I am still unclear as to which is the best cursor/recordset type for my application.

Let's say that I have a query that is based on the parameters supplied by the client, who is doing some sort of search on my website. Now lets say the client's search returns 2000 records. I decide that I want each page to display 50 records at a time, so the client will have 40 pages available to look through.

Now suppose the client decides that, after looking through 3 pages of records, they don't want to look through any more. Is it not efficient that a client-side cursor location will return all of the 2000 records to the client anyway? The client would have to wait for 2000 records to be downloaded regardless of whether they will look through all pages or not.

Is there a way, other than using the SQL Server Stored Procedure method, to return only the records of the current page to the client?

If a website is expected to have a high volume of users, what is the most efficient way to solve this problem? Is there no better way to use ADO's built-in paging methods than with a client-side cursor and a Static or Keyset cursor? Are the high volume sites really all using custom paging methods?
Thanks, Rob Marriott

 

[Deadline]

The one I saw in learnasp.com is very good. He passes the SQL Query itself as a Querystring. It is a cool logic and works fine for me.. Thank you...
RR

 

[TheInsider]

Thank you for the reply. I had a look at that code:

http://www.learnasp.com/learn/dbtablepaged.asp

but it is still using a client-side cursor -- thus fetches all records to the client. At the top they state:

"It does not in any way store a recordset or connection in memory when the page is not accessed unlike many solutions you will read about"

This is confusing to me because it looks like all of the other standard tutorials. I can only assume that they are refering to the Server's memory.

When I wrote the above question, I had just read that you couldn't use a server-side cursor with a Keyset or Static recordset. This was misleading because I later read on Microsoft's knowledgebase that you can use a server-side cursor with SQL Server, but not with Access.

What I would like to do is:

When the client submits a search, the ASP page will open a connection to the database, and, using a server-side cursor, only return the data from the requested page. Then the connection will be closed again.

This would be slightly slower for each page request, but it would prevent the client from having to initially download the entire set of results, when they may just abondon the results after looking at a couple pages.

Most of the paging tutorials out there assume that you have a low-traffic website. I was hoping for a more efficient paging technique.

The problem with the SQL Server Paging method is having 200 people at once creating temporary tables to hold query results -- this doesn't sound good either.

Does anyone know of any other server-side paging methods?
Thanks, Rob Marriott

 

[SauerC]

the page on LearnAsp.com does not fetch all the records to the client ... it's doing exactly what you are looking for.

just because of

rstemp.cursorlocation=aduseclient

doesn't mean that the entire recordset is being served to the browser.

 

[TheInsider]

Ok... thanks for the information. I will have to look into this more. I was under the impression that a client-side cursor returned all records, and then you would use the AbsolutePage method to filter them. I guess this explains why there aren't any examples out there using server-side cursors.
Thanks again, Rob Marriott