[thelist] ADO cursor - best solution for count and page throughprocedure

[Ken Schaefer]

Hi there,

If there aren't many records, then you are use GetRows() to get /all/ the
records, then just Response.Write() the records you need straight of the
resulting array. That avoids the second SELECT COUNT() query. If you use an
adOpenStatic cursor, you're going to pull all the records as soon as you do
a .RecordCount(), so using .GetRows() to all the records, even the ones you
don't need, isn't going to be any more expensive. (You can see all the
records being pulled if you use SQL Server and use the Profiler tool to see
what's happening under the covers).

I've done some load testing on this. Using 3 client machines, and a
dual-proc webserver. Two options:
a) using adOpenStatic + .RecordCount, and .AbsolutePage
b) using ForwardOnly/GetRows, separate Count(*) query.

For each of (a) and (b), I used the Orders table in the Northwind database,
and tried 5 pages of 20 records (100 recs total), 5 pages of 100 records
(500 recs total), and 5 pages of 160 records (800 total). I used a
randomiser to mix up the column to sort by.

the results (in requests served, per minute):

                 Using Static Cursors         Using ForwardOnly/GetRows
100 Records 4267                             5829
500 Records 3577                             5367
800 Records 2193                             3906

The small dataset resulted in a 27% drop in performance. The larger dataset,
a 44% drop in performance. One of the big killers of adOpenStatic method was
calling .RecordCount(). By omitting .RecordCount(), the number of succesful
requests usng adOpenStatic cursors, with the 100 record resultset, jumped to
6133/minute, which is just a bit higher than using
adOpenForwardOnly/GetRows - however the GetRows code also involved a second
SELECT COUNT(*) query. Even with this second query, performance is very
good.

Cheers
Ken

----- Original Message ----- 
From: "Adriano Castro" <ad at netvisao.pt>
To: <thelist at lists.evolt.org>
Sent: Wednesday, March 31, 2004 9:58 PM
Subject: [thelist] ADO cursor - best solution for count and page
throughprocedure


:
:     After reading (1) http://adopenstatic.com/faq/jetcursortypes.asp I
: asked myself which is the most appropriate cursor to use taking into
: consideration that before paging through a recordset I first want to
: count the number of entries on it.
:
:     I don't want to create two recordsets (one for COUNT(*) and another
: one with the actual data) so I'm only using one.
:
:     With a adOpenForwardOnly cursor I can't use the .recordCount property
: so I'm using GetRows() and saving it in an array. However, after
: having done that I need to page the recordset to its first entry
: through MoveFirst(). According to (1) by doing so the recordset is
: closed and the query re-executed. That can't be good...
:
:     Should I, for this purpose, use a adOpenStatic and use the
: .recordCount property? Or is the current method being used faster?
:
:     Also, if all I want to do is check if the recordset returns anything
: is "IF (recordset.EOF) THEN do_something END IF" the best thing to do?
:
:     AD
:
: --
: www.adrianocastro.net
: -- 
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !
: