How do you avoid too many joins?

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

I currently have a very complex query that joins 11 tables and the performance is very poor (even with indexes and updated statistics). Using the Profiler Framework Entity Framework, I got a suggestion to reduce the number of connections and instead run multiple separate requests: link .

Each connection requires the database to do additional work, and the complexity and cost of the query grows rapidly with each additional join. Although a relational database is optimized to handle joins, it is often more efficient to run multiple separate queries instead of a single query with multiple joins in it.

How can I modify the following example to achieve 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



source to share

1 answer

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

  • Make sure what you want and what tables are needed.
  • Also make sure the tables are in normalized form.

Encapsulate multiple joins using: -

  • Using CTE
  • Using Temp Tables
  • representation

You can find information about CTEs and Temp tables from the General Table Expression , View links . For a temporary table, just add "#" in front of the table name and insert the desired data from the joins, then use it, but in the same session.



All Articles