locked
Delete is too slow RRS feed

  • Question

  •  

    Hi,

     

    I am trying to delete rows from two tables. My first delete statements are working fine but my second delete is too slow.

     

    The 'results' table has 22728624 rows. The delete below works fine and fast.

        DELETE FROM [results]
        FROM [results]
          INNER JOIN [lots]
          ON [lots].[id] = [results].[lot_id]
        WHERE lots.ww < 200808

    Then I tried to delete the 'lots' table that only has 11524 rows. This delete below is too slow and I have to cancel it because it is taking forever. I have tried to delete one row and it took 11 seconds. 

        DELETE FROM [lots]
        WHERE lots.ww < 200808

     

    I am new in MS SQL and I dont know what to do next.  Can you give some advice on how I can improve the delete operation?

     

    Additional tables information:

      table name: lots

      columns:

       id (PK)

       lot

       ww

     

      table name: results

      columns:

      id (PK)

      lot_id (FK)

      (there are 10 more result columns after lot_id) 

     

    Regards,

    vpdeguz

    Monday, May 19, 2008 1:56 AM

Answers

  • The main reason the delete action is slow is because of foreign key constraint.

     

    So, if you are sure the delete action won't violate the foreign key constraint. You can try to disable and re-enable the

    foreign key.

     

    CREATE TABLE lots(

    id int primary key,

    lot sysname,

    ww int

    )

    CREATE TABLE results(

    id int primary key,

    lot_id int constraint fk_re***s_lots foreign key references lots(id),

    na sysname

    )

    ALTER TABLE results NOCHECK CONSTRAINT fk_re***s_lots;

    DELETE FROM [lots] WHERE lots.id = 200808

    ALTER TABLE results CHECK CONSTRAINT fk_re***s_lots;

     

     

    Thanks,

    Zuomin

     

    Monday, May 19, 2008 4:20 AM

All replies

  • Seems like you may need an INDEX on Lots.ww.

     

     

     

    Monday, May 19, 2008 2:08 AM
  • Thanks for the reply...

     

    I have tried making the Lots.ww an index -- same results. Also, I have tried to delete using the 'id' (PK):

     

      delete FROM [lots] WHERE id = 12407

     

    It took 14 seconds.

     

    Regards,

    Vergel

     

     

    Monday, May 19, 2008 2:26 AM
  • how many rows are effected by each delete statement?

     

    Monday, May 19, 2008 2:34 AM
  • Only one row was effected by doing this and it took 14 sec:   delete FROM [lots] WHERE id = 12407

     

    I tried to run delete statement below. Effected rows were 389 and it took 1 minute and 26 sec. It didn't took a forever as before maybe because It is now an index (as suggested). But still, I think it is too slow for deleting 389 rows.

     

    delete FROM [lots] WHERE ww < 200808

     

    regards,

    vpdeguz

     

     

     

     

     

    Monday, May 19, 2008 2:49 AM
  • The main reason the delete action is slow is because of foreign key constraint.

     

    So, if you are sure the delete action won't violate the foreign key constraint. You can try to disable and re-enable the

    foreign key.

     

    CREATE TABLE lots(

    id int primary key,

    lot sysname,

    ww int

    )

    CREATE TABLE results(

    id int primary key,

    lot_id int constraint fk_re***s_lots foreign key references lots(id),

    na sysname

    )

    ALTER TABLE results NOCHECK CONSTRAINT fk_re***s_lots;

    DELETE FROM [lots] WHERE lots.id = 200808

    ALTER TABLE results CHECK CONSTRAINT fk_re***s_lots;

     

     

    Thanks,

    Zuomin

     

    Monday, May 19, 2008 4:20 AM
  •  

    Further to Arnie's suggestion, I'd suggest an index on results.lot_id.  The reason for this, is when you run

    DELETE FROM [lots]
    WHERE lots.ww < 200808

    SQL Server needs to find any lots where ww values < 200808.  Once those have been found (via Arnie's suggested index), it needs to check the results table to determine if any of the lots.id values that you're deleting exist in the results table (as results.lot_id).  Zoumin's solution works by deactivating the check, whereas an index on results.lot_id should allow you to perform the check immediately (instead of scanning the entire results table).

     

     

    Monday, May 19, 2008 5:11 AM
    Answerer