Re: ADO recordset movement in Oracle DB
- From: "Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom>
- Date: Mon, 24 Oct 2005 18:39:56 +0100
> My query executes and loads the Recordset in about 10 seconds...
What do you think the Recordset consists of?
It is a server-sided cursor, it may consist of 1 record - the size of the
Cache - the rest of the records will still take time to deliver. If this is
a server-sided cursor, Open() may return immediately - precisely because of
the 1 record. Or it maybe there is no records, you still at BOF and a
MoveFirst is required.
>..., but it takes
> 3 minutes to move to the next record the first time I try to move. After
> that, it moves quickly as normal, but if I try to MoveLast/Move First or
even
> check the record count I have at least a 3 minute wait.
> Why is this taking so long when the query executes so quickly and how can
I
> fix the problem to move through the recordset quicker?
Understand what is going on under the hood first with server-sided and
client-sided cursors.
With a client-sided cursor, you will find that RecordSet Open() takes a lot
of time.
But once it completes, doing MoveFirst()/MoveLast()/RecordCount() is
virtually instantaneous.
That is because with a client-sided cursor, ADO builds the entire Recordset
in client memory in the Open() call. Open() won't return until the last
record is built in memory. But once built, traversing the recordset is
virtually instantaneous, because you are traversing client memory. With the
right ADO parameters, you can disconnect the Recordset from the database. It
also costs in terms of client memory.
With a server-sided cursor, you will find that RecordSet Open() returns
reasonably quick.
That is because only CacheSize records, an ADO property, if that, are
delivered back from the database. CacheSize defaults to 1. CacheSize is not
that important for client-sided cursors, but it is very important for
server-sided cursors. If the CacheSize was say 16, then records are
delivered in batches of 16 and MoveNext() will move through the cache 16
times and the cache will be replenished when you get to the 16th record.
Note also, if you have a Server-sided cursor, ForwardOnly - you should only
do MoveFirst initially if at BOF. MoveFirst may not be required as the
cursor position could be the 1st record. I hav read that in some cases, a
MoveFirst may cause the query to be re-executed a 2nd time just to move the
cursor to the start. So, for Server-sided cursor, ForwardOnly, this snippet
does the right thing
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
rs.Open()
IF rs.BOF AND NOT rs.EOF THEN
'Have some records and also at BOF
rs.MoveFirst()
END IF
'Should be on 1st record for all databases at this point
'(or possibly no records at all if EOF is already true).
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
See last point here which explains this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthmovefirst.asp
So in summary, server-sided recordset Open's may appear to have less latency
than client-sided Open()'s because Open returns quicker, but if you had an
application which printed all records and you timed it from the point when
Open() first executes to the last record seen, you should find server-sided
and client-sided are in the same ball park.
And set CacheSize for server-sided cursors before the Open().
I typically use powers of 2: 64, 128, 256, 512
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocachesize.asp
A few other things:
(i) You want to examine your Oracle SQL.
Is it efficient? Could a a few indexs on key columns help?
If the query involves many JOINs, then it will take time.
(ii) If all you need to do is finding the number of records in a table
SELECT COUNT(*) FROM yourtable
is much cheaper than counting many records in a Recordset.
(iii) RecordCount will return -1 for some Cursor Types
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp
(iv) Use the cheapest Recordset you need for your purposes. It tends to be
faster and smaller in terms of memory.
Not updating the recordset? Then open with LockType as ReadOnly.
Don't need MovePrevious()? Then a ForwardOnly cursor might be right.
(v) Server-sided, ForwardOnly,ReadOnly tends to be the fastest cursor but it
is most limited.
RecordCount will return -1, you cannot do a MovePrevious and a few other
things.
It is most useful to read small tables into Arrays for lookup purposes.
(vi) Be aware that ADO may change CursorType, LockType into something else
if combination requested is not available. So print CursorType, LockType
_AFTER_ a successful Open() to see what ADO gave you. Some databases don't
support all cursor types or support them only in limited circumstances.
(vii) Recordset Properties like Filter, Find, Sort work best with
Client-sided cursors as all the records are in memory.
Stephen Howe
.
- Prev by Date: Multiple-step operation generated errors. Check each status value.
- Next by Date: Using IN Operator in ADO Rs.Filter ?
- Previous by thread: Multiple-step operation generated errors. Check each status value.
- Next by thread: Using IN Operator in ADO Rs.Filter ?
- Index(es):
Relevant Pages
|
|
