The SAS Data Providers support the following ADO and OLE DB properties that affect the performance of your application.
| ADO Property Name | OLE DB Property ID | Support |
|---|---|---|
| CacheSize | n/a | Supported in ADO with all providers |
| Maximum Open Rows | DBPROP_MAXOPENROWS | Supported in all providers |
| SAS Page Size | DBPROP_SAS_PAGESIZE | Supported in the IOM Provider |
These properties determine how much data is retrieved and cached at different architectural levels, so they will affect performance—for better or for worse—depending on how you use them in your application. For example, significant performance improvement occurs when each accessed row is relatively close to the previously accessed row. However, if your application randomly accesses rows or only accesses a few rows, then increased data caching may degrade performance. To optimize performance in this case, you can take the following two steps:
The Recordset object property "CacheSize" indicates how many rows should be cached in ADO's local memory. The following code fragment illustrates how to set this property.
Note: For information about how to open a Connection object, see Opening an ADO Connection Object.
Dim can As ADOBE.Connection Dim rs As ADOBE.Recordset Set can = New ADOBE.Connection Set rs = New ADOBE.Recordset '... Open the Connection object. RS.CacheSize = 55 RS.Open "seashell.mediate", can, adOpenDynamic, adLockOptimistic, adCmdTableDirect
The "Maximum Open Rows" property, which is supported by all the providers, determines how many rows can be active at any one time. "Maximum Open Rows" impacts 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 after they request more data from the data provider. This means that all data requests performed after the first request will only be able to retrieve "Maximum Open Rows" minus "CacheSize" rows. For example, if you created 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 way:
Although this behavior does not affect the ability of an application to iterate through a recordset, it may eliminate the performance gain that would otherwise be expected. In order for the ADO Recordset object to retrieve the number of rows indicated by the value of "CacheSize" number of rows, the "Maximum Open Rows" should be set to at least twice the value of "CacheSize". The following code fragment shows how this can be done:
Dim can As ADOBE.Connection
Dim rs As ADOBE.Recordset
Set can = New ADOBE.Connection
Set rs = New ADOBE.Recordset
'... Open the Connection object.
RS.ActiveConnection = can
RS.CacheSize = 55
RS.Properties("Maximum Open Rows") = 110
If InStr(LCase(can.Provider), "iomprovider") <> 0 Then
RS.Properties("SAS Page Size") = 55
End If
'Note that RS.ActiveConnection was set above and
'therefore no Connection is passed to the Open call
RS.Open "seashell.mediate", , adOpenDynamic, adLockOptimistic, adCmdTableDirect
The "SAS Page Size" property, which is only available for use with the IOM provider, determines how many rows will be retrieved from the IOM Server at one time. It serves a function similar to the "CacheSize" Recordset property. However, the "SAS Page Size" property determines how much data is cached in the provider itself rather than in the ADO Recordset object.
There are a few special values for the "SAS Page Size" property.
Dim can As ADOBE.Connection
Dim rs As ADOBE.Recordset
Set can = New ADOBE.Connection
Set rs = New ADOBE.Recordset
'... Open the Connection object.
RS.ActiveConnection = can
RS.CacheSize = 55
RS.Properties("Maximum Open Rows") = 110
If InStr(LCase(can.Provider), "iomprovider") <> 0 Then
RS.Properties("SAS Page Size") = 55
End If
'Note that RS.ActiveConnection was set above and
'therefore no Connection is passed to the Open call
RS.Open "seashell.mediate", , adOpenDynamic, adLockOptimistic, adCmdTableDirect
Note: The IOM Provider also supports optimized accessors as described in the OLE DB Programmer's Reference and Data Access SDK. This feature allows OLE DB consumers to specify which columns should be included in the provider's cache.