In the remainder of this chapter we will look at techniques for the optimization of stored program code that does not involve SQL statements, starting with the optimization of loops .
Because the statements executed within a loop can be executed many times, optimizing loop processing is a basic step when optimizing the performance of a program written in any language. The MySQL stored program language is no exception.
The first principle of optimizing a loop is to move calculations out of the loop that don’t belong inside the loop (these are known as loop-invariant statements , since they do not vary with each execution of the loop body). Although such a step might seem obvious, it’s surprising how often a program will perform calculations over and over within a loop that could have been performed just once before the start of loop execution.
For instance, consider the stored program in Example 22-9. This loop is
actually fairly inefficient, but at first glance it’s not easy to
spot where the problem is. Fundamentally, the problem with this
stored program is that it calculates the square root of the
i variable for every value of the
j variable. Although there are only 1,000
different values of
i, the stored
program calculates this square root five
WHILE (i<=1000) DO SET j=1; WHILE (j<=5000) DO SET rooti=sqrt(i); SET rootj=sqrt(j); SET sumroot=sumroot+rooti+rootj; SET ...