Profiler new featureToo many joins detection

time to read 2 min | 209 words

This is Josh’s feature, since we wrote most of the code for it together. Basically, it recognize a very common performance problem, queries that uses too many joins, such as this one:


Which would result in the following warning:


Queries with too many joins might be a performance problem. Each join requires the database to perform additional work, and the complexity and cost of the query grows rapidly with each additional join. While relational database are optimized for handling joins, it is often more efficient to perform several separate queries instead of a single query with several joins in it.

For OLTP systems, you should consider simplifying your queries or simplifying the data model. While I do not recommend avoiding joins completely, I strong discourage queries with large numbers of joins. Another issue to pay attention to is possible Cartesian products in queries contains joins, it is very easy to create such a thing and not notice it during development.

More posts in "Profiler new feature" series:

  1. (24 Feb 2010) Too many joins detection
  2. (14 Feb 2010) Integrating with application frameworks
  3. (26 Jan 2010) Side by Side diff