SET vs SELECT
-
The artical that Paul White pointed us to about the peformance difference between SELECT and SET is pretty close to spot on percentage wise. I'm just not one to trust such articles and will always do my own testing of claims... helps keep me from becoming an SQL Clone or perpetuator of myths. 😉
If anyone would like to check it out for themselves, here's the code I used...
First, we need a million row Tally table to drive some of the tests. Notice that this will all be done in TempDB so as not to mess with anyone's real code...
[font="Courier New"]--===== Do this in a nice safe place
USE TempDB
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC[/font]
For more information on Tally tables and how they're used to replace WHILE LOOPs, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
I wanted to test for performance a couple of ways. One of the ways was for functions that are used in batch jobs. So, here's a couple of functions that we'll test with...
[font="Courier New"]CREATE FUNCTION dbo.SelectTest()
RETURNS INT
AS
BEGIN
DECLARE @Var1 INT,
@Var2 INT,
@Var3 INT,
@Var4 INT,
@Var5 INT
SELECT @Var5 = 1
SELECT @Var1 = 1,
@Var2 = 2,
@Var3 = 3,
@Var4 = 4,
@Var5 = @Var5+1
RETURN 0
END
GO
CREATE FUNCTION dbo.SetTest()
RETURNS INT
AS
BEGIN
DECLARE @Var1 INT,
@Var2 INT,
@Var3 INT,
@Var4 INT,
@Var5 INT
SET @Var5 = 1
SET @Var1 = 1
SET @Var2 = 2
SET @Var3 = 3
SET @Var4 = 4
SET @Var5 = @Var5+1
RETURN 0
END
GO[/font]
Here's the test code I used. Some hits on the two functions as if we were running a batch and some is done in WHILE LOOPS to simulate consecutive GUI hits. Of course, there's a "dark horse" in there, as well...
[font="Courier New"]--===== Select test on a million row function use
DECLARE @BitBucket INT
SELECT @BitBucket = dbo.SelectTest()
FROM dbo.Tally t
GO
--===== Set test on a million row function use
DECLARE @BitBucket INT
SELECT @BitBucket = dbo.SetTest()
FROM dbo.Tally t
GO
--
GO
--===== Select w/BEGIN on a million row loop
DECLARE @Var1 INT,
@Var2 INT,
@Var3 INT,
@Var4 INT,
@Var5 INT
SELECT @Var5 = 1
WHILE @Var5 <= 1000000
BEGIN
SELECT @Var1 = 1,
@Var2 = 2,
@Var3 = 3,
@Var4 = 4,
@Var5 = @Var5 + 1
END
GO
--===== Select wo/BEGIN on a million row loop
DECLARE @Var1 INT,
@Var2 INT,
@Var3 INT,
@Var4 INT,
@Var5 INT
SELECT @Var5 = 1
WHILE @Var5 <= 1000000
SELECT @Var1 = 1,
@Var2 = 2,
@Var3 = 3,
@Var4 = 4,
@Var5 = @Var5 + 1
GO
--===== Set on a million row loop
DECLARE @BitBucket INT
DECLARE @Var1 INT,
@Var2 INT,
@Var3 INT,
@Var4 INT,
@Var5 INT
SET @Var5 = 1
WHILE @Var5 <= 1000000
BEGIN
SET @Var1 = 1
SET @Var2 = 2
SET @Var3 = 3
SET @Var4 = 4
SET @Var5 = @Var5 + 1
END
GO
--===== Select w/pseudo-cursor on a million row loop
DECLARE @Var1 INT,
@Var2 INT,
@Var3 INT,
@Var4 INT,
@Var5 INT
SELECT @Var5 = 1
SELECT @Var1 = 1,
@Var2 = 2,
@Var3 = 3,
@Var4 = 4,
@Var5 = @Var5 + 1
FROM dbo.Tally t
GO
--
GO
--
GO
[/font]
To measure the performance, I started profiler using the "SQL:BatchCompleted" event with a filter just on the spid I was using. Here's the result... (just one typical set included... many sets where executed).
Now, it looks like the WHILE LOOP beats Set-based code... that because people think that calling a function is Set-based and it's not. UDF's are actually a form of "Hidden RBAR" with additional penalties.
The results of the "Dark Horse" code are highlighted in the image above. The "Dark Horse" code is actually Set Based code using variables that cannot be done using SET. I've used such code to do "successive approximation" problems and it can be very, very fast.
In summary, there's not a huge difference between SELECT and SET at the GUI call level. In large batches and reporting, it can make a fairly large difference in the amount of wait time (with SELECT being the clear winner) between the request for a report and when the customer sees it. SET also makes it impossible to do something Set-Based (ironic, isn't it?).
Heh... and that's why I prefer to use SELECT for variable assignment instead of SET... it may not be ANSI, but I like it for performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Great stuff Jeff! I feel bad for being so lazy now 🙂
-
RBarryYoung (5/9/2009)
Paul White (5/9/2009)
RBarryYoung (5/9/2009)
Ah yes, been there many times.I know. It seems to be the same most everywhere. Kinda depressing!
RBarryYoung (5/9/2009)
In such circumstances I usually ask the decision maker (TPM in your case) if they had thought about why we were testing things if we weren't going to use the results anyway?The answer, inevitably, is "because you have to test!" together with a frown and a dismissive look.
I used to sometimes try to pursue the logic, but met with extremely limited success.
Now I just let them get on with it 😉
I have found that as I have gotten older, I am actually having more success at pursuing this with them. Heh, either the ravages of time and age are also lending me some aura of wisdom and gravitas or else I really am just getting better at finding the right button to push ("But Joe, their your testers on your project. If you just ignore it and then it blows up on the customer, you know that some SOB here who wants your job will just use that against you. You've got to CYA, it's the only smart thing to do.") 😀
(and yes, sometimes FUD can be used for good 🙂 )
Ermm... For sure ... some SOB ... and you've to CYA ... FUD is good. :ermm:
What the heck are you speaking about??? 😀
I tried to google those acronyms and I found between three and five different definitions for each.
-
Hey Flo!
SOB = son-of-a-b i tch
CYA = cover your a r s e
FUD = fear, uncertainty and doubt
Paul
-
:laugh:
Well, I googled in wrong direction. I forgot that you use acronyms for urban expressions (like WTF).
Thanks for t r a n s l a t i o n !
Flo
-
Florian Reischl (5/10/2009)
:laugh:Well, I googled in wrong direction. I forgot that you use acronyms for urban expressions (like WTF).
Thanks for t r a n s l a t i o n !
Flo
Yes, that's right, I generally use acronyms as euphemisms for "colorful" language. Sorry, I forget that this can be very confusing for non-native english speakers.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Always good to learn new things 😉
Greets
Flo
-
Good to see you learned some new acronyms flo :-D:-D
Viewing 8 posts - 76 through 82 (of 82 total)
You must be logged in to reply to this topic. Login to reply