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
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