Friday, December 26, 2008

Performance of workflow - Most feared, but the easiest

With the newer and newer versions of PowerCenter, there is added flexibility for the developer to build better-performing workflows. Though performance tuning has been the most feared part of development, it is the easiest, if the intricacies are known. We shall discuss one major problems for performance - SQL query tuning.

SQL queries/actions occur in PowerCenter in one of the below ways.

  • Relational Source Qualifier
  • Lookup SQL Override
  • Stored Procedures
  • Relational Target

Accessing an execution plan for the query is the best way to tune it. Look for any table scans and remember "Full Table Scans are not evil", "Indexes are not always fast", and “Indexes can be slow too". Analyse the table data to see if picking up 20 records out of 20 million is best using index or using table scan. Fetching 10 records out of 15 using index is faster or using full table scan is easier.

Many times the relational target indexes create problem for the performance of the actual loading of records into the relational target. It may surprise you (some times?), the way PowerCenter has reduced the time of loading the target data if you disable the indexes and then enable them in post-SQL again.

Please note that the flow has to also consider the integrity constraints when you have disabled the indexes before loading.


Bharath.S.Parthasarathy said...

Dude... I know this must be usual to you, from my end. i got to say that you are amazing in informatica..
Please keep posting..

Radhakrishna Sarma said...

Thank you Bharath. Keep visiting my blog. I'll write whenever I find time.


Leevan said...

Nice article. I'm the fan of your blogs. I couldn't see much from you recent days. .. hope you are busy .. Your articles helped me and my team a lot. I've implemented many tips from you in my projects. Thank you once again.


HTML Hit Counters