Deferred Columns and Performance

        It is normal to use select fields and where clauses to filter away data at the server side so that only a sub-tabular data of required columns are returned to the client. However, a considerable proportion of useless data are still fetched onto a client because limitations of SQL select statement and feature of tabular data can't make it possible that just the required data are fetched only. The way to filter away the useless data further is definitely useful and improve the navigation of a big rowset. OLEDB has such a way to approach this goal, use of deferred columns.

        For a deferred column, the provider is not required to retrieve data from the data store until IRowset::GetData is called for that column. It implies that the provider need not retrieve a column data until a method used to access the data is called. In other words, if a DBBINDING structure for a column is not bounded with an accessor, an OLEDB provider probably doesn't retrieve the column data from a server to a client. We can fully take advantage of this feature to further filter away data at the server side after setting required fields and where clauses of a statement.

        How can we check if a provider supports this feature? After opening a rowset, you simply check if the rowset DBPROP_DEFERRED property is set to true. Whether or not a column is deferred by default is provider-specific and may vary depending on the column. If a column is deferred, its DBCOLUMNFLAGS_MAYDEFER or DBCOLUMNFLAGS_CACHEDEFERRED enumerated type flag returned by IColumnsInfo::GetColumnInfo is set. Otherwise, it is not deferred.

        Currently, both jet providers well support this feature for MS Access and other database files, spreadsheets, and textual data stored in tabular format through installable ISAM drivers. MS OLE DB simple provider also supports this feature. MS client cursor engine supports this feature too after fetching a rowset from a server to a client.

        All the CRBase based classes are capable to fully take advantage of this feature by use of the key function CRBase::SetDBPart at the run time, as demonstrated in the provided example FastAccess. The resultant improvement is up to the number of columns, record size and what percentage of data fetching can be avoided.         

        Currently most of OLEDB providers, which move data from servers to clients by network, are not able to have this feature because of the complexity of implementation of rowset property DBPROP_DEFERRED. However, the capability of use of this feature is not dependent on if a provider supports this property at all. You are still very safe to use this feature for improving a little performance with OleDBPro module because use of this feature reduces copying data from a provider to a consumer. In the future, it is hoped that more and more network based providers support this advanced future.