Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand how JOINS are handled, and was wondering about the following:

Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty important query. What would be faster in the long run. The 'normal':

SELECT 
    TOP x
    A.a
    , A.xxx
    , B.a
    , B.xxx
FROM A 
LEFT JOIN B ON (A.xxx = B.xxx)
ORDER BY A.a

Or specifying the columns of the joined table beforehand?

SELECT 
    TOP x
    A.a
    , A.xxx
    , B.a
    , B.xxx
FROM A 
LEFT JOIN 
(
    SELECT 
        B.A AS A
        , B.xxx AS xxx
    FROM B
 ) AS B ON (A.xxx = B.xxx)
ORDER BY A.a

I guess this boils down to having a good understanding of how SQL Server handles optimization. In my preliminary tests I haven't found any difference, but these tests aren't quite as complicated as 'The Real Thing'. Will the latter just be optimized to the former by the interpreter?

Since I have never seen the latter side being used (but admittedly, I'm really new to this), my gut feeling tells me the first one will be preferred, but a gut feeling won't (or shouldn't) hold up as real evidence.

Bare in mind the real query really has alot of tables involved with probably only 1 or 2 relevant columns per table, so I was thnking perhaps I can doe some sort of preselection to avid including the whole table in the query.

So, any ideas?

share|improve this question
1  
Look at the query plan for both queries. Especially look at the "Output List" of any Scan or Seek operators. They will show you which columns that will be part of the next steps in the processing of the query. It is likely that it will use the same plan for both queries in this case and in most similar cases. –  Peter Henell Feb 19 at 14:04
    
What do you think TOP even means, without ORDER BY? –  Aaron Bertrand Feb 19 at 14:24
    
Forgot to add it to the example (might have as well removed the TOP altogether). Updated. –  Apeiron Feb 19 at 15:32
add comment

1 Answer 1

For most simple cases, like your, there will not be any difference. SQL Server is lazy and will figure out the most easiest way of completing your query and it will do a very good job at it (most of the time).

The best way of analyzing the difference yourself is to look at the query plan of both queries.

Given this example of two queries similar to yours:

SELECT 
    c.CustomerId,
    o.OrderId,
    o.ArticleId,
    o.Customerid
FROM 
    dbo.Customer c
INNER JOIN 
    dbo.orders o
    ON o.CustomerId = c.CustomerId


SELECT 
    c.CustomerId,
    o.OrderId,
    o.ArticleId,
    o.Customerid
FROM 
    dbo.Customer c
INNER JOIN 
(
    SELECT 
        o.OrderId,
        o.ArticleId,
        o.Customerid
    from    dbo.orders o
) AS o
    ON o.CustomerId = c.CustomerId

The query plans for both will look exactly the same. Query Plan of both queries

If you hover your mouse over the Index Scan operator you will see the Output List at the bottom of the popup. The Output List will show exactly which columns that will be "extracted" from the table. Both queries produce the same Output Lists which means that there is no benefit of "pre-selecting" the columns because SQL Server is already doing this for you.

This is true in your example since you are specifically specifying columns from both tables in your select clause, if you would do SELECT * then there could be a difference.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.