Delete is too slow

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

Answer 1

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

 

 

 

Answer 2

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

 

 

Answer 3

how many rows are effected by each delete statement?

 

Answer 4

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

 

 

 

 

 

Answer 5

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

 

Answer 6

 

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).