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
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.
source to share