Joins

Avoid the use of unnecessary Joins as they impact the performance of a query. One of the obvious advantages of stored procedures is that you can break down your query into modular components and use variables instead of Joins.

Do not exceed a maximum of 15 simultaneous joins as the query performance is significantly impacted.

When writing an inner join, try to make the exclusions as one-sided as possible. This vastly improves the performance of your query. Exclusions on both sides of an inner join can prove to be expensive.

Also, never substitute a variable for a joinable field. In the following example the first join uses the variable @PUId instead of joining to the field in the Events table. This causes the query to run much slower because it takes longer for the query engine to merge the rows together.

For example,

FROM Events e
     JOIN Variables v ON v.PU_Id = @PUId
                         AND v.Var_Desc = ‘MyVariable’

versus

FROM Events e
     JOIN Variables v ON v.PU_Id = e.PU_Id
                         AND v.Var_Desc = ‘MyVariable’

When writing a query with Joins, put the table with the smallest number of rows last in the list and the table with the largest number of rows first.