改善添加单值列的性能

Question

经过试验，令人惊讶的是，在处理大型表格时，将点表左联接速度要比将单个值分配给列要快得多。所谓点表是指1x1的表格（即一行一列）。

方法1。 在说“将单个值分配”时，我指的是以下方法（速度较慢）：

SELECT A.*, 'Value' as NewColumn,
FROM Table1 A

方法二。 通过 左连接 点表，我指的是这个（更快）：

WITH B AS (SELECT 'Value' as 'NewColumn')
SELECT * Table1 A
LEFT JOIN B
ON A.ID <> B.NewColumn

现在是我的问题的核心。有人能建议我如何摆脱整个ON子句吗：

ON A.ID <> B.NewColumn?

检查连接条件似乎是浪费时间，因为表A的键必须不等于表B的键。如果t1.ID的值与'Value'相同，则它会将行从结果中排除。删除该条件或者将<>更改为=符号，似乎可以进一步节省连接的性能空间。

2015年2月23日更新
悬赏问题面向性能专家。我在问题和答案中提到的哪种方法最快。
方法1 简单赋值,
方法2 左连接一个点表格,
方法3 交叉连接一个点表格（感谢Gordon Linoff的答案）
方法4 在奖励期间可能建议的任何其他方法。
根据我对3种方法查询执行时间的经验测量 - 第二种采用LEFT JOIN的方法是最快的。然后是CROSS JOIN方法，最后是简单的赋值。令人惊讶。需要拥有所罗门之剑的性能专家来确认或否认它。

- Przemyslaw Remin

---

也许这是罕见的快速执行 LEFT JOIN 场景，由 dbenham 在 Stack Overflow 上回答并获得 +50 悬赏（不是被标记为已接受的顶部答案）的描述。https://dev59.com/yHE85IYBdhLWcg3wikEu - Przemyslaw Remin

@gvee 不，只要条件满足，也就是说只要在A.ID中没有任何值等于B.NewColumn，你就可以得到方法2中所示的结果图片。 - Przemyslaw Remin

@PrzemyslawRemin 我明白了。我只是在测试它并发现了我的错误。顺便说一下，只有在连接中的数据类型可比较时才能起作用。将条件更改为 ON 1=1 是否更简单呢？一些非常快速的测试表明这样稍微更有效率。顺便说一句，在我所有的测试中，CROSS JOIN 都是最好的选择... - gvee

我只是在想，如果您在方法1中使用变量声明“value”，SQL核心引擎是否可以以更好的方式编译/工作：DECLARE @value varchar(10) SET @value = 'Value' SELECT A.*，@value作为新列 FROM Table1 A - Rubik

你有看过查询计划吗？我猜你的left outer join版本有一个并行计划，而其他版本没有。如果您可以发布表结构、行数和连接与常量版本的计划，那将会很有帮助。在我的测试中，我发现对于每行特定字节数，只有当计划是并行的时，使用左连接版本才能获得更好的性能。此外，您将varchar常量连接到一个看起来是整数的ID列上。这样不应该工作，请发布表结构。 - Mikael Eriksson

Answer 1

我很惊讶这个简单的表达式能更快，但你似乎需要一个 cross join ：

WITH B AS (SELECT 'Value' as NewColumn)
SELECT *
FROM Table1 A CROSS JOIN
     B;

我使用这个结构在查询中放置“参数”（可以轻松更改的值）。但是，我不明白为什么这样做会更快。如果表达式更复杂（例如子查询或非常复杂的计算），那么这种方法只会评估一次。在原始查询中，它通常只会被评估一次，但可能有情况下它会被每一行评估。

- Gordon Linoff

Answer 2

太多的文本无法在评论中添加，因此我将其作为答案添加，尽管我实际上更多地是在回答问题（**）。

我认为这可能是一种“情况有所不同”的情况。我认为这很大程度上取决于所涉及的行数，甚至更多地取决于数据之后会发生什么。它是否只是返回，是否在以后使用GROUP BY或DISTINCT，我们是否进一步进行JOIN或计算等等。

无论如何，我认为这是一个有趣的问题，因为我必须通过艰苦的方式发现，在单行临时表中有十几个“参数”比预先分配给12个变量更快。许多月前，我得到的代码对我来说看起来像是一个荒谬的结构，所以我重写它使用@variables代替。这是在一个+1000行的存储过程中需要从中挤出一些额外性能的情况下。经过相当多的重构，结果运行速度比改变之前明显慢了？？！

我从未真正理解为什么，当时只是再次回到旧版本。我最好的猜测是参数嗅探与临时表上（自动创建的？）统计信息的奇怪组合；如果有人能解答你的问题，那可能也会引导我得到答案 =)

（**：我意识到SO不是论坛，所以我提前道歉，只是想说一下，OP观察到的行为并非完全是个人经历）

- deroby

Answer 3

您也可以尝试使用CROSS APPLY：

SELECT A.*, B.*,
FROM Table1 A
CROSS APPLY(SELECT 'Value' as 'NewColumn') B

- Giorgi Nakeuri

Answer 4

你可以尝试将数据插入到临时表中，而不是直接输出到屏幕上：

SELECT A.*, 'Value' as NewColumn
INTO #Table1Assign
FROM Table1 A

并且

WITH B AS (SELECT 'Value' as 'NewColumn')
SELECT * Table1 A
INTO #Table1Join
LEFT JOIN B
ON A.ID <> B.NewColumn

这将实际传输和呈现数据到SSMS的因素排除在外，这可能是由于网络减速或客户端处理引起的。

当我在一个100万行的表上运行时，使用简单赋值方法始终能获得更好的性能，即使我切换到CROSS JOIN作为连接方法。

- jlee-tessik

Answer 5

我怀疑第二种方法会更快，需要使用三个select和left join。 首先，您应该反复测试相同的查询以获取各种样本数据。

真正的情况是什么？

内连接肯定比左连接更快。

这怎么样？

Declare @t table(id int,c2 varchar(10))
INSERT INTO @T
select 1,'A' union all
select 2,'A' union all
select 3,'B' union all
select 4,'B' 

Declare @t1 table(nEWcOL varchar(10))
INSERT INTO @T1 Values('Value')

-- #Approach1
--SELECT * FROM @T outer apply
 --@t1

--Create index on both join column
 --#Approach2
SELECT * FROM @T A inner join
 @t1 b on a.c2<>b.nEWcOL

--#Approach3
Declare @value varchar(20)
Select @value= nEWcOL from @t1

select *,@value value from @t

- KumarHarsh

Answer 6

在SQL中，使用Select *无法正确使用索引，您应该始终指定列。

除此之外，我会使用

DECLARE @Value VARCHAR(30) = 'Value'
SELECT t.Id, t.C2, @Value NewColumn
FROM Table1 t

- TonyM

---

虽然选择所有列可能会导致查找，否则可能通过仅从（覆盖）索引中选择所需字段来避免；但显式选择所有字段与通过 * 符号隐式选择字段在任何方面都没有区别。 - deroby