Why Select faster than Set statement

.net, c#, sql, sql-server

Solution

It's not (at least in the example you currently have), you are running 6 SET statements and a single SELECT statement for each iteration of the loop, therefore, I would expect the SELECT to be faster.

I would suggest comparing like for like;

CREATE Procedure [dbo].[SelectLoop]
As Begin 
declare @counter int = 0
    While @counter < 1000000
    Begin
        select @counter = @counter + 1
    End
End

CREATE Procedure [dbo].[SetLoop]
As Begin 
declare @counter int = 0
    While @counter < 1000000
    Begin
       set @counter = @counter + 1
    End
End

This will give a more accurate indication as to whether there are differences.

Problem

i did 2 basic tests 1- ``` Create Procedure [dbo].[SetLoop] As Begin declare @counter int = 0 ,@a int,@b int,@c int,@d int,@e int While @counter < 1000000 Begin set @a=1 set @b=2 set @c=3 set @d=4 set @e=5 set @counter = @counter + 1 End End ``` ``` create procedure SelectLoop As Begin declare @counter int =0 ,@a int,@b int,@c int,@d int,@e int While @counter < 1000000 Begin select @a=1, @b=2, @c=3, @d=4, @e=5,@counter = @counter + 1 End End ``` ``` var setTimes = new List<double>(); for (var i = 0; i < 50; i++) { stopwatch.Start(); dataContext.SetLoop(); stopwatch.Stop(); setTimes.Add(stopwatch.ElapsedMilliseconds); stopwatch.Reset(); } var selectTimes = new List<double>(); for (var i = 0; i < 50; i++) { stopwatch.Start(); dataContext.SelectLoop(); stopwatch.Stop(); selectTimes.Add(stopwatch.ElapsedMilliseconds); stopwatch.Reset(); } var setAverage = setTimes.Sum() / setTimes.Count; var selectAverage = selectTimes.Sum()/selectTimes.Count; ``` Results ``` setAverage : 2418.44 selectAverage : 1037.52 setAverage : 2513.8 selectAverage : 1025.98 setAverage : 2496.52 selectAverage : 996.36 ``` results mean Select faster than Set by `one and half` time exactly by `142.78%` 2- ``` While @counter < 1000000 Begin select @a=1 select @b=2 select @c=3 select @d=4 select @e=5 select @counter = @counter + 1 End ``` ``` While @counter < 1000000 Begin set @a=1 set @b=2 set @c=3 set @d=4 set @e=5 set @counter = @counter + 1 End ``` Results ``` setAverage : 2518.58 selectAverage : 2504.44 setAverage : 2474.5 selectAverage : 2529.48 setAverage : 2511.22 selectAverage : 2514.04 ``` results means they are the same that's mean if you need to set one variable prefer to use `Select` cuz in the future if you want to set another one just `,@w=3` will be added but if you want to set more than one variable defiantly you need to use `Select` it's easier to write, faster to be executed But I was wondering why `Select` Faster than `Set` in the first test?

Original source