MySQL Stored Procedure Programming

Name

PER-06: Move loop invariant expressions outside of loops

Synopsis

Whenever you set out to tune your stored programs (having completed your SQL optimization), you should first take a look at your loops. Any inefficiency inside a loop's body will be magnified by the multiple executions of that code.

A common mistake is to put execute code within the body of a loop that has the same result with each iteration of the loop. When you identify such a scenario, extract the static code, assign the outcomes of that code to one or more variables in advance of the loop, and then reference those variables inside the loop.

Example

At first glance, this loop block seems sensible enough, but in reality it is quite inefficient:

      WHILE (i<=1000) do
          SET j=1;
          WHILE (j<=1000) do
            SET counter=counter+1;
            SET sumroot=sumroot+sqrt(i)+sqrt(j);
            SET j=j+1;
          END WHILE;
          SET i=i+1;
      END WHILE;

This code contains two loops: we calculate the square root of i inside of the inner loop, even though it only changes for each iteration of the outer loop. Consequently, we calculate the square root 1,000,000 times, even though we have only 1,000 distinct values.

Here's the optimized version of that same code:

    WHILE (i<=@i) do
          SET rooti=sqrt(i);
          SET counter=counter+1;
          SET j=1;
          WHILE (j<=@j) do
            SET sumroot=sumroot+rooti+sqrt(j);
            SET j=j+1;
          END WHILE;
          SET i=i+1;
      END WHILE;

A small change, but one that will have a massive effect on performance.