Inner Join on left() too slow

[vjp2.at]

This takes forever. I sthere any way to rearrange it so it is faster?
qsen11 has a million records while pollworkers has 1500

SELECT qsen11.email
FROM qsen11 INNER JOIN pollworkers ON (left(qsen11.[FIRST
NAME],3)=left(pollworkers.pwfirst,3)) AND (left(qsen11.[LAST
NAME],5)=left(pollworkers.pwlast,5)) AND
(left(qsen11.house,2)=left(pollworkers.pwadr,2))
WHERE Not isempty(qsen11.email);



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]

 

[Bob Barrows]

This takes forever. I sthere any way to rearrange it so it is faster?
qsen11 has a million records while pollworkers has 1500

SELECT qsen11.email
FROM qsen11 INNER JOIN pollworkers ON (left(qsen11.[FIRST
NAME],3)=left(pollworkers.pwfirst,3)) AND (left(qsen11.[LAST
NAME],5)=left(pollworkers.pwlast,5)) AND
(left(qsen11.house,2)=left(pollworkers.pwadr,2))
WHERE Not isempty(qsen11.email);

Indexes cannot be used when comparing the results of functions performed on
fields. The only way to make this query faster is to put the data that you
are parsing out of the qsen11 fields into their own indexed fields in that
table. With only 1500 records in pollworkers, it is not as critical to do it
there as well, but it cannot hurt.

 

[John W. Vinson]

SELECT qsen11.email
FROM qsen11 INNER JOIN pollworkers ON (left(qsen11.[FIRST
NAME],3)=left(pollworkers.pwfirst,3)) AND (left(qsen11.[LAST
NAME],5)=left(pollworkers.pwlast,5)) AND
(left(qsen11.house,2)=left(pollworkers.pwadr,2))
WHERE Not isempty(qsen11.email);

Try putting nonunique Indexes on qsen11 FIRST NAME, LAST NAME and HOUSE if
they're not there already (or index both tables, though it won't matter with
the smaller pollworkers table) and changing this to

SELECT qsen11.email
FROM qsen11 INNER JOIN pollworkers
ON (qsen11.[FIRST NAME] LIKE left(pollworkers.pwfirst,3) & "*")
AND (qsen11.[LAST NAME] LIKE left(pollworkers.pwlast,5) & "*")
AND (qsen11.house LIKE left(pollworkers.pwadr,2) & "*")
WHERE qsen11.email IS NOT NULL;

The LIKE operator will use indexes if the wildcard is at the end. Not sure
about your IsEmpty criterion but try the IS NOT NULL instead - you should be
able to go back to IsEmpty if the NOT NULL criterion doesn't work as expected.
The JOINS will be a heck of a lot faster than the function calls though!

Matching on names and on two letters of an address WILL get false drops and
WILL miss some records, but I presume you know that...
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[vjp2.at]

I had messed up the LIke by using parens then got rid of
it and just used left=left. WHen I went back to like, it worked.
But without it it just hung the machine.

Many thanks!



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]