Loops are fairly common in SQL Server
stored procedures. If you discover a loop in a
stored procedure, you might find that the code
iterates thousands of times before the loop
exits. One way to improve the performance of
loops is to optimize variable handling.
Do you know the difference between
using the SELECT and SET commands
to assign values to variables? Many SQL
Server developers believe that the commands perform the same function, except
that SELECT was designed to return data,
whereas SET is optimized to assign values
to local variables; therefore, most developers
use SET to change variables. This action is
correct most of the time.
Certainly, if you're setting a variable's
value, SET is the recommended command;
however, sometimes SET isn't the best
command for the job. Every time you use
the SET command, an assignation language
element executes. Thus, if you're setting
multiple variables, each SET command runs
like a SELECT @variable statement. Consider the following two statements, which
are functionally equivalent:
SET @iOne = 1;
SET @iTwo = 2;
SET @iThree = 3;
SET @iFour = 4;
SELECT
@iOne = 1,
@iTwo = 2,
@iThree = 3,
@iFour = 4;
Although the statements are similar, the
SELECT statement runs significantly faster
than the SET statement. Each SET statement runs individually and updates one
value per execution, whereas the entire
SELECT statement runs as a whole and
updates all four values in one execution.
To test this action, I ran each statement
in a loop one million times. I found that the
SET block ran in 0.0094 milliseconds (ms) on average and the SELECT statement ran
in only 0.0039 ms. The SELECT statement
ran 59 percent faster than the SET statement.
If you're looping through a query only a few
hundred thousand times, you probably won't
notice a difference between the speed of the
two commands; however, the difference in
speed might be important to an end user
who must wait 10 seconds rather than just 3
seconds for a report to generate.
Another thing to consider is that you can
embed your variable updates in preexisting
SELECT and UPDATE statements within a
loop. In the benchmark tests I ran, each variable that I added to a SELECT or UPDATE
statement required an additional 0.00016
ms. By comparison, adding a SET statement
required 0.0027 ms. I was surprised to learn
that adding a SET statement introduces
a processing delay that is 1687.5 percent
longer than the processing delay caused by
adding a variable to a previously existing
SELECT assignation statement. If you're
looping through rows, you can further expedite your loop by altering multiple variables in
one UPDATE statement, as Listing 1 shows.