Delete query very slow (2000/SQL Server)

Topic

New Reply

I am trying to run a delete query that will delete records from a linked SQL Server table. The query runs so slowly that I have never yet seen it finish, although the component parts of the query run quickly. The SQL for the query is:

DELETE dbo_customer.*, dbo_customer.cust_code
FROM dbo_customer
WHERE (((dbo_customer.cust_code) In (select cust_code from qry_DupeRes_8_CustCodesToDelete )));

The query selects the records for deletion using a sub query, which takes customer codes from a second query (qry_DupeRes_8_CustCodesToDelete ). This second query, if I run it on its own, runs instantly, so I don’t think it’s the cause of the problem.
If I remove the sub query from the WHERE clause and replace it with something like WHERE cust_code = “71092” (cust codes are strings) then this version of the delete query also runs instantly.

I am therefore baffled. The query hangs, but its component parts work very quickly.

Ian

Reply | Quote

Replies

Nested queries with In are always slow (Not In is even slower), and combining local queries with SQL Server tables makes it worse . Do you have an index on the cust_code field?
You could change qry_DupeRes_8_CustCodesToDelete into a Make Table query to create a temporary table, and use that in the Delete query.

Reply | Quote

I didn’t realise that nested queries slowed things down so much. As it happens I went down the temp table route and that worked OK.

The reason that I used a nested query was that I couldn’t get the query to run when I included the second query in the FROM clause of the ‘main’ query. The query would list the records to be deleted, when run from the datasheet view button on the top left of the screen, but refused to work when run from the red exclamation mark, or from code. I often find this with delete queries, and usually get round it by using a nested query to select the records to be deleted. I guess I’ve only deleted from mdb based tables using this approach before. But what I would like to know is why it happens. What is it about delete queries that makes them insist on having their WHERE clauses based on a nested query (I think it is only required when the selection is based on the contents of a table that is not the one that the records are being deleted from)? Other action queries don’t seem to be so fussy.

Ian

Reply | Quote

My guess is as follows: in a delete query based on two tables A and B, with a one-to-many join from A to B, and criteria on B, the decision which records to delete from A is ambiguous, since one record in A can correspond to multiple records in B, some of which meet the criteria and others don’t. The nested query approach avoids this ambiguity.

Reply | Quote

So do I assume that it’s not just me that has to use nested queries when selecting records for deletion (when the decision on which records to delete is based on information in another table)? Is there an alternative?

Reply | Quote

I always use nested queries too; that and creating a temporary table are the only methods, I think.

Reply | Quote