Selective Column Fetching (SCF) offers a way to programmatically influence the number of columns that are fetched from the back-end. This feature is especially useful when looping through records. It is supported by the SQL Drivers. Limiting the number of columns that are fetched reduces the amount of data that is sent from the database server to the client, thus improving performance.
There are two levels in which SCF can be controlled. It can be switched ON or OFF at the table level using the DF_FILE_FETCH_ALL_COLUMNS attribute. It can be switched ON or OFF for individual columns using the DF_FIELD_FETCH_STATE attribute.
The DF_FILE_FETCH_ALL_COLUMNS table attribute indicates if Selective Column Fetching is ON or OFF for a table. When a table is opened, it will automatically be set to true (all columns are fetched after opening).
When DF_FILE_FETCH_ALL_COLUMNS is set to false, all columns having the fetch state set to false will be cleared. The value in the buffer for the columns that have the fetch state set to true will not be changed.
Setting DF_FILE_FETCH_ALL_COLUMNS to true does not alter any column values.
The DF_FIELD_FETCH_STATE column attribute indicates if individual columns are selected or not. There is a special field number keyword to set this attribute for all columns in a table at once: DF_ALL_COLUMNS. When a table is opened, the attribute will automatically be set to true for all columns (all columns are fetched after opening).
Setting the DF_FIELD_FETCH_STATE of a column to false will also clear the column value in the buffer. Setting the attribute to true will not alter the column value in the buffer.
If we would want to fetch column "MySampleColumn" of MyTable only, we would write:
Move MyTable.File_Number To hTable
Field_Map hTable "MySampleColumn" To hColumn
Set_Attribute DF_FILE_FETCH_ALL_COLUMNS Of hTable To False
Set_Attribute DF_FIELD_FETCH_STATE Of hTable DF_ALL_COLUMNS To False
Set_Attribute DF_FIELD_FETCH_STATE Of hTable hColumn To True
Setting DF_FILE_FETCH_ALL_COLUMNS will not alter the individual column settings for the DF_FIELD_FETCH_STATE attribute. This enables the programmer to create maximum efficiency in update loops. Let’s assume we want to update the table MyTable looping through it by MyIndexColumn starting at a given value and updating it when MyTriggerColumn has a certain value. One would program a loop like:
Procedure RunLoop Handle hTable String sSeed String sTrigger
Handle hColumn1
Handle hColumn2
Handle hSomeOtherColumn
Boolean bMyTableFound
String sValue
String sSomeValue
//*** Switch to SCF for two columns
Set_Attribute DF_FILE_FETCH_ALL_COLUMNS Of hTable To False
Set_Attribute DF_FIELD_FETCH_STATE Of hTable DF_ALL_COLUMNS To False
Field_Map hTable “MyIndexColumn” To hColumn1
Set_Attribute DF_FIELD_FETCH_STATE Of hTable hColumn1 To True
Field_Map hTable “MyTriggerColumn” To hColumn2
Set_Attribute DF_FIELD_FETCH_STATE Of hTable hColumn2 To True
//*** Determine number of other column to update
Field_Map hTable “MyOtherColumn” To hSomeOtherColumn
//*** Seed the buffer and loop
Clear hTable
Set_Field_Value Of hTable hColumn2 To sSeed
VFind hTable 1 GE
Move (Found) To bMyTableFound
While (bMyTableFound)
Get_Field_Value hTable hColumn2 To sValue
If (sValue = sTrigger) Begin
//*** Switch to normal fetching
Set_Attribute DF_FILE_FETCH_ALL_COLUMNS Of hTable To True
//*** Update the record
Begin_Transaction
//*** Update record
Reread hTable
Set_Field_Value Of hTable hColumn2 To sNewValue
Get CalculateSomeValue hTable To sSomeValue
Set_Field_Value Of hTable hSomeOtherColumn To sSomeValue
SaveRecord hTable
Unlock
End_Transaction
//*** Switch to SCF
Set_Attribute DF_FILE_FETCH_ALL_COLUMNS Of hTable To False
End
VFind hTable 1 GT
Move (Found) To bMyTableFound
End
//*** Switch to normal fetching
Set_Attribute DF_FILE_FETCH_ALL_COLUMNS Of hTable To True
End_procedure // RunLoop
Trying to set the value of a column that has its fetch state set to false, in a table for which the DF_FILE_FETCH_ALL_COLUMNS state is false, will result in error 12318 (CLIERR_ CANTCHANGEINACTIVECOLUMN). This error cannot be switched off.
Trying to set the fetch state of the RECNUM column to false can result in error 12317 (CLIERR _CANTSETRECNUMINACTIVE). This error will only be generated when the REPORT_ACTIVE_COLUMN_ERRORS global driver attribute is switched ON.
Trying to get value of a column that has its fetch state set to false can result in error 12319 (CLIERR _ FIELDISINACTIVE). This error will only be generated when the REPORT_ACTIVE_COLUMN_ERRORS global driver attribute is switched ON. If the global driver attribute is switched off, the column value that is currently in the buffer is returned.
The global driver attribute REPORT_ACTIVE_COLUMN_ERRORS can be set in the driver configuration file “mssqldrv.int” by using the keyword REPORT_ACTIVE_COLUMN_ERRORS. It can be set to ON by giving it a non-zero value or to OFF by giving it the zero value. By default the attribute is switched off.
It is also possible to set the attribute from within a program by using the DF_DRIVER_REPORT_ACTIVE_COLUMN_ERRORS driver level attribute.
Selective column fetching will reduce the amount of data that travels over the network and will increase the speed of a program accessing this data. In lab testing SQL Server we found that the impact on text columns is the largest. The default SQL Server type to map a DataFlex text column to is now SQL Server text, it used to be SQL Server varchar. The table below shows the results of our lab test on two tables. One table converted the “old way” using varchar, one table converted the “new” way using text. On both tables we ran a loop that fetched all columns and one that fetched all but the DataFlex text columns:
Varchar Text Difference
Loop fetch all columns 214 270 56 – 26%
Loop fetch selected columns 209 120 89 – 43%
Difference 5 – 2% 150 – 56%
Be aware that the actual numbers in the table above do not have a big meaning. Changing the environment will change the numbers. The differences between the numbers are important. The differences show that using text column is slower when fetching all columns but much faster when not fetching all columns.
Since in a lot of situations the text columns are never used, we provide a helper class to easily switch fetching all text columns of all open tables OFF and back ON again. The class is called cTableColumnFetchHelper and can be found in cTableColumnFetchHelper.pkg.