Help with slow query

[gavine]

I have one table with phone numbers [Phone Assignment List], and another table with phone usage transactions [Call Detail Data] I want to have a report which lists the unqiue phone numbers which appear on the transaction report but not in the phone number table.

I wrote the below query which works correctly, but is very slow. Is there a faster and simpler way to do this?

Thanks!

SELECT DISTINCT [Call Detail Data].PHONENUMBER,
[Call Detail Data].AMOUNT,
[Call Detail Data].CALLDATETIME
FROM [Call Detail Data]
WHERE ((([Call Detail Data].PHONENUMBER) Not In
(select [Phone Assignment List].PHONENUMBER
from [Phone Assignment List])));

 

[plog]

This method won't require a subquery:

SELECT [Call Detail Data].PHONENUMBER
FROM [Call Detail Data] LEFT JOIN [Phone Assignment List] ON [Call Detail Data].PHONENUMBER = [Phone Assignment List].PHONENUMBER
WHERE ((([Phone Assignment List].PHONENUMBER) Is Null))
GROUP BY [Call Detail Data].PHONENUMBER;

Also you can index the PHONENUMBER fields in both tables.

 

[gavine]

Excellent, thanks so much! Works like a charm.

 

[Pat Hartman]

To summarize - Ace/Jet will usually be faster with a join than a subquery. You may have different results with different RDBMS' but many times a subquery and join are interchangeable as in this example so it always pays to try the other method if the one you chose turns out to be slow.