如何确定哪个查询在2014年SSMS中更有效

Question

使用SSMS，如何确定哪个查询更有效？我更喜欢A，但是我被告知子查询对传输中的每一行执行一次，因此B是首选的。

一个

Update t set t.transmission_status_id = 
(select transmission_status_id from transmission_status where code = 'TRANSLATED')
from transmission t
where t.transmission_status_id = 
(select transmission_status_id from transmission_status where code = 'RECEIVED')

B

declare @transmission_status_TRANSLATED INT = (select transmission_status_id from transmission_status where code = 'TRANSLATED')
declare @transmision_status_RECEIVED INT = (select transmission_status_id from transmission_status where code = 'RECEIVED')

Update t set t.transmission_status_id = @transmission_status_TRANSLATED
from transmission t 
where t.transmission_status_id = @transmision_status_RECEIVED

编辑:这是在以下方面使用SET统计信息的统计信息：

一个。

Table 'transmission_status'. Scan count 1, logical reads 2, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'transmission_status'. Scan count 1, logical reads 2, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'transmission'. Scan count 1, logical reads 778, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

B

Table 'transmission'. Scan count 1, logical reads 778, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'transmission_status'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

所以，据我所知，效率是没有差别的。

编辑2:所以我现在明白了:因为子查询不是correlated子查询，所以它只执行一次。subquery (感谢@destination_data提供的链接)。

Answer 1

这将需要审查实际的执行计划，但我怀疑这些计划将是相同的。这些子查询应该只执行一次，因为它们是不相关的，而且引擎可以很好地看到这些内容。

如果你真的想进入细节，看看这个免费电子书从格兰特弗里奇。https://www.red-gate.com/library/sql-server-execution-plans-2nd-edition

Answer 2

比较SSMS中查询的一种方法：

在SSMS中，将两个查询都包含在一个选项卡上。选择“查询”菜单，并选择“包含客户端统计信息”

注释掉其中一个查询，然后运行另一个查询。选择“客户统计”选项卡并查看统计数据。

现在注释掉另一个查询，取消对第一个查询的注释，然后再次运行。这两个统计信息都将显示在选项卡中，您可以很容易地看到哪个更有效。