by Ross Mack - GUI Computing

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 Type	Locking Type	Time taken to perform GetRows
ForwardOnly	Optimistic	0:02
Unspecified	Optimistic	0:02
ForwardOnly	Unspecified	0:03
ForwardOnly	ReadOnly	0:03
Unspecified	Unspecified	0:03
Unspecified	ReadOnly	0:03
Dynamic	Unspecified	0:30
Dynamic	Optimistic	0:30
Dynamic	ReadOnly	0:32
Static	ReadOnly	0:57
KeySet	Optimistic	0:58
KeySet	ReadOnly	1:01
Static	Unspecified	1:09
Static	Optimistic	1:10
KeySet	Unspecified	1: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

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.

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.

---

[HOME] [TABLE OF CONTENTS] [SEARCH]