Query turnaround time

[jjb373]

This is my number 1 problem that I can not figure out. We are using access to keep track of training records. I have two tables, one that has the training needed and one that has the training taken. In the training needed table there are about 1,500 records In the training taken there are about 10,000 records and growing each month. I’m running a make-table query that list all the items in the training needed that are not in the training taken. From this table I’m running all of my reports.

My problem is that when I run the make-table query it takes about 10 minutes, and seams to be getting longer has the records add up. Do you know of any way to speed this up? I have already limited the number of characters in each field to 50.

Here is my SQL as a reference if it is of any help...

SELECT Eliminatro2Pro.Name, Eliminatro2Pro.Procedure, Eliminatro2Pro.Revesion, Eliminatro2Pro.RevDate INTO ProcedureUpdates

FROM Eliminatro2Pro LEFT JOIN EliminatroPro ON Eliminatro2Pro.x = EliminatroPro.X

WHERE (((EliminatroPro.X) Is Null));

 

[Golom]

Do you have indexes defined on the "X" field in both tables?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jjb373]

No, I do not have an index, or a primary key for either table.

One table is simply a list of the procedures read, and the other is a list of the ones needed.

 

[Golom]

Without indexes Access must do what is called a table scan to resolve the query. That means that it must read every record in each table and compare them. Ovbiously, the more records, the longer it takes. With indexes however Access can just read the index to determine which records match the conditions in the query.

If you create indexes on the "X" fields in each table (i.e. the fields that you are comparing) you should see a dramatic improvement in speed.