Informatica ETL Tuning Steps
Posted by Dylan Wan on March 14, 2009
First, we need to identify where the performance problem come from. The bottleneck can be:
- Source Extract
- Target Write
I will talk about how to isolate the problems and go over each about how to investigate the problem in each of these area.
1. Source Extract SQL
The simple way to identify the issue is to run the SQL against the database and gather the statistics. You can get the database specific SQL from the session log. Also, you can create a mapping with only the source qualifier with any transformation and write directly to a file.
If SQL Extract is the bottleneck, this a a typical SQL tuning task. Here are typical way to fix the issues:
- Analyze the tables: The database optimizer cannot generate a good plan without the statistics.
- Add, change, or remove indexes
- Add or modify the database hints
- Rewrite the SQL
- Try Parallel Query
- Revisit the requirements and remove the unnecessary table joins
- Consider some database features like analytical function
If SQL tunning cannot fix the issue, another option is to break the extract into multiple separate extract programs and create some temporary tables to hold the data.
To be continued…