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
resultsmean 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 ?