Query runs but 20 min to go to last record

[Guest]

I am running the following query sql between two Access files that are not
linked together. The records returned are correct. M_Pro has 10,750 records
and M_Lee has 4,357 records. Each has an index named ZLAF comprised of Zip,
LastName,Address1, and FirstName fields with Unique specified. The query
returns data in just a second.
However, when I use the “go to the last record†icon it will take 20+
minutes to get to the bottom. Have plenty of RAM and hard disk space on a
fairly robust PC. Virus check is up-to-date and clean. No other software is
running this slow. Why does it take so long and what can I do to speed up
going to the last record? Thanks in advance to everyone who can make a
suggestion.
SELECT s1.*
FROM M_Pro AS S1 LEFT JOIN M_Lee AS S2 ON
Left(s1.Zip,5)+Left(s1.LastName,5)+Left(s1.Address1,3) =
Left(s2.Zip,5)+Left(s2.LastName,5)+Left(s2.Address1,3)
WHERE Left(s2.Zip,5)+Left(s2.LastName,5)+Left(s2.Address1,3) is null;

 

[John Vinson]

I am running the following query sql between two Access files that are not
linked together. The records returned are correct. M_Pro has 10,750 records
and M_Lee has 4,357 records. Each has an index named ZLAF comprised of Zip,
LastName,Address1, and FirstName fields with Unique specified. The query
returns data in just a second.
However, when I use the “go to the last record” icon it will take 20+
minutes to get to the bottom. Have plenty of RAM and hard disk space on a
fairly robust PC. Virus check is up-to-date and clean. No other software is
running this slow. Why does it take so long and what can I do to speed up
going to the last record? Thanks in advance to everyone who can make a
suggestion.
SELECT s1.*
FROM M_Pro AS S1 LEFT JOIN M_Lee AS S2 ON
Left(s1.Zip,5)+Left(s1.LastName,5)+Left(s1.Address1,3) =
Left(s2.Zip,5)+Left(s2.LastName,5)+Left(s2.Address1,3)
WHERE Left(s2.Zip,5)+Left(s2.LastName,5)+Left(s2.Address1,3) is null;

The problem is that you're defeating all the indexing on your tables
by Joining the two tables on a complex function rather than on field
values. Access must do a full table scan of M_Pro, and *FOR EVERY
RECORD* in M_Pro it must do a full table scan of M_Lee, call the
Left() function six times, and check to see if there is a match.

Unless you can pull these subfields out and index them, or use the
LIKE operator rather than Left(), it's just going to be slow!

John W. Vinson[MVP]