PDI performance tuning tips
The following tips can help diagnose performance issues.
| Step | Tip | Description |
| ------------- | ----------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| JS | Turn off compatibility mode | Rewriting JavaScript to use a format that is not compatible with previous versions is, in most instances, easy to do and makes scripts easier to work with and to read. By default, old JavaScript programs run in compatibility mode. That means that the step will process like it did in a previous version. You may see a small performance drop because of the overload associated with forcing compatibility. If you want to make use of the new architecture, disable compatibility mode and change the code as shown below:
intField.getInteger() > intFieldnumberField.getNumber() > numberFielddateField.getDate() > dateFieldbigNumberField.getBigNumber() > bigNumberField- and so on...
Instead of Java methods, use the built-in library. Notice that the resulting program code is more intuitive. For example:
- checking for null is now:
field.isNull() > field==null - Converting string to date:
field.Clone().str2dat() > str2date(field) - and so on...
If you convert your code as shown above, you may get significant performance benefits.
Note: It is no longer possible to modify data in-place using the value methods. This was a design decision to ensure that no data with the wrong type would end up in the output rows of the step. Instead of modifying fields in-place, create new fields using the table at the bottom of the Modified JavaScript transformation.
|
| JS | Combine steps | One large JavaScript step runs faster than three consecutive smaller steps. Combining processes in one larger step helps to reduce overhead. |
| JS | Avoid the JavaScript step or write a custom plug in | Remember that while JavaScript is the fastest scripting language for Java, it is still a scripting language. If you do the same amount of work in a native step or plugin, you avoid the overhead of the JS scripting engine. This has been known to result in significant performance gains. It is also the primary reason why the Calculator step was created — to avoid the use of JavaScript for simple calculations. |
| JS | Create a copy of a field | No JavaScript is required for this, a Select Values step does the trick. You can specify the same field twice. Once without a rename, once (or more) with a rename. Another trick is to use B=NVL(A,A) in a Calculator step where B is forced to be a copy of A. |
| JS | Data conversion | Consider performing conversions between data types (dates, numeric data, and so on) in a Select Values step. You can do this in the **Metadata** tab of the step. |
| JS | Variable creation | If you have variables that can be declared once at the beginning of the transformation, make sure you put them in a separate script and mark that script as a startup script (right click on the script name in the tab). JavaScript object creation is time consuming so if you can avoid creating a new object for every row you are transforming, this will translate to a performance boost for the step. |
| N/A | Launch several copies of a step | There are two important reasons why launching multiple copies of a step may result in better performance:1. The step uses a lot of CPU resources and you have multiple processor cores in your computer. Example: a JavaScript step.
2. Network latencies and launching multiple copies of a step can reduce average latency. If you have a low network latency of say 5ms and you need to do a round trip to the database, the maximum performance you get is 200 (x5) rows per second, even if the database is running smoothly. You can try to reduce the round trips with caching, but if not, you can try to run multiple copies. Example: a database lookup or table output.
|
| N/A | Manage thread priorities | This feature that is found in the Transformation Settings dialog box under the (**Misc** tab) improves performance by reducing the locking overhead in certain situations. This feature is enabled by default for new transformations that are created in recent versions, but for older transformations this can be different. |
| Select Values | If possible, don't remove fields in Select Values | Don't remove fields in Select Value unless you must. It's a CPU-intensive task as the engine needs to reconstruct the complete row. It is almost always faster to add fields to a row rather than delete fields from a row. |
| Get Variables | Watch your use of Get Variables | May cause bottlenecks if you use it in a high-volume stream (accepting input). To solve the problem, take the Get Variables step out of the transformation (right click, detach) then insert it in with a Join Rows step. Make sure to specify the main step from which to read in the Join Rows step. Set it to the step that originally provided the Get Variables step with data. |
| N/A | Use new text file input | The CSV File Input or Fixed File Input steps provide optimal performance. If you have a fixed width (field/row) input file, you can even read data in parallel. (multiple copies) These new steps have been rewritten using Non-blocking I/O (NIO) features. Typically, the larger the NIO buffer you specify in the step, the better your read performance will be. |
| N/A | When appropriate, use lazy conversion | In instances in which you are reading data from a text file and you write the data back to a text file, use Lazy conversion to speed up the process. The principle behind lazy conversion that it delays data conversion in hopes that it isn't necessary (reading from a file and writing it back comes to mind). Beyond helping with data conversion, lazy conversion also helps to keep the data in "binary" storage form. This, in turn, helps the internal Kettle engine to perform faster data serialization (sort, clustering, and so on). The **Lazy Conversion** option is available in the CSV File Input and Fixed File Input text file reading steps. |
| Join Rows | Use Join Rows | You need to specify the main step from which to read. This prevents the step from performing any unnecessary spooling to disk. If you are joining with a set of data that can fit into memory, make sure that the cache size (in rows of data) is large enough. This prevents (slow) spooling to disk. |
| N/A | Review the big picture: database, commit size, row set size and other factors | Consider how the whole environment influences performance. There can be limiting factors in the transformation itself and limiting factors that result from other applications and PDI. Performance depends on your database, your tables, indexes, the JDBC driver, your hardware, speed of the LAN connection to the database, the row size of data and your transformation itself. Test performance using different commit sizes and changing the number of rows in row sets in your transformation settings. Change buffer sizes in your JDBC drivers or database. |
| N/A | Step Performance Monitoring | Step Performance Monitoring is an important tool that allows you identify the slowest step in your transformation. |
#### Logging best practices
You can improve logging with rotation and other best practices.
For more information, see the **Administer Pentaho Data Integration and Analytics** document.
#### Use checkpoints to restart jobs
Checkpoints let you restart jobs that fail without rerunning from the beginning.
**Add a checkpoint**
1. Open a job.
2. Right-click an entry, then select **Restartable Checkpoint**.
**Delete a checkpoint**
1. Open a job.
2. Right-click an entry, then select **Clear Checkpoint Marker**.
**Set up a checkpoint log**
1. Open a job.
2. Right-click the job canvas, then select **Properties**.
3. Select the **Log** tab, then select **Checkpoints log table**.
4. Configure the log connection and table name.
5. Select **OK**.
#### Use the SQL Editor
Use the SQL Editor to preview and execute DDL generated by the PDI client.
Keep these points in mind:
* Separate statements with semicolons.
* Spoon removes line breaks and semicolons before execution.
* PDI clears database cache for the connection you execute on.
#### Use the Database Explorer
Use Database Explorer to explore configured database connections.
You open it from the Database Connections dialog box.
#### Transactional databases and job rollback
By default, changes are committed as a job or transformation executes.
If you need rollback behavior, make the database transactional.
**Make a transformation database transactional**
1. Open a transformation.
2. Open transformation properties.
3. Select the **Miscellaneous** tab.
4. Select **Make the transformation database transactional**.
5. Select **OK**.
**Make a job database transactional**
1. Open a job.
2. Open job properties.
3. Select the **Transactions** tab.
4. Select **Make the job database transactional**.
5. Select **OK**.
### Add notes to transformations and jobs
Notes help document structure, design decisions, business rules, and dependencies.
#### Create a note
1. Right-click the canvas and select **New Note**.
2. Select **Font Style** to change font and color.
3. Select **Note**, then type your note.
4. Select **OK**.
#### Edit a note
1. Double-click the note.
2. Select **Font Style** to change font and color.
3. Select **Note**, then edit your note.
4. Select **OK**.
#### Reposition a note
1. Drag the note to a new position.
2. Optional: right-click the note:
* Select **Raise Note** to move it above other items.
* Select **Lower Note** to move it below other items.
#### Delete a note
1. Right-click the note.
2. Select **Delete Note**.
### Manage PDI transformations and job schedules
Schedule transformations and jobs to run at specific times or intervals.
#### Schedule a transformation or job
1. Connect to the Pentaho Repository.
2. Open a job or transformation, then select **Action** > **Schedule**.
3. Set start time:
* **Now**, or
* **Date** with date, time, and time zone.
4. Set repeat schedule.
5. Set end time or select **No end**.
6. Optional: enable **Safe mode**.
7. Select **Log Level**.
8. Update argument, parameter, or variable values if needed.
9. Select **OK**.
#### Edit a scheduled run
1. Select the **Scheduler** perspective.
2. Select the schedule, then select **Edit Scheduled Task**.
#### Stop a schedule
1. Select the **Scheduler** perspective.
2. Select the schedule, then select **Stop Scheduled Task**.
#### Enable or disable a schedule
1. Select the **Scheduler** perspective.
2. Select the schedule.
3. Select **Start Scheduler** to enable.
4. Select **Stop Scheduler** to disable.
#### Delete a scheduled run
1. Select the **Scheduler** perspective.
2. Select the schedule, then select **Remove**.
#### Refresh the schedule list
1. Select the **Scheduler** perspective.
2. Select **Refresh**.
### Archived source pages
These pages were merged into this single topic page and moved under [Transforming data with PDI (archive)](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive):
* [Work with transformations](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/work-with-transformations-cp)
* [Work with jobs](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/work-with-jobs)
* [PDI run modifiers](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/pdi-run-modifiers)
* [Partitioning data](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/partitioning-data)
* [Logging and performance monitoring](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/logging-and-performance-monitoring)
* [Add notes to transformations and jobs](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/add-notes-to-transformations-and-jobs)
* [Manage PDI transformations and job schedules](https://docs.pentaho.com/pdia-data-integration/archived-merged-pages/transforming-data-with-pdi-archive/manage-pdi-transformations-and-job-schedules)