Sources of join query performance problems

 

The optimization algorithms described above benefit most join queries, but the performance of a few queries may be degraded.

This occurs when:

• An index is not available which provides average number of duplicate values statistics for the potential join columns.

• The query optimizer uses default filter factors to estimate the number of rows being selected when applying local selection to the table because indexes or column statistics do not exist over the selection columns.

  Creating indexes over the selection columns allow—s the query optimizer to make a more accurate filtering estimate by using key range estimates.

• The particular values selected for the join columns yield a significantly greater number of matching rows than the average number of duplicate values for all values of the join columns in the table (for example, the data is not uniformly distributed).

 

Parent topic:

Join optimization