Need a recordcount in ADO recordset

Need a recordcount in ADO recordset

Post by TechBo » Fri, 01 Sep 2006 03:41:19


Hello.
I have the following code that connects to an Oracle database and pulls
records from 3 tables via complex SQL code. These tables are very
large and only a portion of rows from each are pulled for the result.

One of the system requirements is that I need to get a recordcount of
rows in this recordset from the SQL call & output that value to a text
log file later.
The code runs and returns approx 570k rows when I run the SQL code in
an external Oracle utility. In my code though, just doing a
"rs.recordcount" in the debug window yields "-1", not the real row
quantity.
The problem comes when I try to get the recordcount in the recordset
via a movefirst/movelast. The "movelast" statement takes about 4-8
minutes to execute!

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?

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

Thanks for your help.

<begin code>
Public Sub ADOConnectForOracle()
Dim cnConn As ADODB.Connection
Dim rsTemp As ADODB.Recordset
Dim strDatabase As String
Dim strLogin As String
Dim strConn As String
Dim strMsg As String
Dim strSQL As String
Dim strPass As String


strDatabase = "<myOracleDBName>"
strLogin = "<myOracleDBLogIn>"
strPass = "<MyOracleDBpwd>"

Set rsTemp = New ADODB.Recordset
Set cnConn = New ADODB.Connection
strConn = "Provider=MSDAORA.1; " & "Password= " & strPass & "; User
ID= " &
strLogin & " ;Data Source=" & strDatabase & ";
Persist Security Info=True"
cnConn.ConnectionString = strConn
cnConn.Open
strSQL = "<SQL Code that works from three very large tables>"
rsTemp.ActiveConnection = cnConn
rsTemp.Open strSQL, cnConn, adOpenKeyset, adLockOptimistic

'.......yields "-1" here, not the real row count
Debug.Print "rsTemp recordcount = " & rsTemp.Recordcount

'rsTemp.MoveFirst
'rsTemp.MoveLast 'this takes forever to execute

rsTemp.Close
Set rsTemp = Nothing

End Sub
<end code>
 
 
 

Need a recordcount in ADO recordset

Post by Bob Barrow » Fri, 01 Sep 2006 03:56:58


Do you need to do anything with the actual data? or is your goal simply
to get a count? if so,

strSQL = "Select count(*) FROM " & _
"(<SQL Code that works from three very large tables>) as data"


http://www.yqcomputer.com/



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 
 
 

Need a recordcount in ADO recordset

Post by Stephen Ho » Fri, 01 Sep 2006 22:37:43

> Below is my code as it is today. What is wrong with it and what would

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).


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
 
 
 

Need a recordcount in ADO recordset

Post by RN » Sat, 02 Sep 2006 02:18:10

Bob,
"Select count(*) FROM tablename" would work very nicely if I was just
retreiving data against one table. But the actual queries are more
complicated.
I suppose I should have provided a little background first. I
apologize for not doing so earlier.

There are 10 queries (which have all been optimized for
performance,etc) and the results are what the user wants. Some queries
have as many as 9 tables joined. The longest any of them runs is about
3 minutes via an external Oracle utility "Toad". Today the user runs
these all manually in Toad, exports the data form all of the queries
manually to Excel files, and reformats the Excel files to pass along to
other depts.
They want to do some reporting on some other queries via a simple
Access GUI (for a single user) The user is simply wanting to
centralize these queries with a simple GUI and have a form to launch
and run the queries.
User also requested a run log of these queries showing the start/end
times and the number of rows retrieved for each query.

One question that comes to mind, would DAO be faster and/or more
efficient when Access is going against an Oracle database?
I'm neutral either way on that issue, but would favor whichever you
feel would be the better solution technically.

Thanks.
 
 
 

Need a recordcount in ADO recordset

Post by Bob Barrow » Sat, 02 Sep 2006 02:32:44


It will also work with a subquery. Why don't you take 20 seconds and try
it in Toad?

Select count(*) from
(<your complicated query here> ) as q


In fact, couldn't you create a View using that complicated query?


So are you doing this in an Access VBA module? Are you using Access
reports? Passthrough queries?


Not at all. When working against a Jet database, DAO can outperform ADO.
But ADO will probably be better when running against an ODBC database.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.