The DataFlex SQL Drivers use block cursors and JIT (just-in-time) binding.
SQL environments are set oriented. The DataFlex view of data is record oriented. In order to get at set oriented data in a record oriented fashion, database cursors are used. A database cursor can be seen as a pointer to the current record in an SQL set. There are ways to change the position of the cursor.
Most SQL back ends support several database cursor types. An application developer chooses the cursor type that fits the application needs best. The database cursor types are: Forward only, static, keyset-driven and dynamic. Not every back end supports all cursor types.
If find operations are done outside of a transaction, the SQL drivers will use a forward only database cursor. Inside a transaction find operation on a table that does not allow locking (DF_FILE_MODE is set in such a way that the DF_FILE_MODE_NO_LOCKS bit is set), a forward only database cursor will also be used.
If a find operation is done inside a transaction and the table allows locking (DF_FILE_MODE is set in such a way that the DF_FILE_MODE_NO_LOCKS bit is not set), the database cursor will be chosen depending on the supported cursors on the back end. See Transactions for more information.
A block cursor will retrieve multiple records when fetching data. The size of the block cursors can be configured using the Block_Size intermediate file keyword or the DF_FILE_BLOCK_SIZE attribute.
Using block cursors speeds up find operations; it also creates a form of caching. The driver will request multiple records at a time. The next record will be fetched from memory, rather then from the back end. Using a cache introduces the risk of returning incorrect data. To avoid this, a find cache timeout can be set (in milliseconds). If the time between two find operation is larger than the find cache timeout (default 10 milliseconds), the driver will fetch the data from the back end even if not all records in the cache have been processed. The find cache timeout can be configured using the Find_Cache_Timeout driver configuration keyword.
Two table level attributes can be used to measure the effectiveness of the current find cache timeout setting. The attributes DF_FILE_FINDCACHE_HITS and DF_FILE_FINDCACHE_TIMEOUTS will return the number of cache hits (usage of cache) and timeouts (discarding the cache and getting data from the server) respectively. The find logic will increment the appropriate attribute when needed. The attributes are kept in signed integer variables. Whenever incrementing the integer value of one of the attributes causes the value to become negative both variable will be set to 0 (zero). It is possible to set the attributes to 0 (zero, the only allowed value). Setting one of the attributes will automatically cause the other attribute to be set to 0 (zero).
For every table, two statement handles can be allocated. A statement handle allows a program to access a database cursor. One statement handle is used for all find equal operations on any index of the table; the other statement handle is used for every other find operation on the table. The statement handle will be allocated the first time it is required. When a table is opened but no finding is done in the table, no statement handles will be allocated. Version 4.1 and earlier would allocate one handle for the find equal by Recnum/RowId and another for every other find operation (including all other find equal operations).
To minimize network traffic, not all columns of a table will be fetched from the back end. Columns that have a large native size (default > 10 megabytes) will not be fetched along with the rest of the record when finding. The value of the column will be fetched into the record buffer when it is accessed the first time.
JIT Binding can be configured on two levels. On driver level, the threshold that makes a column eligible for JIT binding can be set using the JIT_Treshold driver configuration keyword. On table level, JIT binding can be switched on or off using the JIT_Binding intermediate file keyword or the DF_FILE_JIT_BINDING attribute.