So say you have four tables A, B, C and D. A joins to B, B joins to C and C joins to D. If all the tables are required in the results (i.e. you are using inner joins) you can theoretically use 4 different link configurations that should give you the exact same output. If you start with A or D the joins would be in a straight line (ABCD or DCBA).  If you start with B or C you would get a fork, like B to A and B to C with C linking to D. But even though the results will be the same, the performance could be dramatically different. So how do you decide which pattern is most efficient?

There isn’t a simple answer that works in every case, so testing is important. However, there are two places I look that often help: the indexed fields and the WHERE clause fields. You can often see the indexed fields in the linking window (colored tabs) or you can ask someone who knows the database what the indexes are on each table. To see the WHERE clause fields go to the database menu in Crystal and select “Show SQL Query”. The fields mentioned in the WHERE clause should match your record selection formula.  If they don’t you may need to tweak the formula so that the criteria can translate into SQL.

Indexed fields:
When linking you want your join to go TO indexed fields and ideally to ALL the fields in that index. So say Tables A and B are linked on two fields from each table. And say that these four fields all have red index tabs. But table B has a third field with a red tab and that field isn’t part of the join. That would mean you should link from B to A.  This uses the complete index in A which is the more efficient than linking to the partial index in B.

And don’t assume that because B is sitting on the left that the join starts at B and goes to A. I always hit the “auto-arrange” button in the links window to confirm the direction of the joins. After hitting “auto-arrange” all the joins flow from left to right. If a join is backwards, you can right-click that join and select “reverse join”, then click “auto-arrange” again to confirm the new direction.

Here are some other posts where I discuss the affects of linking on indexes:
https://kenhamady.com/cru/archives/2923
https://kenhamady.com/cru/archives/2653

WHERE clause fields:
Now lets also say that most of the WHERE clause criteria applies to the C table. I try to take the table with the most restrictive criteria and put it all the way to the left (or as far left as possible). That way they query starts out with the smallest data set possible and each subsequent join has fewer matches to find.

If the primary field in the WHERE clause is found in more than one table you get some flexibility. You can select the table that works best for indexing and then use the field from that table in the criteria.

So based on the above scenario I would recommend starting with table C. Then forking from C to both B and D, with a final link from B to A.

In some rare cases the indexed fields  and the WHERE clause fields can’t both be optimized at the same time because they point in opposite directions. When that happens you have to test different join patterns to see which works best.

One last note. In most reports the order of the joins is obvious from the link pattern.  But if you look at the SQL and the links aren’t in the order you want, you might have to use the “order links” feature of the database expert.