performance on t-sql joins | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
performance on t-sql joinsI 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.
The business question you are about to answer should dictate the SQL statement you are using. If you need a combination of INNER and OUTER JOINs, so be it. We do mixed JOINs between tables with only a handful of rows and billion row tables with good performance on commodity hardware Try to get the statement right in the first place, and then go from there and check if you are missing some indexes that will speed up the query.
The first and best thing is to checkout the Execution plan for that query which is causing the problem. As you say it is SQL2K8R2 there is a good feature we got in query analzyer.
Few questions to ask:
Is the data changed frequently on those lookup tables? – if not you can go with an INDEXED VIEW. Edit:**
How many foreign keys are NULLABLE in this case? – try to avoid the nullable FK columns as it will be more expensive to process the inner outer joins that are involved in the query. The joins within the query both outer and inner cost will be more expensive to process. ** Also better to index on most of the FK columns. Are the schema names are same for these tables? Using Execution plans evaluate the HASH JOINS cost.
How many foreign keys are NULLABLE in this case? – try to avoid the nullable FK columns as it will be more expensive to process the inner joins. Also better to index on most of the FK columns.
Thanks for the point Frank, corrected the typo
Wendy; Never mind to use any type of logical joins either Inner join or Left outer join or right outer join or full outer join since all of them are translated to one of 3 physical joins :
- Hash join for huge data entitiy
- Merge join for medium ones
- Nested loop for small ones.
So the most important hints here are :
- To use sufficient indexes paricuray more covered compound indexes for Hash join
- This by placing all join columns within the Key column parts and all other select columns within the incude column part
- Then enure not use any of the query hints below particuarly:
- Option ( Hash join)
- Option (Merge join)
- Option ( force order)
And if you want to not get into plans so much, sometimes you can take a inner join and an outer and shove into a working table (temp or table variable) then do more outer joins later in the script. one query with a lot of outers is rarely a good idea.