Geek asks and answers

Search for missing keys

Is it possible to 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 combinations (zipcode, place) in a table pthat are not in the table zipcodes.

The table is pquite large, but when reduced to tmpabout 40,000 records. The table zipcodeshas about 15,000 entries. The table is zipcodesindexed (zipcode, place).

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

Regards, Carsten.

+3
source share
2 answers

JOIN . zipcodes zipcodes , . , , .

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));

. . TechRepublic: Microsoft Jet ShowPlan . , "Jet ShowPlan",.

+5

-, "" tmp , . ( ). -, , zipcode p, , tmp. , (zipcode, place) zipcodes.

+1

More articles:


All Articles