THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Assigning multiple variables with one SELECT works faster

Yesterday MVP Aaron Bertran posted his "best practices checklist".

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

Among other recommendations, he suggested that all assigments should be done in on SELECT, not in a series of SET assimgments.
In fact, assigning variables in a single SELECT not only looks better but also performs significantly faster. I have known that for a while, but after reading Aaron's post I decided to verify if it is still true for SQL Server 2005.
It still is true. The following script demonstrates it. To emphasize the effect, I compared 100 SET assignments with assigning all 100 variables in one SELECT. Here it is:

--SELECT ' @i'+CAST(Number AS VARCHAR(10))+' INT,' FROM Data.Numbers WHERE Number<100;

DECLARE
-- decalration generated by the following SELECT
--SELECT ' @i'+CAST(Number AS VARCHAR(10))+' INT,' FROM Data.Numbers WHERE Number<100;
 @i0 INT,
 @i1 INT,
 @i2 INT,
(snip)
 @i99 INT;

SET NOCOUNT ON;
DECLARE @i INT, @before DATETIME, @after DATETIME;
SELECT @i=0, @before = Getdate();
WHILE @i<10000 BEGIN
SET @i0=1;
SET @i1=1;
(snip)
SET @i99=1;
SET @i=@i+1;
END
SELECT DATEDIFF(ms, @before, Getdate()) AS Duration_100_SETs;

SELECT @i=0, @before = Getdate();
WHILE @i<10000 BEGIN
SELECT
-- add code generated by
-- SELECT ' @i'+CAST(Number AS VARCHAR(10))+'=1,' FROM Data.Numbers WHERE Number<100;
 @i0=1,
 @i1=1,
(snip)
 @i99=1;
SET @i=@i+1;
END
SELECT DATEDIFF(ms, @before, Getdate()) AS Duration_one_SELECT;

The difference is consistently significant:

Duration_100_SETs
-----------------
576

Duration_one_SELECT
-------------------
63

Published Friday, October 31, 2008 4:33 PM by Alexander Kuznetsov

Comments

 

Madhivanan said:

November 1, 2008 5:31 AM
 

Francesco Quaratino said:

simply wow!

November 3, 2008 3:34 AM
 

Alexander Kuznetsov said:

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before.

January 25, 2009 1:48 PM
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Privacy Statement