1

Recently I came across a standard in my project which strangely asserts that we should refrain from joining more than five tables in SQL query. More than five tables in a query leads to performance degradation.

Even though I strongly disagree with such an assertion (regardless it's SQL Server or Oracle) I was wondering if there are any suggestions as to how many tables we should use in join, in SQL Server or Oracle. Does it depend on the database type (SQL Server Vs Oracle Vs MySQL) or number of rows in the tables, indexing, INNER Vs OUTER Join, cardinality of the tables joined, etc.?

My understanding is since SQL is not a procedural language we should try to add more tables in JOIN provided that we are using the right columns for joining.

0

2 Answers 2

2

More than five tables in a query leads to performance degradation.

A join will increases the cost of running a query to some degree no matter what. But what's the right thing to do ultimately depends on what's ideal or acceptable for any given project.

For example. I have a 1-column table that contains 1 million row, whose only column is an ordered bigint clustered index. Make 10 queries ranging from no join to 10 joins, and compare execution plan and execution time. You'll see the increased cost and time pretty much in a linear manner. In my personal environment, SELECT * with no join might take 5 seconds to complete while 6 joins might take 6 seconds. What if the table had only 10,000 records? It will run significantly faster even if the number of joins is doubled (in my environment <200ms).

Now, if the only way to get the right data set is by joining 10 tables and it takes 5 seconds, is that good or bad? If the speed is critical to your application, like high-speed stock trading, then you might want to reconsider. What if it was a report for sales department?

Does it depends on the database type (SQL Server Vs Oracle Vs MySQL) or number of rows in the tables, indexing, INNER Vs OUTER Join, cardinality of the tables joined, etc.?

Database product, yes and no. Each products have different optimization logic. So one might not execute a query the same as others. One might be faster than others in some cases and vice versa. Everything else you mentioned affects performance, including selectivity. OUTER can be slower than INNER. There are occasions where OUTER joins can be faster or costs less.

Of course, my very simple example is far from any real-life scenario but the underlying logic is the same. How many you should join depends on your environment and your requirements. Things you mentioned will affect the performance, and if the query is running below acceptable level or started having a performance issue, you should examine them. But none of them are relevant to how many joins you SHOULD have.

-2

I only know SQL Server

With 5 or more joins, I have observed that the query optimizer will tend to go with loop joins more often. And a loop join can perform poorly.

But you just need to deal with it. You can get good performance with 5 or more joins - sometimes it takes a bit more work.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.