Looking up missing keys

Question

Can I speed up this statement on Ms Access 2007?

SELECT *
FROM (SELECT DISTINCT p.zipcode, p.place FROM p) AS tmp
WHERE NOT EXISTS 
  (SELECT * FROM zipcodes WHERE 
    (tmp.zipcode=zipcodes.zipcode) AND (tmp.place=zipcodes.place));

I want to find the (zipcode, place) combinations in the p table that are not in the zipcodes table.

The ptable is rather large, but when reduced to tmp has around 40.000 entries. The zipcodes table has around 15.000 entries. The zipcodes table is indexed on (zipcode, place).

It takes over one hour to get my result. Can I get it faster? Can I see an execution plan in Access 2007? (I am not a regular Access user and also not a SQL expert.)

Kind regards, Karsten.

Answer 1

You can LEFT JOIN the two tables and exclude the matching rows from the result set. With the zipcodes table indexed on zipcode and place, this could be much faster than what you have now. Hopefully fast enough you need not resort to creating an intermediate temporary table.

SELECT DISTINCT p.zipcode, p.place
FROM p LEFT JOIN zipcodes AS z ON (p.place = z.place) AND (p.zipcode = z.zipcode)
WHERE (((z.zipcode) Is Null));

Edit: You asked to see an execution plan. Start with this article on TechRepublic: Use Microsoft Jet's ShowPlan to write more efficient queries You can also find more information by searching the web for "Jet ShowPlan".