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

Mukesh Patel 20 Reputation points
2023-04-21T08:38:29.8533333+00:00

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.  

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,436 Reputation points
    2023-04-24T02:56:21.78+00:00

    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.