slow delete query

  • Thread starter Thread starter Michel Peeters
  • Start date Start date
M

Michel Peeters

I have following query:
DELETE tblLosplaats.LosplAn, *
FROM tblLosplaats
WHERE (((tblLosplaats.LosplAn) Not In (SELECT LosplAn from tblAfname)));

tblAfname has about 70000 records.
tblLosplaats has about 2000 records.

The query works, but it takes about 35 minutes.

How can I improve the speed?

Michel
 
Hi Michel,

Instead of using a subquery, try adding tblAfname to the
query, and doing an outer join between the two (all
records from tblLosplaats and matches from tblAfname).
Then, add tblAfname.LosplAn to the query, select where,
and then set Is Null as the criteria. This should limit
the query to all records that do not have a match in
tblAfname (always back up the database before doing an
untested delete query).

Also, in any case, make sure that the tables are indexed
on the LosplAn field.

HTH, Ted Allen
 
ted,
I tried:
DELETE tblLosplaats.LosplAn, tblAfname.LosplAN, *
FROM tblLosplaats RIGHT JOIN tblAfname ON tblLosplaats.LosplAn =
tblAfname.LosplAN
WHERE (((tblLosplaats.LosplAn) Is Null));

The query shows the records to be deleted but on execution asks "specify the
table containing the records you want to delete".
tks for help

Michel
 
Hi Michel,

I think this is what Ted had in mind:

changing

DELETE tblLosplaats.LosplAn, *
FROM tblLosplaats
WHERE (((tblLosplaats.LosplAn)
Not In (SELECT LosplAn from tblAfname)));

to this:

DELETE DISTINCTROW tblLosplaats.*
FROM tblLosplaats LEFT JOIN tblAfname
ON tblLosplaats.LosplAn = tblAfname.LosplAn
WHERE (((tblAfname.LosplAn) Is Null));

Please test this on a backup copy to verify.

Good luck,

Gary Walter
 
Hi Michel,

Sorry I wasn't in yesterday to respond (I was home with
our 8 mo old who had an ear infection), but Gary's post
was right on the money. Try pasting that sql text into a
query and see if it will work (but back up the database
first as he mentioned).

The main differences from the sql that Gary posted and
what you had were that the join is reversed, the query is
only deleting from the target table (yours also had a
field from tblAfname in the FROM clause), and his
specified that it was Distinct.

I think I forgot to mention that it is important to
specify DISTINCTROW for delete queries based on
relationships between multiple tables. I think it was
the default for earlier versions of Access, but around
A2K or A2002 that was changed. FYI, if you are using the
query builder to design your queries you can specify
DISTINCTROW by changing the queries "Unique Records"
property to Yes.

Try Gary's sql and post back with the results.

HTH, Ted Allen
 
tks boys, this works 100 times faster
michel
Ted Allen said:
Hi Michel,

Sorry I wasn't in yesterday to respond (I was home with
our 8 mo old who had an ear infection), but Gary's post
was right on the money. Try pasting that sql text into a
query and see if it will work (but back up the database
first as he mentioned).

The main differences from the sql that Gary posted and
what you had were that the join is reversed, the query is
only deleting from the target table (yours also had a
field from tblAfname in the FROM clause), and his
specified that it was Distinct.

I think I forgot to mention that it is important to
specify DISTINCTROW for delete queries based on
relationships between multiple tables. I think it was
the default for earlier versions of Access, but around
A2K or A2002 that was changed. FYI, if you are using the
query builder to design your queries you can specify
DISTINCTROW by changing the queries "Unique Records"
property to Yes.

Try Gary's sql and post back with the results.

HTH, Ted Allen
Click to expand...
 
Back
Top