Simpy query take 10 minutes for execute -

[pio92]

I have a simply query in MS Access:



SELECT *
FROM D1
WHERE D1.ID NOT IN (SELECT cID FROM D2)

D1 is a query with one column (ID) and contain ca. 2100 records.
D2 is a query with one column (cID) and contain ca. 300 records.

Why my query need ca. 10 minutes for execute ?
How can I optimize it ?

[Minty]

It would be better to use an left join rather than a sub query

SELECT D1.*
FROM D1 LEFT JOIN D2 On D2.cID = D1.ID
WHERE D2.cID IS NULL

Air code untested

[CJ_London]

perhaps your D1 and D2 queries are not optimised - but agree with Minty's suggestion

[isladogs]

I agree with the suggestion in post #2. That is what you get using an unmatched query wizard.
Subqueries are always slow and should be avoided if other methods are available.

Even so, it shouldn't take more than a few seconds to run your query. Are both fields indexed?

For more general advice on optimising queries, see my article Speed Comparison Tests 8 (isladogs.co.uk)

[ArviLaanemets]

No way so slow work of so simple query from so small tables is caused by design only. Probably the reason is outside of Access (antivirus, network connection problems, etc.)

Some 20 years ago I had a similar situation (a query from table with subquery from another table), and the reason was antivirus, which did make full scan of of both tables for every row of main table. We switched the antivirus off, and instead of 20 minutes the query finished in couple of seconds.

[pio92]

"It would be better to use an left join rather than a sub query

SELECT D1.*
FROM D1 LEFT JOIN D2 On D2.cID = D1.ID
WHERE D2.cID IS NULL

Air code untested"


Thank you very much, now its take only 5 seconds for execute.

[pio92]

BTW.
I have two queries more they are very slow too.

UPDATE temp SET F10 = DLookUp("scanned","4b","[4a.ID] =" & temp.ID)

is here a way for optimalize ?

[Minty]

Again Join the data and use appropriate criteria, something like

UPDATE temp
Inner Join 4B ON 4B.ID = Temp.ID
Set F10 = 4b.Scanned

Again air code untested

You really should give your tables and fields meaningful names, if you revisit this in 6 months you wouldn't have clue what it was doing.
ID of what ? As you can see having all your Autonumber fields called ID makes this difficult to follow the logic.