Deletes taking long time

  • I have a situation where I created I ran a query that generated about 32,000 individual deletes that I am sending to my client to run on their database.

    Delete x from table where id = 1234

    Delete x from table where id = 1235

    Delete x from table where id = 1237

    On this table, it takes about 3 hours to run.

    It has the requesite clustered PK and 2 foreign keys.

    I have another table same type 2 foreign keys and it takes 40 seconds to delete 90,000 records.

    Why would the table that I am deleting a 3rd as many records take a ton of time longer?

    I thought about disabling the FKs but not sure what happens when I re-enable them. Does it recreate the FKs pointers?

    Thanks,

    Tom

  • tshad (3/1/2013)


    ...

    Why would the table that I am deleting a 3rd as many records take a ton of time longer?

    I thought about disabling the FKs but not sure what happens when I re-enable them...

    When a DELETE is run, it checks all referencing tables that have foreign keys to the table you are deleting in. If the referencing tables don't have an index on the column(s) used in the foreign key and they have a large number of records then yes it could take a long time for the database to check referential integrity.

    If you disable or remove the foreign keys in the referencing table to the deleting table, then after the delete you may have orphaned records in the referencing table unless you absolutely know that no records exist in the referencing table for all the records you are deleting.

  • I am deleting the referenced records first, so if I just disable that table do the deletes then reenable the FK then all should be well?

    Is there a way to check for orphaned records and clear those after the fact?

    Thanks,

    Tom

  • tshad (3/1/2013)


    I am deleting the referenced records first, so if I just disable that table do the deletes then reenable the FK then all should be well?

    Is there a way to check for orphaned records and clear those after the fact?

    Tom

    No, it won't be well. Once disabled, foreign key constraint is marked as "not trusted" and optimizer does not rely on it any more. After that you have to "trust" your constraint with explicit command (which will go through every record and check is constraint satisfied - takes time).

    Let's name the table you delete rows A. If you want to efficiently delete rows from table A, put the indexes on FK columns on all child tables of table A (tables with FK that references your table A) - if they have many rows.

    If you can't afford locking the whole table upfront and delete all at once, do your deletion in batches of e.g. 500, not in single-row delete transactions.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Then maybe the foreign key constraint should be dropped and readded. Not sure how long that would take.

    If you disable a constraint and reenable it and you then lose the use of it, it really is useless.

    My table (A) that I am deleting from has 3 references. Say table A, B, C and D.

    Table A has a reference to B and C

    ALTER TABLE A WITH CHECK ADD CONSTRAINT [FK_B] FOREIGN KEY(BID)

    REFERENCES (BID)

    GO

    ALTER TABLE A WITH CHECK ADD CONSTRAINT [FK_C] FOREIGN KEY(CID)

    REFERENCES C (CID)

    GO

    Then D reference A:

    ALTER TABLE D WITH CHECK ADD CONSTRAINT [FK_A] FOREIGN KEY(AID)

    REFERENCES A (AID)

    GO

    So what is happening is that I am deleting all the records in D that reference all the records in A first (about 98,000 records). For each A record there could b from 1 to 10 records in D. Then I am deleting all the records I want deleted in A (about 31,000 records). If I don't do anything about FK's, it runs for about 3 hours. If I disable D, it takes about 45 seconds. I then found after doing this the first time it did seem to take longer even with disabling the FK's. Maybe because of the "Not Trusted" problem you mentioned.

    I tried to disable and re-enable the table when deleting the records for D and also when deleting the records for A. Something like:

    ALTER TABLE D NOCHECK CONSTRAINT ALL

    Delete D from table where id = 1234

    Delete D from table where id = 1235

    Delete D from table where id = 1237

    ...

    ALTER TABLE D CHECK CONSTRAINT ALL

    then I deleted the records from A, like so:

    ALTER TABLE D NOCHECK CONSTRAINT ALL

    Delete A from table where id = 51234

    Delete A from table where id = 51235

    Delete A from table where id = 51237

    ...

    ALTER TABLE D CHECK CONSTRAINT ALL

    Maybe I should have just dropped the constraint and re-added it after I was done.

    Also, I assume that I don't really have to worry about the B and C FK's on A as there wouldn't be any referential integrity issues with my referencing those tables.

    Thanks,

    Tom

  • Check estimated execution plan of your "delete A" command. Does it SCAN or SEEK table D ? If it SCANs, you probably have no right index.

    Don't drop or disable constraints. Consider partitioning - most efficient delete is to drop a partition in a snap.

    For the "normal" delete, there are only two rules:

    - there should be an index on FK column of the tables that reference the table you delete from (child tables).

    - delete in batches, not one by one

    You delete from A. Child table is D. So, D has FK column that must have index on:

    CREATE INDEX D_IX1 ON D(AID)

    If you delete from D (and you do), the same rules apply: child tables of D must have indexes on their FK columns that reference D.

    Your deletes are not in batches, they are on-by-one. And that's a very slow method. Not just because of zillion times you enter the query that deletes only one row, but also because of implicit commits that happen after each row is deleted. Because of durability (D letter in ACID), commit waits until records are written to the transaction log file, and then acknowledges back that DELETE command is finished. It is shown as a WRITELOG wait event. Therefore it is much faster to have one bigger transaction, than a bunch of tiny ones.

    Here is example of delete in batches:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/9edfffba-c471-41fd-ab91-6a4d06f4a0c9

    WHILE 1 = 1

    BEGIN

    DELETE TOP(500)

    FROM Table

    WHERE ...

    IF @@ROWCOUNT < 500 BREAK

    END

    Your delete should fly.

    HTH,

    Vedran

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Yes, as mentioned above, delete in batches, unless the performance of the target DB does not matter. Any sort of bulk operation causes major index maintenance activities and they are resource-intensive. Bulk U/D may seem fine in testing environment, but in prod, it will cause deadlocks/timeouts.

    https://sqlroadie.com/

  • Any sort of bulk operation causes major index maintenance activities and they are resource-intensive. Bulk U/D may seem fine in testing environment, but in prod, it will cause deadlocks/timeouts.

    If your command puts more than 5000 locks, they will escalate and lock the whole table (or partition if you enabled that on your partitioned table). In that case, concurrency will probably suffer.

    That is why the given example is with TOP 500, and not 5 000 rows - to prevent lock escalation.

    Doing delete one-by-one actually is far more resource intensive than doing it in batches (takes hours instead of seconds).

    So, if you are concerned about performance of the target database, DONT do it one-by-one (but also not with too big batch).

    If you want writes not to block readers and readers not to block writers, enable READ COMMITTED SNAPSHOT isolation at the database level and enjoy even greater concurrency.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Would I also have to reference the PK in D as well:

    CREATE INDEX D_IX1 ON D(AID, DID)

    Also, would it be best to have it "Unique" as well.

    This normally doesn't happen. We are only doing these deletes one time because of some changes in business rules. So I would really only add this index one time, run it and the then drop it. I don't really want to slow other processes down with the extra indexes that are not needed.

    Thanks,

    Tom

  • No, you don't have to add DID column to the index and it does not have to be unique.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Thanks, just wanted to make sure.

    BTW, if I did want to make it unique, would I then use:

    CREATE INDEX D_IX1 ON D(AID, DID)

    Thanks,

    Tom

  • Yes

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply