Why Select faster than Set statement

Viewed 149

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?

1 Answers
Related