6

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
Ricardo
  • 448
  • 4
  • 7
  • 19
  • 4
    Your query is probably fine, assuming that you have appropriate indexes. The link is highly misleading. – Gordon Linoff Sep 24 '14 at 03:00
  • More suited for our brotherly site. Codereview – Daryl Gill Sep 24 '14 at 03:00
  • 4
    Joins are actually one of the cheaper things you can do on a sql server. It's the quantity of result records that can lead to problems. – Lynn Crumbling Sep 24 '14 at 03:00
  • 5
    Select * is probably not a good idea. You should only select the columns you need. This can affect performance too, though it is not usually a big factor, but it can be sometimes. – Gary Walker Sep 24 '14 at 03:07
  • 4
    Eleven joins is a rather straightforward circumstance - get used to it. Now when you start approaching 150 or 200 tables in your joins, serious thoughts on redesign might be in order; until then, you are gaining so much efficiency from reduced data volume that you should simply enjoy it. – Pieter Geerkens Sep 24 '14 at 03:11
  • 1
    +1 on the 'SELECT *' suggestion. also, as the profiler suggests, I'd try breaking the query down so that you eliminate records using subqueries - starting with the largest result sets, and working your way down so that the last join at the top of the pile is dealing with the minimum number of records possible. you may also be able to get a performance advantage by using different joins depending on the purpose of each subquery: http://www.w3schools.com/sql/sql_join.asp – Tim Sep 24 '14 at 03:43
  • First try it and see if you've got a problem. Sometimes materialising a partial join of some of the tables into a temp table can help if estimates are wrong from the output of one join affecting later join choices. For a query with 12 joins the number of possible join orders is 28,158,588,057,600. Additionally each join may be of three possible algorithms (hash, nested loops, merge). – Martin Smith Sep 24 '14 at 06:26

1 Answers1

5

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.

Mr. K
  • 380
  • 3
  • 15