Table of Contents

Search

  1. Preface
  2. Performance Tuning Overview
  3. Bottlenecks
  4. Optimizing the Target
  5. Optimizing the Source
  6. Optimizing Mappings
  7. Optimizing Transformations
  8. Optimizing Sessions
  9. Optimizing Grid Deployments
  10. Optimizing the PowerCenter Components
  11. Optimizing the System
  12. Using Pipeline Partitions
  13. Appendix A: Performance Counters

Performance Tuning Guide

Performance Tuning Guide

Optimizing IIF Functions

Optimizing IIF Functions

IIF functions can return a value and an action, which allows for more compact expressions. For example, you have a source with three Y/N flags: FLG_A, FLG_B, FLG_C. You want to return values based on the values of each flag.
You use the following expression:
IIF( FLG_A = 'Y' and FLG_B = 'Y' AND FLG_C = 'Y',   VAL_A + VAL_B + VAL_C,    IIF( FLG_A = 'Y' and FLG_B = 'Y' AND FLG_C = 'N',     VAL_A + VAL_B ,      IIF( FLG_A = 'Y' and FLG_B = 'N' AND FLG_C = 'Y',       VAL_A + VAL_C,        IIF( FLG_A = 'Y' and FLG_B = 'N' AND FLG_C = 'N',         VAL_A ,          IIF( FLG_A = 'N' and FLG_B = 'Y' AND FLG_C = 'Y',           VAL_B + VAL_C,            IIF( FLG_A = 'N' and FLG_B = 'Y' AND FLG_C = 'N',             VAL_B ,              IIF( FLG_A = 'N' and FLG_B = 'N' AND FLG_C = 'Y',               VAL_C,                IIF( FLG_A = 'N' and FLG_B = 'N' AND FLG_C = 'N',                 0.0, ))))))))
This expression requires 8 IIFs, 16 ANDs, and at least 24 comparisons.
If you take advantage of the IIF function, you can rewrite that expression as:
IIF(FLG_A='Y', VAL_A, 0.0)+ IIF(FLG_B='Y', VAL_B, 0.0)+ IIF(FLG_C='Y', VAL_C, 0.0)
This results in three IIFs, two comparisons, two additions, and a faster session.

0 COMMENTS

We’d like to hear from you!