Performance Tuning Guide

10.5
- 10.5.6
- 10.4.0

Back Next

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.

Rename Saved Search

Table of Contents

Performance Tuning Guide

Performance Tuning Guide

Optimizing IIF Functions

Optimizing IIF Functions