- In Deferred Mode, ODBC Driver for Teradata sends an additional query to retrieve each LOB. By default, the driver uses Deferred Mode.
- In SLOB Mode, ODBC Driver for Teradata retrieves LOBs without sending any additional queries, but it may need to cache some LOBs in memory.
To optimize driver performance, use Deferred Mode when retrieving large LOBs you do not want to cache into memory; use SLOB Mode to retrieve many small LOBs and want to avoid sending a large number of queries. For example, SLOB Mode can improve driver performance when retrieving geospatial data.
SLOB Mode Usage Guidelines
- The LOB to be retrieved must be smaller than the size specified by the Max Single LOB Bytes setting, MaxSingleLOBBytes. ODBC Driver for Teradata falls back to using Deferred Mode when retrieving LOBs that exceed this size. The default value for this setting is 4000.
- If the total amount of LOB data being retrieved from a row exceeds the size specified by the Max Total LOB Bytes Per Row setting, MaxTotalLOBBytesPerRow, ODBC Driver for Teradata uses Deferred Mode to retrieve the remaining LOBs from that row after using SLOB Mode to retrieve LOBs up to this size limit. The default value for this setting is 65536.
- Do not enable the Use Sequential Retrieval Only setting, UseSequentialRetrievalOnly, if there is any possibility you might retrieve LOBs from columns in a non-sequential order. For instance, do not enable this option and then execute a query that retrieves LOBs from the third column in a table, then from the first column, and then from the fifth column. If you enable this option and then retrieve LOBs non-sequentially, ODBC Driver for Teradata discards the LOBs that are returned through SLOB Mode and must then retrieve them all again using Deferred Mode.
- When the Use Sequential Retrieval Only setting, UseSequentialRetrievalOnly, is disabled, ODBC Driver for Teradata caches the other LOBs it reads while looking for the one to be retrieved. Caching large amounts of data in memory can decrease performance. To prevent this problem, set the size limits so ODBC Driver for Teradata does not apply SLOB mode to large LOBs. LOB values that do not meet the requirements for SLOB Mode are retrieved using Deferred Mode instead, and, therefore, do not get cached.
Controlling the Scope of SLOB Mode Settings
Configure the settings for SLOB Mode on the connection level or on the statement level. Because the optimal settings vary depending on the size of the specific LOBs you are retrieving, it may be useful to adjust the settings for each statement as you work with your data.
- SQL_ATTR_MAX_SINGLE_LOB_BYTES: Use this attribute to specify the maximum size of the LOBs (in bytes) that ODBC Driver for Teradata can retrieve using SLOB Mode. LOBs that exceed this size are retrieved using Deferred Mode instead. This attribute corresponds to the Max Single LOB Bytes driver setting, MaxSingleLOBBytes.
- SQL_ATTR_MAX_LOB_BYTES_PER_ROW: Use this attribute to specify the maximum size of LOB data per row (in bytes) that ODBC Driver for Teradata can retrieve using SLOB Mode. If the total amount of LOB data contained in a row exceeds this size, ODBC Driver for Teradata retrieves the LOBs from that row using Deferred Mode instead. This attribute corresponds to the Max Total LOB Bytes Per Row driver setting, MaxTotalLOBBytesPerRow.
- SQL_ATTR_USE_SEQUENTIAL_RETRIEVAL_ONLY: Use this attribute to indicate whether you are retrieving LOB data from columns in sequential order. This attribute corresponds to the Use Sequential Retrieval Only driver setting, UseSequentialRetrievalOnly.
Max Single LOB Bytes
Key Name | Default Value | Required |
---|---|---|
MaxSingleLOBBytes | 4000 | No |
Max Total LOB Bytes Per Row
Key Name | Default Value | Required |
---|---|---|
MaxTotalLOBBytesPerRow | 65536 | No |
This is the maximum size of LOB data per row (in bytes) that ODBC Driver for Teradata can retrieve using Smart LOB (SLOB) Mode. If the total amount of LOB data contained in a row exceeds this size, ODBC Driver for Teradata retrieves the LOBs from that row using Deferred Mode instead.
If this option is set to 0, SLOB Mode is disabled, and ODBC Driver for Teradata retrieves all LOB data using Deferred Mode.
Use Sequential Retrieval Only
Key Name | Default Value | Required |
---|---|---|
UseSequentialRetrievalOnly | Clear (0) | No |
- Enabled (1): When working in SLOB Mode, ODBC Driver for Teradata does not cache the other LOBs it reads while looking for the one to be retrieved. Because ODBC Driver for Teradata can retrieve LOBs in a single pass if they are queried sequentially, it does not need to cache them.
- Disabled (0): When working in SLOB Mode, ODBC Driver for Teradata caches the other LOBs that it reads while looking for the one to be retrieved. This caching allows the driver to successfully retrieve SLOBs in any order.
As an alternative to using this option, you can specify this setting on the statement level rather than the connection level by using the SQL_ATTR_USE_SEQUENTIAL_RETRIEVAL_ONLY statement attribute.
Differences in Driver Implementation
Starting with version 16.20 of the driver, ODBC Driver for Teradata has defaults to determine which mode to transfer LOB data with. These default sizes may be different than past versions as we try to provide values that fit most cases.
If the LOB is too large, ODBC Driver for Teradata uses Deferred Mode as this is faster if you do not want to cache into memory. SLOB is better in certain cases, such as when working with geospatial data.
- Starting with 16.20, ODBC Driver for Teradata can cache, or attempt to cache, up to 2 GB of all SLOBs in a row.
- In 16.10 and prior versions of the driver, ODBC Driver for Teradata only caches two Response Buffers, up to 32 MB.
- 1- Enable SLOB Random Access
- 2- Max size of one SLOB
- 3- Max size of all SLOBs in a Row
When #1 is set to True, the driver will cache up to #3, potentially up to 2 GB.