3

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

Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • Doesn't the first step always involve `EXPLAIN PLAN`? – Ken White Apr 15 '16 at 16:12
  • I am looking at the Execution Plan, but it's not clear to me from the data which one is more efficient. – Greg Gum Apr 15 '16 at 16:15
  • `Execution Plan/STATISTICS IO` should help you – Pரதீப் Apr 15 '16 at 16:18
  • 1
    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. – Sean Lange Apr 15 '16 at 16:27
  • 1
    Too late! Was going to mention correlation but @SeanLange beat me to it. For more see the [wiki article](https://en.wikipedia.org/wiki/Correlated_subquery). – David Rushton Apr 15 '16 at 16:29
  • 2
    One method is to place both queries in the same SSMS tab. Press Ctrl+L to view the execution plan. At the top of each plan you are looking for *Query cost (relative to the batch): xx%*. Between the different versions of your query this will always total 100%. Whichever has the higher number is expected to take the longest. The detail in each plan will help you figure out why one plan is better/worse than the other. – David Rushton Apr 15 '16 at 16:32
  • @destination-data, Good point. I did this and it comes out as 1% for the variable selects, 49% for the A, and 50% for B. I would call that identical. – Greg Gum Apr 15 '16 at 16:35
  • @SeanLange If you want to post comment as an answer, I will accept. – Greg Gum Apr 15 '16 at 16:41
  • Using ctrl + L is NOT an accurate method for this. That is the keyboard shortcut to show the estimated execution plan. And using the percentage relative to the batch is useless. You need to look at the actual execution plan and evaluate what is actually happening. – Sean Lange Apr 15 '16 at 16:43

2 Answers2

2

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

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
0

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.

Greg Gum
  • 33,478
  • 39
  • 162
  • 233