Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

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
  • Transformation
  • Lookup
  • 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…

Advertisements

One Response to “Informatica ETL Tuning Steps”

  1. chandra08 said

    Hi,
    Thanks for your comments about Source extract SQL bottleneck issue fix techniques.
    CAn you post the comments how to fix Transformations and Target Battlenecks?

    Thanks,
    Chandra

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