Improving Performance with Pentaho Data Integration (Kettle) Table Input Step and Oracle Databases


When using Pentaho Data Integration  (a.k.a. Kettle)  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:

Advertisements

3 thoughts on “Improving Performance with Pentaho Data Integration (Kettle) Table Input Step and Oracle Databases

  1. Wow. Without it, my row fetching was capped at an awful 5000 rows per second. Adding this attribute, I boosted it up to 600,000 rows per second!!!

    But be careful with large values for defaultRowPrefetch attribute. If you use the same connection for Insert/Update, you run out of memory very quickly. The solution I found was to duplicate my connection, have the attribute set in one and not in the other. Then I use one for input only and one for output.

  2. Thanks for InformationFlax IT online training began online training for Software program and SAP courses. Online training is the better option to study software products as well as programs, which are SAP modules, like SAP HR, SAP FSCM, SAP BASIS, SAP ABAP, SAP BW, SAP ED ADMIN, SAP SECURITY, SAP SRM, SAP SCM, SAP GRC etc., and Software Courses like JAVA-J2EE, PeopleSoft, Tibco courses, Microsoft Courses, Network and System Admin, Oracle Courses, SAS, Testingtools, PMP, Cognos, AB Initio, ORACLE and Database etc..

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s