IF and CASE Statements

Another category of statement that is highly amenable to code optimization is the conditional statement categoryIF 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 statement
• Stopping the comparison process as early as possible

22.6.1. Test for the Most Likely Conditions First

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

Example 22-15 shows a more efficiently formed IF statement. In this variation, the first condition will evaluate as true in the majority of executions and no further comparisons will be necessary.

Example 22-15. Optimized IF statement

Figure 22-7 shows the performance improvement gained by reordering the IF statement so that the most commonly satisfied condition is evaluated first.

Figure 22-7. Effect of optimizing an IF statement by reordering comparisons

22.6.2. Avoid Unnecessary Comparisons

Sometimes an IF or CASE statement will be constructed that has some kind of common condition in each comparison clause. For instance, in Example 22-16, each of the expressions in the IF statement includes an employee_status='U' condition. Even if the employee_status is not equal to "U", each of these comparisons will need to be evaluatedadding some processing overhead.

Example 22-16. IF statement with common condition in each expression

Example 22-17 shows a more optimized IF structure. In this example, the employee_status is checked first and thenonly if employee_status='U'are the additional comparisons are evaluated. Figure 22-8 demonstrates the optimization.

Example 22-17. Nested IF statement to avoid redundant comparisons

To be honest, under most circumstances, tuning IF statements will not greatly improve the performance of your code. The overhead of SQL processing will usually dominate overall execution time. Consequently, we suggest that when it comes to conditional statements, you should prioritize writing readable and maintainable code. If a particular IF statement becomes a bottleneck, then you should consider a rewrite that will improve performance even at the expense of maintainability.

Figure 22-8. Effect of nesting an IF statement to eliminate redundant comparisons

22.6.3. CASE Versus IF

We wondered if there was any performance difference between a CASE statement and an equivalent IF statement. We thought that CASE might be more optimal for comparing a variable against a range of set values, so we speculated that this statement:

CASE customer_code WHEN 1 THEN SET process_flag=7; WHEN 2 THEN SET process_flag=9; WHEN 3 THEN SET process_flag=2; ELSE SET process_flag=0; END CASE;

might be more efficient than the equivalent IF statement:

IF customer_code= 1 THEN SET process_flag=7; ELSEIF customer_code= 2 THEN SET process_flag=9; ELSEIF customer_code=3 THEN SET process_flag=2; ELSE SET process_flag=0; END IF;

In fact, the opposite turned out to be true. The IF statement is roughly 15% faster than the equivalent CASE statementpresumably this is the result of a more efficient internal algorithm for IF in the MySQL code.

As noted earlier, we advise you to structure your stored program's statements primarily for readability and maintainability, since it is almost always the elapsed time of SQL statements that dominates performance. However, if performance is critical, you may want to make a habit of using IF statements rather than CASE statements in your code.