Very slow query

[Alain T.]

Hi!

I built a Main table, normalized, containing about 800 records. It will
grow to about 1600-2000 records. 21 secondary tables (code, description)
are linked to the Main table. All the keys are indexed. On the Main table,
all secondary tables are link in the way showed in this sample :
SELECT TB_Groupes_Age.Code, TB_Groupes_Age.Description FROM TB_Groupes_Age
ORDER BY TB_Groupes_Age.Description;
When I display the content of the Main table, all the fields that contain
the foreign key display correctly the description instead of the code.

In the query, if I only chose the Main table, it is very fast. When I run
that query in Access, the fields that contains the foreign keys display
correctly the information from the secondary tables (for example, instead of
displaying the code 123, It would display "Emergency"). But if I get the
data in Excel, I get 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 are also transfered in Excel. It works very fast if I use 15 secondary
tables or less. More than that, big problem (after 90 minutes, it is still
not finished...)!

Below, you see a sample of the query with the main table and three secondary
ones (the construction is the same, but just bigger, with all the secondary
tables added) :

SELECT SCPU.Dte_Visite, SCPU.Période, TB_Accompagnements.Description,
TB_Centres_Référants.Description, TB_Dangerosités.Description
FROM TB_Dangerosités INNER JOIN (TB_Centres_Référants INNER JOIN
(TB_Accompagnements INNER 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é;

Is there a way to speed up the query?

Thanks a lot for any information provided.

Alain T.

 

[tina]

sounds like you're using Lookup fields in your Main table. if so, recommend
you get rid of them - see http://home.att.net/~california.db/tips.html#aTip8
for more information. as you have already found out, using the Lookup fields
obscures what data is actually being stored in the table - leaving you to
find out the hard way, when you export the data - and does nothing to
simplify writing a query to show the "display" data from secondary tables
rather than the stored data in the main table.

per Access 2003 Help, the number of enforced relationships allowed in a
query are "32 per table minus the number of indexes that are on the table
for fields or combinations of fields that are not involved in
relationships". unless you've indexed a number of non-linked fields, you're
well under that limit (though the limit may change for different versions of
Access; check Help in your version by searching on "specifications").

hth

 

[Alain T.]

Hi Tina!

Thanks for the information provided! And I thought I used the best
methodology to be efficient...

As I am not at all an Access expert, can you guide me a bit please, ie what
do the experts do instead?

Thank you!

Alain T.

 

[tina]

As I am not at all an Access expert

well, i'm not an Access "expert" either, Alain, so we have something in
common!

can you guide me a bit please, ie what
do the experts do instead?

experienced Access developers draw a sharp line between database structure
and user interface. tables fall cleanly into the structure side, and the
focus is on data normalization and data integrity. queries, forms, and
reports fall into the user interface category; the focus here is on making
the data available to the user for display and interaction, in a format that
is intuitive, easy to use, and supports the user's work process.
*presenting* the "text values" from secondary tables to the user, is a user
interface issue, and so it is handled at the query/form/report level - not
at the table level. recommend you use the following guidelines:

1) make sure your tables' relationships are correctly set up and referential
integrity enforced, at the table level, using the Relationships window.
2) make sure that table fields that should be indexed, *are*, and fields
that don't need an index, *are not*.
3) in a query, add the "secondary" tables that are needed so that you can
show the "text value" fields associated with the key values stored in the
primary data table(s).
4) in reports, base the report on the type of query described above
(ususally).
5) in forms, base the form on the primary data table, and use combobox or
listbox controls with RowSource property set to the appropriate secondary
table, so that the user has access to the "text values" stored in the
secondary table.

if you're not familiar with combo box controls, suggest you read up on them,
and their unique properties, in Access Help so that you have a thorough
understanding of how they work and how to use them.

hth

 

[Alain T.]

Hi Tina!

Thanks for the following informations. I will study them in the next few
days.

Meanwhile, as my database is in production for some months now, how will I
replace correctly my existing key fields?

I thought about those steps :
1. create a new fied in the Main table, like cSex
2. replace cSex with text value from secondary table based on the value in
the Main table called Sex (how will I do that?)
3. delete the field Sex in the Main table
4. rename the field cSex to Sex in the Main table
5. Repeat all those steps for my others 20 secondary tables...

After all those operations, modify all my secondary tables to remove all the
keys and keep only one field ("text value") and link the fields from those
tables to my fieds on the Main table (step 3 below).

What do you think of that?

Alain T.

 

[tina]

i think you're making this a lot harder than it is, or else i'm missing
something about your tables design. to get rid of a Lookup field in a table,
open the table in Design view, click on the field you want to change, then
click on the Lookup tab at the bottom and change the DisplayControl setting
to Text Box. AFAIK, it's that simple - though i never create Lookup fields
in the first place, so it's possible there's an issue i'm unaware of.

if you're using Access2000 or newer, make sure that the Name Autocorrect
option is turned OFF. if it's currently turned on in your database, suggest
you do the following:
1. create a new blank database and immediately turn OFF the Name Autocorrect
option.
2. compact the database.
3. import all objects from your current database into the new one you just
created.
4. compact the database.
5. remove all the Lookup fields in your tables, as described in the
paragraph above.
6. compact the database.

run through the first three steps outlined in my previous post to this
thread, and see if your query works any faster than it did when there were
Lookup fields in the data table(s).

hth

 

[Alain T.]

Hi Tina!

Thanks for all the info! I will work on it very soon. Hope I have enough
to be put on the right track!

Alain T.

 

[tina]

you're welcome, and good luck!
if you don't get the results you were hoping for after following the
suggestions i posted, don't give up. start a new thread in this newsgroup,
explain the problem, *and* include the steps you've taken to try to improve
the situation. someone else may be able to help at that point.

hth

 

[Alain T.]

Hi Tina!

I followed your instructions but unfortunately, I still get the speed
problem. What I have done precisely :

1. create a new blank database and immediately turn OFF the Name Autocorrect
option
2. import all objects from my current database into the new one I just
created.
3. compact the database.
4. remove all the Lookup fields in my main table, as you described (open the
table in Design view, click on the field I want to change, then
click on the Lookup tab at the bottom and change the DisplayControl setting
to Text Box). Done that on 21 fields of the main table.
5. compact the database
6. make sure my tables' relationships are correctly set up and referential
integrity enforced, at the table level, using the Relationships
window (didn't have to do that as I have only one main table. All the
others are for the links).
7. make sure that table fields that should be indexed, *are*, and fields
that don't need an index, *are not*. (only one field in the main table is
indexed for all the secondary tables, only the primary key is indexed).
8) in a query, add the "secondary" tables that are needed so that I can show
the "text value" fields associated with the key values stored in the primary
data table(s).

With 14 secondary tables or less, my query runs in 1 sec or less.
With 15 " ", it takes about 2 sec.
With 16 " ", it takes about 5 sec.
With 17 " ", it takes about 20 sec.
With 18 " ", it takes about 1 minute 20 sec.
I didn't take the risk with more than that...

I didn't do yet the next steps as I am still blocked at eigth..
9) in reports, base the report on the type of query described above
(ususally).
10) in forms, base the form on the primary data table, and use combobox or
listbox controls with RowSource property set to the appropriate
secondary table, so that the user has access to the "text values" stored in
the secondary table.

Did I misunderstood something? By the way, in the query, when I added a
secondary table, it linked automatically on the good field from the main
table. And this was done perfectly for all the 21 tables. How was it able
to do that as I removed all the lookup fields from the main table before
that?

Thanks!

Alain T.

 

[Alain T.]

Hi Tina!

As a follow-up about step 6 below :
I have all the 21 secondary tables linked to the main, referential
integrity enforced, joint #3 (right-join).

Alain T.

 

[tina]

i saw your additional post re #6 below; that's good, you *do* need to set
relationships between all those parent (secondary) tables, and the child
(primary data) table - so good job there.

7. make sure that table fields that should be indexed, *are*, and fields
that don't need an index, *are not*. (only one field in the main table is
indexed for all the secondary tables, only the primary key is indexed).

i'm not following this one. are you saying that only the primary key field
in the main table is indexed? if so, try indexing the foreign key fields
(that link to those other tables) in the main table, as well.

if the above suggestions yields no improved results, then it'll be time for
you to begin a new thread, as i previously suggested you might need to do.

hth

 

[Alain T.]

Hi Tina!

Unfortunately, that didn't help either.

Thanks for all the help provided. I will soon start a new thread.

Happy New Year!

Alain T.