July 27, 2011 at 10:29 pm
I have the following performance question on joins in sql server 2008 r2.
Is there a performance problem when I join 5 tables using inner joins and 4 tables are joined using left outer joins?
Is there a performance problem with this type of join? If so, then should I join all the tables using left outer joins? I can not use all inner joins since all rows I want selected would not be picked.
(Note: This issue has occurred since my company is changing their production database. Basically there was one table
that contained about eveything we needed. Now the new database is breaking up the one major table into nine different
tables. The production application are the same, but they need to work with the new database.)
Thus basically in several stored procedures I need to join all nine of the tables. For a couple tables I need to do
left outer joins, so at least data from from the 5 major tables will appear.
July 27, 2011 at 10:48 pm
It sounds like the result of this query will be used quite heavily since all the legacy code will depend on the joined output.
It's quite common to index your foreign keys (ie, your join columns). For this particular query I would use the include option when creating the index to have the other columns used from each table as part of that index.
With this in place your query should perform really well.
July 27, 2011 at 10:50 pm
There's no inherent problem, no. Given a sensible design, appropriate indexing, and a sensible query, SQL Server will generally have no problem performing such a join.
Most likely, something about the design, statistics, indexing or query is producing an unfortunate query plan in your case. If you would like to provide details (e.g. table definitions and an example query plan that does not perform well), I will be happy to give a more detailed answer.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply