Why choose faster than Set

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

the average value of the result. Choose faster than specified in one and halfexactly142.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

mean they are the same

which means that if you need to set one variable, prefer to use Selectcuz in the future, if you want to set another one, it ,@w=3will only be added, but if you want to specify more than one variable defiantly, you need to use Select it easier to write, faster to execute

, Select , Set ?

+5
1

( , , ), 6 SET SELECT , , SELECT .

, :

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

.

+7

All Articles