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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

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

Leave a Comment

(required) 
(required) 
Submit

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

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement