Creating a simple query with many secondary tables

[Alain T.]

Hi!

As my initial thread ("Very slow query") was unfortunately unsuccessful
(many thanks to Tina that put a lot of time trying to help me), I am
starting this new one.

I have a very simple file (Main) that contains 48 fields, near of 800
records (max of about 2500). 21 of them contain foreign keys to link to
description tables (which contain only the primary key and the description).
The application is working very well and is fast. But I have quite a
problem with one query.

I use it to export data to Excel. In the query, if I only chose the Main
table, it is very fast. But in Excel, I get, for example, the code "123"
instead of "Emergency", which is not useful for people looking at the
stats. To solve that problem, in the query, I display the Main table and
all the secondary tables so that all the data can also be transfered in
Excel. It works very fast if I use 15 secondary tables or less. More than
that, the (lack) of speed grows exponentially (5 sec. for 16 tables, 20 sec.
for 17 tables, 1"20 for 18 tables...), which is not acceptable for me,
neither my customer...

So far, I have done those steps :
- remove lookup fields from the Main table (used TextBox for the Control
Display setting instead)
- put all subdatasheet to none
- create a blank new database and immediately turn OFF the Name Autocorrect
before importing all the data from the old database
- In the relations, all the secondary tables are linked to the Main, with
referential integrity active, and right join (#3)
- on the main table, only one field is indexed
- on all the secondary tables, the key and description fields are indexed

Here is a sample of the query with three secondary tables :

SELECT SCPU.*, TB_Accompagnements.Description,
TB_Centres_Référants.Description, TB_Dangerosités.Description
FROM TB_Dangerosités RIGHT JOIN (TB_Centres_Référants RIGHT JOIN
(TB_Accompagnements RIGHT JOIN SCPU ON TB_Accompagnements.Code =
SCPU.Accompagné) ON TB_Centres_Référants.Code = SCPU.Centre_Référant) ON
TB_Dangerosités.Code = SCPU.Dangerosité;

Any advice would be highly appreciated.

Thanks!

Alain T.

 

[John Vinson]

SELECT SCPU.*, TB_Accompagnements.Description,
TB_Centres_Référants.Description, TB_Dangerosités.Description
FROM TB_Dangerosités RIGHT JOIN (TB_Centres_Référants RIGHT JOIN
(TB_Accompagnements RIGHT JOIN SCPU ON TB_Accompagnements.Code =
SCPU.Accompagné) ON TB_Centres_Référants.Code = SCPU.Centre_Référant) ON
TB_Dangerosités.Code = SCPU.Dangerosité;

Well, any Query joining 22 tables simply WILL be slow.

Do your 21 lookup tables all have values for all the corresponding
codes in the main table? If so, you don't need the RIGHT joins. Having
Right joins will show you EVERY ROW in EVERY LOOKUP TABLE, whether
that lookup value is used or not! See if changing the query to use all
INNER joins meets your needs.

John W. Vinson[MVP]

 

[Alain T.]

Hi John!

1. All those lookup tables have values in the corresponding codes in the
main table.
2. Initially, I used the INNER join - same problem. I did use the RIGHT JOIN
because it says (translated in English) : "Include all the records of the
table SCPU and only those of the table TB_Dangerosités for which the fields
are equals". SCPU is my main table, TB_Dangerosités is the lookup table.

Most lookup tables contain few records (some have about 5 records, others, a
max of 30 records).

I don't understand why having 21 lookup tables link to the Main would be so
slow? As all the primary key are indexed, it should be simple for Access
to quickly find the correct description from each lookup table for the
corresponding fields on the Main table?

Thanks for your support.

Alain T.