March 1, 2013 at 12:06 pm
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
March 1, 2013 at 2:10 pm
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.
March 1, 2013 at 2:26 pm
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
March 1, 2013 at 8:31 pm
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.
March 2, 2013 at 8:48 pm
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
March 3, 2013 at 4:53 pm
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
March 3, 2013 at 9:44 pm
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/
March 4, 2013 at 1:12 am
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.
March 4, 2013 at 11:07 am
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
March 4, 2013 at 4:45 pm
No, you don't have to add DID column to the index and it does not have to be unique.
March 4, 2013 at 5:20 pm
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
March 4, 2013 at 5:50 pm
Yes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply