[thelist] Fw: A Little Query Question

[Joshua Olson]

> -----Original Message-----
> From: VOLKAN ÖZÇELIK
> Sent: Thursday, December 08, 2005 1:42 AM

> I once had done pagination using a temporary table and a 
> stored procedure.
> It worked pretty well.
> However, it's impact should be investigated on a high-load 
> environment since you may need to use cursors in some 
> situations (we did but cannot remember the reason). 
> And you are creating and dropping a (temporary) table.
> Which may affect performance.

As can be expected, a better answer has been provided to me by Rudy.  He
pointed me to this link:

http://rosca.net/writing/articles/serverside_paging.asp

I'll summarize in bullets:

1.  Pagination CAN be done in one query and without temporary tables.
2.  This pagination DOES require a database that can handle subselects.
3.  The pagination built into the ADO Recordset object isn't very efficient
for large record sets because it relies on client-side cursors.
4.  Both of these are vital to efficient paging: having an index on the sort
order used during pagination and having a single field primary key.
5.  A generalized stored procedure is provided to assist with paging.

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/