CODEWITHSUNDEEP
sql-serversql-server-2008ssms-2014
Greg Gum
Greg Gum

Reputation: 37905

How to determine which query is more efficient in SSMS 2014

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.)

Upvotes: 3

Views: 411

Answers (2)

Greg Gum
Greg Gum

Reputation: 37905

A way to compare to queries in SSMS:

In SSMS, include both queries on a single tab. Select the Query menu, and select "Include client Statistics"

Comment out one of the queries, and then run the other one. Select the "Client Statistics" tab and view the stats.

Now comment out the other query, uncomment the first one, and run again. Both statistics will be shown in the Client Statistics tab and you can easily see which is more efficient.

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 2

Related Questions