CODEWITHSUNDEEP
sqlms-access
Karsten W.
Karsten W.

Reputation: 18490

Looking up missing keys

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.

Upvotes: 3

Views: 800

Answers (2)

HansUp
HansUp

Reputation: 97131

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".

Upvotes: 5

Thomas Mueller
Thomas Mueller

Reputation: 50127

First, I would create a 'real' table tmp instead of using it in this way. This alone may already help (not sure). Second, I would ensure there is an index on zipcode in the table p, and if this doesn't help, also on the table tmp. If this still doesn't help, also create an index on the combination of (zipcode, place) on zipcodes.

Upvotes: 1

Related Questions