My first call today had to do with a report that was working, but took 20 minutes to complete.  Other similar reports ran in a few seconds so I was asked to find the difference.  I noticed several added tables and a different table configuration which were likely places to start.  But the one difference that looked most promising was the number of join lines between the tables.  In the reports that ran quickly, every pair of tables had one additional join line between them.  And in all cases the field being joined was called SystemID.

Apparently, this software package allows you to maintain the data for several completely independent ‘systems’ in the same database.  This SystemID identifies the system of each record. The customer explained that since they only use one ‘system’ they didn’t think the link on SystemID was essential. And the report did seem to generate accurate data without the SystemID link, if you didn’t mind the wait.

I explained that the indexes that facilitate the joins between tables were all probably created with the SystemID as the primary key. So any join without a SystemId value couldn’t tap into the index.  Without the help of an index, the database would have to do a ‘serial read’ (record by record) to find the matching records.  Think of finding a topic in a book without an index.

So we added the extra line for SystemID between every pair of tables and tested the report.  The report ran in about 8 seconds. The lesson here is that even links that seem redundant can make a significant difference in the performance.