I have a client who was experience an very long delay (in excess of 1 minute) when opening a particular form. The form had 2 listboxes on it, which were both Totals queries that essentially pulled info from same 4 tables. These tables are (and relationships, all with enforced RI):
Customers —-> Orders —-> Work Orders <—- Styles
The Work Orders table is the central table, and has 300,000+ records in it. The grouping fields for the queries are all indexed fields. There are 6 or so yes/no fields with WHERE conditions; at least 1 from each of the 4 tables. I played with the query, eliminating fields, etc., trying to find what was the big bottleneck.
When I eliminated the Yes/No field from the Styles table, the query ran in 2 seconds! On a whim, I indexed this field and put the selection back in the query, and it again ran in 2 seconds.
This makes no sense to me! This table has maybe 20 records in it. The yes/no fields in the other tables (with many more records in them) are not indexed. So why would indexing this one field in this little table make such a huge difference in performance? I guess it has something to do with how Access optimizes the query, but I don't understand how or why. And how do I predict this in future?
