SELECT vs. SET: Optimizing Loops

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.