SQL Server Performance Forum – Threads Archive
Converting Outer to Inner Join
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.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>
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. ThomasIs there a way of turning an outer join into an inner join, even if it means using an additional temp table?
http://toponewithties.blogspot.com
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)? -vitaly
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.
]]>