CODEWITHSUNDEEP
sqlsql-servert-sql
Craig
Craig

Reputation: 18724

Difference between straight query, or sub query?

I came across a stored procedure, which had a few faults. One being, the developer did this:

SELECT alias.firstname, alias.surname, alias.id
FROM
    (SELECT firstname, surname, id, address, anotherfield, etc, etc
     FROM TableName
     WHERE afield = avalue) alias

So, this is clearly the same as:

SELECT firstname, surname, id
     FROM TableName
     WHERE afield = avalue

This was repeated a lot. So, my question is, is there a performance loss by doing the sub query? I know it's useless... and the best idea is to make it right - which I did. But, is there any performance loss in leaving it?

I'm guessing the query optimizer would make sense of it, and do the right thing?

Upvotes: 1

Views: 85

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280479

In a simple case like this, SQL Server will collapse these queries to the same execution plan.

I tried this on AdventureWorks2012:

SELECT CarrierTrackingNumber, ProductID, UnitPrice, LineTotal
FROM 
(
  SELECT *
    FROM Sales.SalesOrderDetail
    WHERE ProductID = 781
) AS Alias;

SELECT CarrierTrackingNumber, ProductID, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
     WHERE ProductID = 781;

The plans are identical and differences in runtime metrics are indistinguishable.

enter image description here

I also tried the following, purposely selecting a table with a complex type (Geography):

 SELECT AddressLine1, City, StateProvinceID 
 FROM Person.Address
   WHERE StateProvinceID = 9;

 SELECT AddressLine1, City, StateProvinceID 
 FROM 
 (
   SELECT * FROM Person.Address
   WHERE StateProvinceID = 9
 ) AS x;

 SELECT AddressLine1, City, StateProvinceID 
 FROM 
 (
   SELECT * FROM Person.Address
 ) AS x
 WHERE StateProvinceID = 9;

Same thing, in each case the optimizer collapses to an index scan and ignores the other columns that appear to be referenced:

enter image description here

Whether you can rely on this same optimization to occur on more complex queries, I'm not sure. The optimizer isn't always perfect or predictable... so I can certainly envision more involved queries where this collapse won't happen reliably.

I'm not sure I understand the value of the pattern you're seeing. Perhaps there are examples there that actually serve some purpose.

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726919

In cases when only the projection is involved in the outer query, there should be absolutely no difference in execution plans between the two queries. More complex queries with "where" clauses on both the inner and the outer query, may test the limits of the query optimizer, and might produce inferior query plans for two-level queries, but in your case the plans and the execution speeds should be identical.

Upvotes: 2

Kyle Hale
Kyle Hale

Reputation: 8120

The execution plan for the two queries would be identical in terms of performance. The optimizer would just throw out the unused columns.

Upvotes: 1

Related Questions