Performance Tuning Guide

10.5.6
- 10.5

Back Next

SQL Transformation Optimization

Each time the Data Integration Service processes a new query in a mapping, it calls a function called SQLPrepare to create an SQL procedure and pass it to the database. When the query changes for each input row, it might decrease performance.

Consider the following solutions for SQL transformation bottlenecks:

Do not use transaction statements in an SQL transformation query.: When an SQL query contains commit and rollback query statements, the Data Integration Service must recreate the SQL procedure after each commit or rollback. To optimize performance, do not use transaction statements in an SQL transformation query.
Enable early selection or push-into filter optimization methods with the SQL transformation.: To increase performance, the consider enabling the early selection or push-into optimization method with the SQL transformation.