How many joins are too many

However, keep in mind that the large amount of joins may only be a symptom , not the root cause of the issue. The standard practice for query optimisation should be followed look at profiler, query plan, database structure, logic etc. It's really depends on how big your tables are, even you only joining 2 tables together if it has M records, then that's gonna be a slow process anyway.

However, if you feel really need to join a lot of tables to achieve the goal , I am suggesting your databases are over normalized, 3rd normalisation is working really well in most of scenario, don't try to spit the information too much , as it recognised to be inefficient for querying. Yes, if necessary please create a table to cache the results from the heavy query, and updates the fields only when is necessary, or even only once a day.

I also see mammoth queries joining tables, but from what I've seen the query optimiser always seems to find the most efficient plan - certainly all the performance issues I see in these sorts of complex issues are usually related to some other problem such as conditional WHERE statements or nested sub queries. The optimizer sets a time limit on itself to prevent it from running too long. The problem with many tables is that each one multiplies the number of possible plans for the optimizer to evaluate actually it's the number of Joins, not tables per se.

At some point the optimizer runs out of time and will just use the best plan that it has so far, which can be pretty bad. So where is this point? There are other variables involved that have a more significant impact on the overall query plan and performance, in my experience, such as:. You might have only two tables being joined together in a query, but if one key column is a GUID and the other is a varchar representation of a GUID, you have no indexes anywhere, and the tables are 2 million rows each, then you'll probably get very poor performance.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 12 years, 5 months ago. Active 12 years, 5 months ago. Viewed 19k times. I have a query that includes a total of 11 joins all of which are joining on int. Granted there are only 10 users at any one given time the system screams and we do not see any performance issues.

Like so many other issues - I would say the answer is "it depends". The biggest challenge is to figure out a viable test. For a specific selection, one method might be faster than another, but the impact to the system could be more debilitating. I've never had problems with joining many tables. I have also seen performance flip-flop when volumes and database size gets large. I have seen some temp tables cause big performance problems and other improve the performance.

This is where you get to prove you are worth your position. You must be logged in to reply to this topic. Login to reply. Post reply. March 25, at pm I'm having an argument with a co-worker actually my boss about how many JOINs is too many. What do you all think? I'd vote for more joins, less tables.

Interesting topic and something that is worthy of a few tests. Look for something soon. One query I found that was 'stuck' had 53 joins. In saying that, the database overall data size was tiny, with only pieces of content in Drupal this is very low, some Drupal sites can have millions of items of content.

Upsizing the vCPU count in the database cluster had no impact, as the 'stuck' queries just consume all the CPU available. We even had trouble trying to connect to the database cluster via the MySQL client, due to timeouts.

After seeing the seeing queries stuck in Statistics phase, we did some digging to see what other content had to say on this topic:.