SQL Server Performance

Converting Outer to Inner Join

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by rerichards, Dec 5, 2006.

  1. rerichards New Member

    We have a number of complex queries in stored procedures that sometimes run well (under 5 seconds), but other times take several minutes (with an entirely different explain plan). The bad explain plan occurs, even after a dbcc dbreindex on all tables and indexes, and after an update statistics with fullscan on all tables and indexes.

    If we localize variables (suspecting parameter sniffing) this seems to resolve the issue.

    However, I feel that due to the complexity of the statement, that it could be rewritten. I was wondering if a left outer join converted to an inner join, might help. Is there a way of turning an outer join into an inner join, even if it means using an additional temp table?

    We are running SQL 2005, SP1, on Windows 2003.
  2. FrankKalis Moderator

    It depends! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />But without even seeing the statement in question, a definitive answer is like a shot in the dark.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  3. Roji. P. Thomas New Member

    quote:Originally posted by rerichards
    Is there a way of turning an outer join into an inner join, even if it means using an additional temp table?
    No, Not one I can think of, without affecting the output.

    Roji. P. Thomas
  4. vitaly New Member

    Why do you think the execution plan is different? If you are running a stored procedure, should not execution plan be the same (unless you are using WITH RECOMPILE)?

  5. Adriaan New Member

    Well, the choice between an outer or an inner join is never based on performance. Either you need only matching values, so you can use an inner join, or you need to include unmatched values as well, in which case you must use an outer join.

    You probably heard about replacing outer joins with inner joins to improve performance, but you must understand the impact on the results returned by your query.

Share This Page