Sunday, September 18, 2011

Improving Performance with Pentaho Data Integration Table Input and Oracle

When using Pentaho Data Integration Table Input step to connect to Oracle via a JDBC connection there is a setting in your connection information that you can specify that can dramatically improve your performance in retrieving data.  This property is the defaultRowprefetch.  Oracle JDBC drivers allow you to set the number of rows to prefetch from the server while the result set is being populated during a query. Prefetching row data into the client reduces the number of round trips to the server. The default value for this property is 10.

In the table input step, edit your connection, click on the options tab and then enter in your defaultRowprefetch specification: