Specifying the Fetch Size for Oracle and MS SQL Server
You can control the amount of data fetched in each batch of rows retrieved from an Oracle or MS SQL Server data source. In the data source capabilities file, the fetch_size attribute controls the number of rows that are being fetched from the data source on every ResultSet.next call.
Adjusting fetch_size is optional and for performance tuning only. Be sure to test the effects of these adjustments to see if performance improves. If the configured value of fetch_size is zero or negative, the default value for the driver is used.
Note: A fetch size that is too small can degrade performance.
To change the batch fetch size for Oracle or MS SQL Server
1. Locate the directory containing data source capabilities files which is:
<TDV_install_dir>\conf\adapters\system\
2. Using a text editor like Notepad, open the capabilities file in the directory for your data source:
microsoft_sql_server_2008_values.xml
oracle_11g_oci_driver_values.xml
oracle_11g_thin_driver_values.xml
3. Uncomment the fetch_size attribute and set its value. For example:
<ns5:attribute xmlns:ns5="http://www.compositesw.com/services/system/util/common">
<ns5:name>/runtime/iud/fetchSize</ns5:name>
<ns5:type>INTEGER</ns5:type>
<ns5:value>12345</ns5:value>
<ns5:configID>jdbc.fetch_size</ns5:configID>
</ns5:attribute>
4. Save the capabilities file.