Using subqueries in delete queries is really slow?

[acteck]

Here is the task: delete all duplicates by field from the table and keep the one with say max(ID):

DELETE *
FROM tbl_1
WHERE ID not IN
(SELECT  Max(tbl_1.ID) AS MaxOfID
FROM tbl_1
GROUP BY tbl_1.Fields);

And it performs painfully slow even though i have indexes
If i am using temp table to insert result of grouping into , the code executes almost instantly.
Is it just Access thing ?
Thank you!

 

[MarkK]

You have an index on tbl_1.Fields?

 

[acteck]

yep! I have index on tbl_1.field. It did not increase the performance of that query at all

 

[MarkK]

And how many records are we talking about here, like how many records does this ...

SELECT Max(ID) AS MaxOfID
FROM tbl_1
GROUP BY [Fields]

... return? And how many records are in the table tbl_1 before the delete?
Curious,

 

[acteck]

Not much! probably around 10 -15 thousands! so the result should be 2-3 thousands