The "Maximum
Open Rows" property, which is supported by all of the providers, determines
how many rows can be active at any one time. This property affects
performance because of the way it interacts with ADO and the "CacheSize"
property.
ADO Recordset
objects do not release open rows held in memory until they request
more data from the data provider. All data requests performed after
the first request can retrieve only "Maximum Open Rows" minus "CacheSize"
rows. For example, if you create an application that iterated in sequence
through each recordset row in a cache and you set the "Maximum Open
Rows" value to 10 and the CacheSize value to 8, performance would
be affected in the following ways:
-
On the first request for data, ADO requests eight
rows from the provider.
-
After the application iterates through those eight
rows, ADO initiates its next request for eight rows. However, because
"Maximum Open Rows" is set to 10 and ADO still has eight rows open,
the provider returns only two rows.
-
When the two rows are returned, ADO releases the first
two rows in its local cache, returning the number held in memory to
eight.
Although
this behavior does not affect the ability of an application to iterate
through a recordset, it might eliminate the performance gain that
would otherwise be expected. In order for the Recordset object to
retrieve the number of rows indicated by the value of "CacheSize",
you should set "Maximum Open Rows" to at least twice the value of
"CacheSize". The following code shows how this task can be done:
' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset
' Set the active connection here instead of on the Open method.
obRecordset.ActiveConnection = obConnection
obRecordset.CacheSize = 55
obRecordset.Properties("Maximum Open Rows") = 110
If InStr(LCase(obConnection.Provider), "iomprovider") <> 0 Then
obRecordset.Properties("SAS Page Size") = 55
End If
obRecordset.Open "seashell.mediate", , adOpenDynamic, adLockOptimistic, adCmdTableDirect