Bad Standards: #3--Table Limits for Online Joins

In this case, what I usually see is a standard that states something like this: "Code no more than a 3 table join in any program that is accessed in an online environment."

The idea is to limit the amount of work that is done by a transaction so that it can be accomplished in a limited amount of time. Given that there is a user waiting for a response you do not want the transaction to take an inordinate amount of time. Why is this wrong?

Well, the idea is not wrong, the standard is. Whether it says a 3 table join or a 5 table join (or some other number) is not important. The number of tables participating in a join is not the defining factor. Instead, it is the number of qualifying rows to be returned. A well defined query that specifically limits the data to be returned appropriately, can be very efficient whether it is a single table access or a multiple-table join. So do not place artificial limits on the number of tables that can participate in a join. Each DBMS has its upper limit and, of course, you cannot exceed that (for example, 225 table in DB2 for z/OS).

Another misunderstanding is when standards are written that "worry" about the size of tables. Once again, this factor is not important - instead it is the number of qualifying rows. Consider, for example, a table with 10 million rows in it. A query that uses a matching index to return 2 rows from that table can be very efficient. The number of rows that qualify for the predicates is more important than the size of the table.

You should create standards and guidelines that help your application developers and SQL users to create efficient queries instead of creating arbitrary standards based on table size or number of tables in a join.

© 2005, Mullins Consulting, Inc.