T-SQL 中 SELECT 与 SET 的速度 [英] Speed of SELECT vs. SET in T-SQL

问题描述

我一直相信对于 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 的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！