The following connection options can enhance driver performance. You can also enhance performance through efficient application design. See "Designing ODBC Applications for Performance Optimization" for details.
Array Fetch Size (ArrayFetchSize): To improve throughput, consider increasing the value of Array Fetch Size. By increasing the value, you increase the number of rows the driver will retrieve from the server for a fetch. In turn, increasing the number of rows that the driver can retrieve reduces the number, and expense, of network round trips. For example, if an application attempts to fetch 100,000 rows, it is more efficient for the driver to retrieve 2000 rows over the course of 50 round trips than to retrieve 500 rows over the course of 200 round trips. Note that improved throughput does come at the expense of increased demands on memory and slower response time. Furthermore, if the fetch size exceeds the available buffer memory of the server, an out of memory error is returned when attempting to execute a fetch. If you receive this error, decrease the value specified until fetches are successfully executed.
Array Insert Size (ArrayInsertSize): You can improve performance when executing batch inserts by increasing the max buffer size using Array Insert Size. By increasing the value for Array Insert Size, you increase the size of the packet sent to the server, therefore reducing the expense associated with multiple network round trips. The tradeoff for improved throughput is a greater demand on memory and slower response times. If you encounter memory issues or memory related server errors, you may need to decrease the value for this option.
Batch Mechanism (BatchMechanism): If your application does not require individual update counts for each statement or parameter set in the batch, then BatchMechanism should be set to 2 (MultiRowInsert). Unlike the native batch mechanism, the multi-row insert mechanism only returns the total number of update counts for batch inserts. Therefore, setting BatchMechanism to MultiRowInsert offers substantial performance gains when performing batch inserts.
Catalog Mode (CatalogMode): Apache Hive’s native catalog functions return incorrect information in certain scenarios. To address this issue, by default, the driver uses a combination of driver-discovered information and native functions to retrieve more accurate catalog information than native functions alone. While using driver-discovered information improves accuracy, it does so at an expense to performance. If accurate catalog information is not required, you can improve performance by setting Catalog Mode connection option to 1 (Native).
Default Buffer Size for Long/LOB Columns (in Kb) (DefaultLongDataBuffLen): To improve performance when your application fetches images, pictures, or long text or binary data, a buffer size can be set to accommodate the maximum size of the data. The buffer size should only be large enough to accommodate the maximum amount of data retrieved; otherwise, performance is reduced by transferring large amounts of data into an oversized buffer. If your application retrieves more than 1 MB of data, the buffer size should be increased accordingly.
EnableCookieAuthentication (EnableCookieAuthentication): To improve response time when using HTTP mode (TransportMode=1), enable session cookie based authentication (EnableCookieAuthentication=1). When cookie based authentication is enabled (the default), the driver uses cookies authenticate requests to the server after the initial authentication that occurs at connection. This eliminates the overhead associated with executing a standard re-authentication attempt for each request to the server.
Encryption Method (EncryptionMethod): Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) that is required to encrypt and decrypt data.
String Describe Type (StringDescribeType): To fetch String as SQL_WLONGVARCHAR, the String Describe Type connection option must be set to -10 (SQL_WLONGVARCHAR). When String Describe Type is set to -10, the driver not only maps String to SQL_WLONGVARCHAR, but also allocates more space to cache the long data. Because more space is allocated for the long data, your application will incur a performance penalty.
