cascading deletes
-
does cascading deletes give better performance vs manually deleting all FK records?
-
No.
use tempdb
create table p(
pid int identity primary key)
create table f(
fid int identity primary key nonclustered,
pid int constraint fk_f_pid foreign key references p on delete cascade)
set nocount on
while 1 = 1 begin
insert p default values
if scope_identity() = 1000 break end
declare @i int, @c smallint
set @i = 1
set @c = 1
while @i <= 1000 begin
insert f(pid)
select @c
set @c = @c + 1
if @c > 100
select @c = 1, @i = @i + 1
end
create clustered index ix_f_pid on f(pid)
delete from p
where pid = 100
alter table f nocheck constraint fk_f_pid
delete from f
where pid = 200
delete from p
where pid = 200The first delete takes 96.94% of the time, according to QA.
--Jonathan
--Jonathan -
interesting.
what about a situation where u have, for one process, 2000 sp calls to delete FK records. Would cascading deletes be faster there?
-
quote:
interesting.what about a situation where u have, for one process, 2000 sp calls to delete FK records. Would cascading deletes be faster there?
No.
--Jonathan
--Jonathan -
Really, the fastest way to do deletes is not to have any FK contraints at all.
🙂
I know this sounds crazy, but I've been working on a project where I can do this. All deletions and insertions are handled through custom procs, which are aware of the table structure and will delete all or nothing. Performance has been amazingly fast compared to using FK.
Don't know if this is a good idea yet...still working on it. The important thing is to lock permissions down so only the procedures have rights to DML statements on those tables.
Signature is NULL
-
quote:
Really, the fastest way to do deletes is not to have any FK contraints at all.🙂
I know this sounds crazy, but I've been working on a project where I can do this. All deletions and insertions are handled through custom procs, which are aware of the table structure and will delete all or nothing. Performance has been amazingly fast compared to using FK.
Don't know if this is a good idea yet...still working on it. The important thing is to lock permissions down so only the procedures have rights to DML statements on those tables.
That's what I wrote.
I believe it's documented somewhere (BOL?) that a disadvantage of using DRI is that it's slower than handling it yourself. Not crazy to me; check the execution plans on the script I posted above.--Jonathan
--Jonathan -
It's no wonder to me that the first delete in your script takes 96.94% of the time. The first delete has to delete 1000 records from f, while the second delete does nothing. No entry in table f has a pid higher than 100. What happens when you replace "200" with "50" so that the 2nd delete does the same amount of work as the 1st?
--Dan Franklin
I believe it's documented somewhere (BOL?) that a disadvantage of using DRI is that it's slower than handling it yourself. Not crazy to me; check the execution plans on the script I posted above.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply