With SSMS, how can I determine which query is more efficient? I prefer A, but I am told that the subquery is executed once for each row in Transmission, and thus B is preferred.
A
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
EDIT:This is the Statistics from using SET STATISTICS ON:
A.
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.
So, as far as I can tell, there is no difference in the efficiency.
EDIT 2: So I get it now: Since the sub-query is NOT a correlated
sub-query, it only executes once. https://en.wikipedia.org/wiki/Correlated_subquery (Thanks to @destination_data for the link.)
It would require examining the actual execution plans but I suspect those are going to be identical. Those subqueries should execute only once because they are not correlated and the engine is pretty good about seeing that stuff.
If you really want to get into the details check out this free e-book from Grant Fritchey. https://www.red-gate.com/library/sql-server-execution-plans-2nd-edition