Re: Need a recordcount in ADO recordset

Below is my code as it is today. What is wrong with it and what would
you suggest for speeding up the recordset code to where I can also have
a valid row qty returned?

None. They are mutually exclusive.
The fastest recordset on the block is the Server-sided, Forward-only,
read-only cursor with some unspecified cached records. It is is the fastest
_BECAUSE_ it has limited functionality and that includes the fact that it
does not know how many rows it is delivering. So RecordCount is -1 for this
cursor.

You might try server-sided cursors setting Recordset CacheSize property. I
usually use powers of 2 like 64, 128, ..., 4096. I expect the ideal size
would be NetworkPacketSize / RecordSize. CacheSize is for server-sided
cursors, it is useless for client-sided cursors

As Bob says. If you need the RecordCount, is much much faster to get the
database to calculate it for you in SQL and return a 1 record result.

And for faster performance it would not hurt to look at your WHERE clause
and see if you cannot cut the amount of rows to be joined further down. Less
rows to consider is usually faster. Looking at whether you have the right
indexes also helps (also the most useful index, usually the PK benefits if
it is clustered).

(I'm not using this right now; but would this speed things up:
"cnConn.CursorLocation = adUseClient" ? )

No, very likely not (but try it and see). All it means is that the
Recordset.Open will take longer, because ADO will spend all its time
creating the Recordset in client-sided memory. You might even find that
Windows starts virtual memory swapping as ADO will need a lot of
client-sided memory to contain the entire Recordset. Once Open() returns,
MoveFirst, MoveLast should be fast (that is if it fits in memory and not on
disk) and RecordCount will be valid.
But you will have paid an enormous price to get here. Garguatuan Recordsets
are usually better off being server-sided.

Stephen Howe


.

Relevant Pages

  • Re: DAO to ADO Recordset Options
    ... DAO was really fast when dealing with Access data (faster than ADO) but was ... Recordset. ... For client-sided cursors, there is only Static cursor type regardless as ... determines how often data is fetched from the server. ...
    (microsoft.public.data.ado)
  • Re: Pbm using dynamic cursor type with Shaped recordset
    ... when you choose Client-side cursors you limit the number and type of ... cursors available to you. ... > What I'd like to know is how to use dynamic cursors with MSDATASHAPE ... I open a recordset on a table. ...
    (microsoft.public.data.ado)
  • Re: Understanding bizarre sp_cursorfetch performance
    ... I changed to OLEDB and found that it avoid using the cursors ... read/only recordset and just grab the data. ... Profiler is showing the first fetch ...
    (microsoft.public.sqlserver.programming)
  • Re: Unable to modify a specific SQL table
    ... It's not necessarily cursors themselves that cause the ... Changing the default recordset ... read-only snapshot based form and then using a different ... >> It can happen with bound controls. ...
    (microsoft.public.sqlserver.server)
  • Re: Pbm using dynamic cursor type with Shaped recordset
    ... > cursors available to you. ... Thanks, William and Kevin. ... I've then tried to Resyncthe child recordset once the record is deleted. ... in other tables the stored procedure deletes them. ...
    (microsoft.public.data.ado)