Optimization \ Best Practices question 3

[Sheco]

If you have several tables joined together, but only one of them has a limiting condition, is it better to just lay it all out like this:

SELECT
 a.Field1
 ,b.Field2
 ,c.Field3
 ,d.Field4
 ,e.Field5
FROM
  ATable a
    INNER JOIN BTable b WITH (NOLOCK)
       ON a.ID = b.aID
    INNER JOIN CTable c WITH (NOLOCK)
       ON a.ID = c.aID
    INNER JOIN DTable d WITH (NOLOCK)
       ON c.ID = d.cID
    INNER JOIN ETable e WITH (NOLOCK)
       ON d.ID = e.dID
WHERE
  b.Foo = 'Bar'

... or is it better to use a subquery on the table with the condition?

SELECT
 a.Field1
 ,b.Field2
 ,c.Field3
 ,d.Field4
 ,e.Field5
FROM
  ATable a
    INNER JOIN (SELECT
                  sub.aID
                FROM 
                  BTable sub
                WHERE
                 sub.Foo = 'Bar'
               ) as b
       ON a.ID = b.aID
    INNER JOIN CTable c WITH (NOLOCK)
       ON a.ID = c.aID
    INNER JOIN DTable d WITH (NOLOCK)
       ON c.ID = d.cID
    INNER JOIN ETable e WITH (NOLOCK)
       ON d.ID = e.dID

... or does it not matter because the server will know what to do automatically?

 

[gmmastros]

You should look at the execution plan. If the execution plan is the same, then it doesn't matter.

To view the execution plan, open the query in Query Analyzer. Press CTRL-K. Then run the query. The execution plan tab will be displayed near the bottom of the QA window.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

For the record you are also write it like this.

SELECT
 a.Field1
 ,b.Field2
 ,c.Field3
 ,d.Field4
 ,e.Field5
FROM
  ATable a
    INNER JOIN BTable b WITH (NOLOCK)
       ON a.ID = b.aID
       [red]AND b.Foo = 'Bar'[/red]
    INNER JOIN CTable c WITH (NOLOCK)
       ON a.ID = c.aID
    INNER JOIN DTable d WITH (NOLOCK)
       ON c.ID = d.cID
    INNER JOIN ETable e WITH (NOLOCK)
       ON d.ID = e.dID
WHERE

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Sheco]

Yes i was just wondering if one style is generally preferred or should one always write it several different ways and compare execution plans?

 

[SQLDenis]

>>should one always write it several different ways and compare execution plans?\

yes, especially if you have a lot of data (I have 180 million rows in 1 table for example)
Also try using EXIST since it's sometimes faster than JOIN
Try getting the optimizer to use index seeks only


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Sheco]

So just keep plugging away until either the optimizer shows only index seeks, you're satisfied with the performance, or you've reach some arbitrary point where you give up spending any more time on making it faster?

 

[vbkris]

bingo!

but usually the query plan thing will do. however, u really need a control over SQL. i got stumped once.

thread183-1239081

Known is handfull, Unknown is worldfull