Query on Large table

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have two tables one with 1500 records and the other
with 2 million records. the first table has a partno
field, and i do a query on the second large table looking
for a match of partnos in a corresponding field.
The query is a partial match search as in LIKE "ABCXYZ",
and needs to be done for every record frm the smaller
table.
The speed of returning results is about 2 or 3 searches a
minute, and this speed needs to be bumped up considerably.
Any clues to what will speed this up will be apperciated.
 
Two things to try
1. Use the inbuilt performance analyser to see if access
thinks you need any different indexes.
Tools - Analyse - Performance, then select your query.

2. Compact and repair.
If your database is as large as it sounds, it may pay to
defrag your hard drive first. Access works best if it
occupies contiguous disk space.
 
Does the large table have an index on the partno field? It should.

Why are you using LIKE? Your example would work exactly the same with equals
and might be faster.

If you really do need to use like for your search criteria, then an index won't
help if you are searching for contains fields, as in Like "*ABCDEW*".

Can you redesign the large table to have a field with just the part number and
not whatever extra characters or data are being stored in the field?
 
Back
Top