Oracle Odbc Driver Configuration Fetch Buffer Size EXCLUSIVE
Isadora Rimbey
Prefetch size for LONG column data - Set this value to prefetch LONG or LONG RAW data to improve performance of ODBC applications. This enhancement improves the performance of Oracle ODBC driver up to 10 times, depending on the prefetch size set by the user. The default value is 0. The maximum value that you can set is 64 KB (65536 bytes).
If the value of prefetch size is greater than 65536, the data fetched is only 65536 bytes. If you have LONG or LONG RAW data in the database that is greater that 65536 bytes, then set the prefetch size to 0 (the default value), which causes single-row fetch and fetches complete LONG data. If you pass a buffer size less than the prefetch size in nonpolling mode, a data truncation error occurs if the LONG data size in the database is greater than the buffer size.
I am having a similar problem, and decided to change the fetch buffer size. However part way through the run the setting reverts. Are you aware of any other way that this setting could be changed, or do I need to change it in a different file, or even the loadscript itself?
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.
Hi @Seeya Xi-MSFT , Thanks for helping me with these two settings BulkFetchEnabled & BulkRowCount, though it seems it is not working and having no impact. I still see the wireshark trace has the same packet length like before and remain unchanged. Means with with/without setting it is same behavior in terms of fetch performance.My sample DSN, looks like[perfTest]Driver=/opt/test/native/odbc/lib/libmsodbcsql.soDatabase=sessionstoreServer=myhost.com, 1433Trusted_Connection=NoTrustServerCertificate=NoEncrypt=NoBulkFetchEnabled=1BulkRowCount=1000Let us know if we are missing something else. You did mention about tuning the other parameter like buffer size and fetch options. Please let us know, as I am finding difficulties to get these setting from SQL Server ODBC Driver documentation.
Regards,Mukesh
-N - Network Packet size,a value that determines the number of bytes per network packettransferred from the database server to the client. The correctsetting of this attribute can improve performance. When set to 0,the initial default, the driver uses the default packet size asspecified in the Sybase server configuration. When set to -1, thedriver computes the maximum allowable packet size on the firstconnect to the data source and saves the value in the systeminformation.
Oracle ODBC driver is enhanced to prefetch LONG or LONG RAW data to improve performance of ODBC applications. To do this, the maximum size of LONG data (MaxLargeData) must be set in the registry on Windows (you also need to add the registry key MaxLargeData in the DSN), and set this manually in the odbc.ini file on UNIX platforms. This enhancement improves the performance of Oracle ODBC driver up to 10 times, depending on the MaxLargeData size set by the user. The default value of MaxLargeData is 0. The maximum value for MaxLargeData that you can set is 64KB (65536 bytes).
If the value of MaxLargeData is set to some value greater than 65536, the data fetched will only be 65536 bytes. If you have LONG or LONG RAW data in the database that is greater that 65536 bytes, MaxLargeData should be set to 0 (the default value), which will result in single row fetch and complete LONG data can be fetched. If you pass a buffer size less than the MaxLargeData size in non-polling mode, a data truncation error will occur if the LONG data size in the database is greater than the buffer size.
Pre-fetch size for LONG column data - Set this value to prefetch LONG or LONG RAWdata to improve performance of ODBC applications. This enhancementimproves the performance of Oracle ODBC driver up to 10 times,depending on the pre-fetch size set by the user. The default value is0. The maximum value that you can set is 64KB (65536 bytes).
If the value of pre-fetch size is set to some value greater than 65536, the data fetched will only be 65536 bytes. If you have LONG or LONG RAWdata in the database that is greater that 65536 bytes, the pre-fetchsize should be set to 0 (the default value), which will result insingle row fetch and complete LONG data can be fetched.If you pass a buffer size less than the pre-fetch size in non-pollingmode, a data truncation error will occur if the LONG data size in the database is greater than the buffer size.
The maximum number of rows that the connector fetches and reads into memory during an incremental snapshot chunk.Increasing the chunk size provides greater efficiency, because the snapshot runs fewer snapshot queries of a greater size.However, larger chunk sizes also require more memory to buffer the snapshot data.Adjust the chunk size to a value that provides the best performance in your environment.
Note the size parameter only affects the number of rows returned to theapplication, not to the internal buffer size used for tuning fetchperformance. That internal buffer size is controlled only by changingCursor.arraysize, see Tuning Fetch Performance.
For Oracle, the Block parameter can be used in conjunction with the MaxFetchBuffer database parameter to improve performance when the size of a row is very large. The MaxFetchBuffer parameter has a default value of 5000000 bytes, which is sufficient for most applications. The size of the actual fetch buffer is the product of the value of the blocking factor and the size of the row.
If the fetch buffer required by the blocking factor and the row size is greater than the value of MaxFetchBuffer, the value of the blocking factor is adjusted so that the buffer is not exceeded. For example, if block=500 and the row size is 10KB, the fetch buffer is 5000KB, which equals the default maximum buffer size.
Sets the maximum size of the buffer into which theDataWindow object can fetch rows from the database. Using the MaxFetchBuffer parameter with the Block parameter can improve performance when accessing a database in PowerBuilder.
The size of the actual fetch buffer is the product of the value of the blocking factor and the size of the row. If the fetch buffer required by the blocking factor and the row size is greater than the value of MaxFetchBuffer, the value of the blocking factor is adjusted so that the buffer is not exceeded.
Enables on-demand loading, and defines double buffer size for the result. The fetchSize parameter is rounded according to chunk size. For example, fetchSize=1 loads one row and is rounded to one chunk. If the fetchSize is 100,600, a chunk size of 100,000 loads, and is rounded to, two chunks.
Here is an example of setting up the plugin to fetch data from a MySQL database.First, we place the appropriate JDBC driver library in our currentpath (this can be placed anywhere on your filesystem). In this example, we connect tothe mydb database using the user: mysql and wish to input all rows in the songstable that match a specific artist. The following examples demonstrates a possibleLogstash configuration for this. The schedule option in this example willinstruct the plugin to execute this input statement on the minute, every minute.
Used with the fetchmany method, specifies the internal buffer size, which is also how many rows are actually fetched from the server at a time. The default value is 10000. For narrow results (results in which each row does not contain a lot of data), you should increase this value for better performance.
After a query execution, you can fetch result rows by calling the next() method on the returned ResultSet repeatedly. This method triggers a request to the driver Thrift server to fetch a batch of rows back if the buffered ones are exhausted. We found the size of the batch significantly affects the performance. The default value in the most of the JDBC/ODBC drivers is too conservative, and we recommend that you set it to at least 100,000. Contact the BI tool provider if you cannot access this configuration.
dd2b598166