Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query very slow (2000/SQL Server)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Delete query very slow (2000/SQL Server)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query very slow (2000/SQL Server)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Delete query very slow (2000/SQL Server)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query very slow (2000/SQL Server)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Delete query very slow (2000/SQL Server)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •