ODBC DSN setting to bulk fetch/load to reduce number of network call.

Question

We are looking for native ODBC driver setting to configure bulk load/fetch to reduce the number of network calls. This will improve the performance by increasing the number of rows the driver loads at a time because fewer network round trips are required. Other drivers e.g. DataDirect do have connection parameter like 'BulkLoadBatchSize', or in DB2 driver it is something 'BlockForNRows'. Oracle uses FetchBufferSize   Symptom: A table with large entries ~1 million records. Use unixODBC isql and ODBC 18 driver version, run a query similar to 'select column1, column2, ... from table' .... validate the network traces, which shows for each SQLFetch there is an entry in network trace.  In case if there is any better way to allow optimization for bulk fetch by providing the DSN setting to sql server odbc driver, do let us know. We are in process of migrating from DataDirect drivers to SQL native driver and experiencing a performance issue as application taking time to fetch all records if large data present.  

Answer 1

Hi @Mukesh Patel,

SQL Server ODBC Driver: For Microsoft SQL Server, you can configure bulk fetch/load operations using the "BulkFetchEnabled" and "BulkRowCount" connection parameters. "BulkFetchEnabled" enables or disables bulk fetch operations, and "BulkRowCount" specifies the number of rows to be fetched in each batch. You can set "BulkFetchEnabled" to "1" to enable bulk fetch, and set "BulkRowCount" to an appropriate value to specify the number of rows to be fetched in each batch.

Oracle ODBC Driver: For Oracle, you can configure bulk fetch/load operations using the "FetchBufferSize" connection parameter. "FetchBufferSize" specifies the number of rows to be fetched in each batch. You can set "FetchBufferSize" to an appropriate value to optimize bulk fetch operations.

IBM DB2 ODBC Driver: For IBM DB2, you can configure bulk fetch/load operations using the "BlockForNRows" connection parameter. "BlockForNRows" specifies the number of rows to be fetched in each batch. You can set "BlockForNRows" to an appropriate value to optimize bulk fetch operations.

Additionally, you may need to also consider tuning other settings such as buffer sizes, fetch options, and network settings to further optimize performance.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.