Another category of statement that is highly amenable to
code optimization is the conditional statement category—`IF`

and `CASE`

statements. This is especially true if
these statements are called repetitively within a loop. The essence of
optimizing conditional statements like `IF`

and `CASE`

is to reduce the number of comparisons
that are performed. You can do this by:

Testing for the more likely matches earlier in the

`IF`

or`CASE`

statementStopping the comparison process as early as possible

When constructing `IF`

and
`CASE`

statements, try to minimize
the number of comparisons that these statements are likely to make
by testing for the most likely scenarios first. For instance,
consider the `IF`

statement shown
in Example 22-14. This
statement maintains counts of various percentages. Assuming that the
input data is evenly distributed, the first `IF`

condition (`percentage>95`

) will match about once in
every 20 executions. On the other hand, the final condition will
match in three out of four executions. So this means that for 75% of
the cases, all four comparisons will need to be evaluated.

Example 22-14. Poorly constructed IF statement

IF (percentage>95) THEN SET Above95=Above95+1; ELSEIF (percentage >=90) THEN SET Range90to95=Range90to95+1; ELSEIF (percentage >=75) THEN SET Range75to89=Range75to89+1; ELSE SET LessThan75=LessThan75+1; END IF;

Example 22-15
shows a more efficiently formed `IF`

statement. In this variation, the ...

Start Free Trial

No credit card required