Image of Navigational Map linked to Home / Contents / Search Some Fun with GetRows

by Ross Mack - GUI Computing
Image of Line Break

Background

I had been struggling with some code that used the GetRows method to return a some recordsets out of a DLL and, to my surprise, which worked quite slowly. This didn't seem right as GetRows can actually perform quite quickly... so what's the story? Well, it turns out that it all comes down to what sort of locking and Cursor you are using, as my testing highlights.

Method

Here are some timings using the GetRows method on ADO 2.0 Recordset objects (I have found that Getrows speed is very similar in MDAC 1.5 and 2.0).

The GetRows call was performed on a recordset of 5 columns and 13600 rows read from a SQL 6.5 backend. The only parameter to GetRows was -1, meaning fetch all rows.

The actual code used is included below the table of results.

As you can see from the code the timing was only to the closest second. This should be accurate enough to be indicative.

Conclusions

The obvious conclusion to make here is that if you need to do a GetRows DO NOT use Static or Keyset cursors. A ForwardOnly (or unspecified) cursor is optimal and it seems that Optimistic Locking is marginally more efficient than Unspecified or ReadOnly Locking.

Dynamic Cursors are just mediocre and should probably be avoided if a ForwardOnly or Unspecified cursor can be used. These times should also indicate that if your code otherwise needs to Use a Dynamic, Static or Keyset cursor it is actually more efficient to open a new recordset for the GetRows than to use the existing Recordset, certainly for reasonably large recordsets.

The Results
Cursor TypeLocking TypeTime taken to perform GetRows
ForwardOnlyOptimistic0:02
UnspecifiedOptimistic0:02
ForwardOnlyUnspecified0:03
ForwardOnlyReadOnly0:03
UnspecifiedUnspecified0:03
UnspecifiedReadOnly0:03
DynamicUnspecified0:30
DynamicOptimistic0:30
DynamicReadOnly0:32
StaticReadOnly0:57
KeySetOptimistic0:58
KeySetReadOnly1:01
StaticUnspecified1:09
StaticOptimistic1:10
KeySetUnspecified1:11


The Code

   Dim Conn As New Connection
   Dim rs As New Recordset
   Dim v As Variant
   
   Conn.Open "DRIVER={SQL Server};SERVER=BUSDEVELOP;DATABASE=dbVD;UID=sa;PWD=;"
   rs.Open "SELECT * FROM refTownSuburb", Conn, adOpenStatic, adLockOptimistic
   Debug.Print Now
   v = rs.GetRows(-1)
   Debug.Print Now
   rs.Close
   Set rs = Nothing
   Conn.Close
   Set Conn = Nothing
As you can see the code used to do the testing was pretty rudimentary but to measure the large differences in timings I was seeing it worked fine.

SQL Server 7.0 Beta 3

The timings on SQL Server 7 (Beta 3) vary somewhat. These times were on a recordset of a few hundred rows.

Three comments can be made about the performance of adForwardOnly cursors.

  1. They are by far the best performers for GetRows.
  2. With small recordsets it is more efficient to use Optimistic or Pessimistic locking.
  3. With larger recordsets (>500 rows roughly) I have actually observed that a ReadOnly lock is more efficient.
Time(ms)Cursor TypeLock Type
181adOpenForwardOnlyadLockReadOnly
20adOpenForwardOnlyadLockPessimistic
20adOpenForwardOnlyadLockOptimistic
4446adOpenKeysetadLockReadOnly
4587adOpenKeysetadLockPessimistic
5077adOpenKeysetadLockOptimistic
2153adOpenDynamicadLockReadOnly
2203adOpenDynamicadLockPessimistic
2374adOpenDynamicadLockOptimistic
4797adOpenStaticadLockReadOnly
6128adOpenStaticadLockPessimistic
5488adOpenStaticadLockOptimistic

I think as much as anything it is important to remember that those 3rd and 4th parameters in the open method of the recordset object ARE importnat and that you should think about how you use them. In some cases I have managed to reduce execution times by minutes bu adjusting the recordset type or lock type used.

Also, if you are using ADO 2.0 and SQL Server 7.0 seriously consider using the SQL Server OLEDB provider rather than the generic ODBC provider. You should see a performance increase result from that change also. In fact the performacve increase for large recordsets can be remarkable.



Written by: Ross Mack
October '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]