SQL Server Performance

Should left outer joins multiple tables or not?

Discussion in 'T-SQL Performance Tuning for Developers' started by pcsql, Jun 1, 2004.

  1. pcsql New Member

    I'm interested to know whether there is any general performance tips about using left outer join to join multiple tables in a select statement besides having indexes for the joining columns.


    Thank you for any help.
  2. derrickleggett New Member

    1. Avoid them if you can use an INNER JOIN. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />2. This isn't supposed to matter, but have your most limiting at the top of the FROM. It does matter.<br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  3. pcsql New Member

    Hi derrickleggett,

    Can you elaborate on your suggestion #2?


    Thank you.
  4. derrickleggett New Member

    When you use the left outer join, you will have basically a cascading list of joins.

    A lot of times, people use the left outer joins, then somewhere down the list say where right side IS NOT NULL. This will in effect limit the entire recordset on that table. Instead of joining that way, list that table closer to the top or as the top table. Use a combination of INNER JOIN; or have the table as your source table. This will cause the "filtering" to occur sooner in the process and the rest of the process will deal with a smaller recordset. Does that make sense?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. satya Moderator

    http://www.sql-server-performance.com/tuning_joins.asp for information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. FrankKalis Moderator

    quote:Originally posted by pcsql

    I'm interested to know whether there is any general performance tips about using left outer join to join multiple tables in a select statement besides having indexes for the joining columns.


    Thank you for any help.

    It might be a good investment for you to spent some $60 for Inside SQL Server by Kalen Delaney. Covers locking, joining strategies and a lot more.


    --Frank
    http://www.insidesql.de
  7. pcsql New Member

    derrickleggett,

    For my case, I have tried without any where condition so all the tables are joined by its respective keys.

    Thank you for the clarification.

    -------------------
    Satya,

    Thank you for the link.

    -------------------
    Frank,

    I have that book but not finish reading it (actually only touch little bit of it).









  8. derrickleggett New Member

    For this type of thing, I actually like Ken Henderson's books better. Look up The Guru's Guide to Transact-SQL. Kalen is really good on the architecture and DBA work. I like the Guru's books better for proc, joins, etc.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. FrankKalis Moderator

    I thought the question was about JOINs, strategies and general performance, not general T-SQL so I mentioned Inside SQL Server as it surely helps choosing the right joining strategy when you know what's going on behind the scenes. Although I also have the Henderson book, meanwhile I'm uneasy about it. I like the Ben-Gan, Moreau book more. But I also guess it depends on personal preferences.


    --Frank
    http://www.insidesql.de
  10. satya Moderator

    Whynot have both the books for reference. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>

Share This Page