Is seven inner joins in a query too much?

Question

I have a query that has 7 inner joins (because a lot of the information is distributed in other tables), a few coworkers have been surprised. I was wondering if they should be surprised or is having 7 inner joins normal?

Answer 1

It's normal if your schema is in the 5th normal form : )

Answer 2

it's not unheard of, but I would place it into a view for ease of use, and maintenance

Answer 3

Two questions:

1. Does it work?
2. Can you explain it?

If so, then seven is fine. If you can't explain the query, then seven is too many.

Answer 4

Depending on what you are trying to accomplish, a large number of joins in a query is not remarkable.

Personally, I would be less concerned with the number of joins employed to return a desired result set and more concerned with whether the query is optimized and running within acceptable parameters.

If the query is fully optimized and cannot be trimmed down but the query itself does not execute quickly enough then it is possible that the data structure design is not the right fit with what you're trying to do. At which point you can re-evaluate what you're trying to accomplish or the structure of the data that is feeding your business model.

Answer 5

It's probably not normal but it's certainly not excessive. If you find yourself joining the same tables over and over, create some views.

Answer 6

It's not at all unusual. With a system like Siebel it's common to see join counts in double figures.

Answer 7

Seven joins makes it tougher for readability, but more important are performance and scalability. If those are OK, go for it.

Answer 8

number of joins depends on your data model, 7 joins can be in your query if that is what you query for - I recall having similar queries in an app I worked on long time ago, the performance depends on many factors (table size, indexes, server load, server config to name few) and I do not think it can be generalized that 7 joins are bad.

if it works for you then I guess its fine :D

Answer 9

I think what you want to avoid is a join depth greater than 7. 7 inner joins of less than 7 joins in depth certainly isn't unheard of, but sometimes people hear "7 joins" and think the no-no is 7 joins, not depth.

Answer 10

7 is fine if your database design requires it. However, if 7 is neccessary to achieve your goal, I'd reexamine the database design to make sure this level of obscurity is really neccessary.

Out of curiosity, is this DB2? Just a pattern I've noticed :)

Answer 11

is this 7 inner joins on the same table, 7 inner joins on different tables, or 7 nested inner joins?

...trick question! It really doesn't matter, if that is what your database structure requires, then it is correct

caveat: if it is 7 nested inner joins on the same table, you probably have a poorly-structured table ;-)

Answer 12

Yes, it's normal - but, really, it's not such a great idea from a performance perspective. Since query plans are built on estimated costs, there is an increase in the number of errors as you increase joins (or any other operator, for that matter):

The SQL Server Query Optimizer will estimate a minimum of one row coming out of a seek operator. This is done to avoid the case when a very expensive subtree is picked due to an cardinality underestimation. If the subtree is estimated to return zero rows, many plans cost about the same and there can be errors in plan selection as a result. So, you’ll notice that the estimation is “high” for this case, and some errors could result. You also might notice that we estimate 20 executions of this branch instead of the actual 10. However, given the number of joins that have been evaluated before this operator, being off by a factor of 2 (10 rows) isn’t considered to be too bad. (Errors can increase exponentially with the number of joins).

Also, the optimizer attempts to balance the time required to come up with a plan versus the potential savings - it won't spend all day trying to find the most optimal plan. The more joins, the greater the number of alternative plans exist - some of which may be more optimal than the optimizer has time to find.

Answer 13

It's certainly not unusual. However at least in Oracle, 7 is a special number, as any more than that and the optimizer can no longer test every join order (due to factorial growth in the number of possibilities). So it would be wise to avoid going over 7 unless you're prepared to babysit your execution plan.

Answer 14

7 or even more is not at all unusual in data warehouses where a fact table could easily have foreign keys to a dozen dimensions. In the data warehouse scenario, the cardinality of the dimensions is usually low compared to the fact table, so filters on the dimensions help the fact table be utilized through an index seek or scan.

For a normalized transactional schema, it is not usually a problem if the cardinality of the results set is low in the primary base table (i.e. select everything about one customer), because the foreign keys can normally simply result in index seeks or index scans.

Answer 15

Seven Joins in a query is not many. I've seen 26 joins in a query, which with the 'right' indexes was efficient. It's not so much the number of joins but what execution plan is generated. I suggest you look at the number of logical reads and the actual execution plan.