TUNING TIPS FOR JOINS

· Return only needed rows from the outer table in the join.

· Include appropriate where clause to minimize the number of rows that needed to be joined.

· Never use * (asterisk) in joins.

· Performance between two joined tables can be optimized if each of the joined columns has their own index. Otherwise query optimizer will use hash join, which will definitely degrade the performance. If indexes are created, optimizer will go for nested loop join instead of hash join. Generally a clustered key is better than a non-clustered one.

· If two tables are joined frequently then columns used for joins should have appropriate index.

· Try to get as much as stuffed data into a data page. This can be done through high fill factor, rebuilding indexes (get rid of empty space) and optimizing design of data columns and width.

· Create index on foreign key of a table because creating a foreign key doesn’t create an index automatically. This will boost up the performance especially in the case of merge joins.

· Joins should be done on columns that have unique indexes. If columns used for joins are not mostly unique then optimizer may not use an existing index to speed up the join.

· Indexes on the join columns should be numeric data type for best join performance. If the data type is char, varchar or any other nonnumeric data type, there will more overhead.

· When joining two columns of two tables, for maximum performance indexes on the columns to be joined should have same data type and same width ideally. One case is mixing up of non-Unicode and Unicode data types.

· Regularly joining four or more tables to get the record set denormalize the tables so that the number of tables is reduced for join condition. An alternative to denormalizing a table is to use an indexed view (Only in 2000 enterprise edition) to pre-join the tables.

· If your join seems to be slow, consider removing hints in the join condition. This is especially when you have upgraded from 6.5 to 7.0 from 7.0 to 2000.

· For very larger joins consider placing the tables to be joined in separate physical files in the same file group.

· Avoid using cross-joins unless it is required for the functionality of the application and consider looking at left outer joins, use if it is required. Unnecessary usage of cross-join and left outer join will lead to overhead and poor performance.

· Comparing join and a sub query to perform a same task, generally join will be faster. But if the returned data is small and there are no indexes on the join columns then sub query will be faster than join.

· Use joins instead of sub selects, if the sub select contains an aggregate function.