问题描述
我一直相信对于 T-SQL 中的单变量赋值,set
是处理事情的最佳方式,原因有二:
I've been led to believe that for single variable assignment in T-SQL, set
is the best way to go about things, for two reasons:
- 这是变量赋值的 ANSI 标准
- 它实际上比执行 SELECT(对于单个变量)更快
所以...
SELECT @thingy = 'turnip shaped'
变成
SET @thingy = 'turnip shaped'
但是快有多快?我真的会注意到差异吗?
But how fast, is fast? Am I ever really going to notice the difference?
推荐答案
SET 在单次运行时速度更快.你可以很容易地证明这一点.它是否有所不同取决于您,但我更喜欢 SET,因为如果所有代码所做的都是赋值,我就看不到 SELECT 的意义.我更喜欢将 SELECT 限制在来自表、视图等的 SELECT 语句中.
SET is faster on single runs. You can prove this easily enough. Whether or not it makes a difference is up to you, but I prefer SET, since I don't see the point of SELECT if all the code is doing is an assignment. I prefer to keep SELECT confined to SELECT statements from tables, views, etc.
这是一个示例脚本,运行次数设置为 1:
Here is a sample script, with the number of runs set to 1:
SET NOCOUNT ON
DECLARE @runs int
DECLARE @i int, @j int
SET @runs = 1
SET @i = 0
SET @j = 0
DECLARE @dtStartDate datetime, @dtEndDate datetime
WHILE @runs > 0
BEGIN
SET @j = 0
SET @dtStartDate = CURRENT_TIMESTAMP
WHILE @j < 1000000
BEGIN
SET @i = @j
SET @j = @j + 1
END
SELECT @dtEndDate = CURRENT_TIMESTAMP
SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SET_MILLISECONDS
SET @j = 0
SET @dtStartDate = CURRENT_TIMESTAMP
WHILE @j < 1000000
BEGIN
SELECT @i = @j
SET @j = @j + 1
END
SELECT @dtEndDate = CURRENT_TIMESTAMP
SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SELECT_MILLISECONDS
SET @runs = @runs - 1
END
结果:
运行 #1:
SET_MILLISECONDS
SET_MILLISECONDS
5093
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5186
运行 #2:
SET_MILLISECONDS
SET_MILLISECONDS
4876
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5466
运行 #3:
SET_MILLISECONDS
SET_MILLISECONDS
4936
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5453
运行 #4:
SET_MILLISECONDS
SET_MILLISECONDS
4920
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5250
运行 #5:
SET_MILLISECONDS
SET_MILLISECONDS
4860
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5093
奇怪的是,如果您将运行次数提高到 10 次,则 SET 开始落后.
这是 10 次运行的结果:
Here is a 10-run result:
SET_MILLISECONDS
SET_MILLISECONDS
5140
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5266
SET_MILLISECONDS
SET_MILLISECONDS
5250
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5466
SET_MILLISECONDS
SET_MILLISECONDS
5220
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5280
SET_MILLISECONDS
SET_MILLISECONDS
5376
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5280
SET_MILLISECONDS
SET_MILLISECONDS
5233
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5453
SET_MILLISECONDS
SET_MILLISECONDS
5343
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5423
SET_MILLISECONDS
SET_MILLISECONDS
5360
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5156
SET_MILLISECONDS
SET_MILLISECONDS
5686
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5233
SET_MILLISECONDS
SET_MILLISECONDS
5436
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5500
SET_MILLISECONDS
SET_MILLISECONDS
5610
SELECT_MILLISECONDS
SELECT_MILLISECONDS
5266
这篇关于T-SQL 中 SELECT 与 SET 的速度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!