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 developersuse 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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More