How to avoid too many joins?

Question

I would like your help to discuss how would I avoid too many joins using a generic approach. Is there a general rule for this?

Currently, I have a very complex query that is joining 11 tables and the performance is very poor (even with indexes and updated statistics). Using the Entity Framework Profiler, I've received the suggestion to reduce the number of joins and, instead, perform several separate queries: link.

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.

How should I modify the following example to achieve a better performance?

select  *
from   Blogs blog0_
       inner join Posts posts1_
         on blog0_.Id = posts1_.BlogId
       inner join Comments comments2_
         on posts1_.Id = comments2_.PostId
       inner join Users user3_
         on posts1_.UserId = user3_.Id
       inner join UsersBlogs users4_
         on blog0_.Id = users4_.BlogId
       inner join Users user5_
         on users4_.UserId = user5_.Id

Answer 1

There are few ways to minimize(optimize) number of table joins:-

• Ensure what you want and which tables required.
• Also make sure tables are in normalized form.

Encapsulate few joins by using:-

• Using CTE
• Using Temp Tables
• Views

You can find the details about CTE and Temp tables from links Common Table Expression, View. For temporary table just add "#" before table name and insert desired data from joins and later use it but in same session.