August 13, 2008 at 3:23 pm
This is a pretty cool piece of code but I have a question about it. This is a proc to get rid of Cascade delete. They think this is the same way delete cascade works. I find this hard to believe. A recursive cursor just seems like such a bad way to remove records. So we have created a relationship table that we maintain so when you pass in the table we know what path we want to delete. Feel free to chime in on this
CREATE PROCEDURE [dbo].[deletion]
@TableNamenvarchar(100)
, @TableKeynvarchar(100)
, @IDuniqueidentifier
AS
DECLARE@ChildPrimaryKeynvarchar(50)
, @ChildTablenvarchar(50)
, @ForeignKeynvarchar(50)
DECLARE @ChildTables CURSOR
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @ChildTables = CURSOR FOR
SELECT ChildTable, ChildPrimaryKey, ForeignKey
FROM Relationships
WHERE ParentTable = @TableName
OPEN @ChildTables
FETCH NEXT FROM @ChildTables INTO @ChildTable, @ChildPrimaryKey, @ForeignKey
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('declare @childID uniqueidentifier
declare @childvalues cursor
set @childvalues = cursor for
select ' + @ChildPrimaryKey + '
from ' + @ChildTable + '
where ' + @ForeignKey + ' = ''' + @ID + '''
open @childvalues
fetch next from @childvalues into @childID
while @@fetch_status = 0
begin
Exec deletion ' + @ChildTable + ', ' + @ChildPrimaryKey + ', @childID
fetch next from @childvalues into @childID
end
close @childvalues
deallocate @childvalues ')
FETCH NEXT FROM @ChildTables INTO @ChildTable, @ChildPrimaryKey, @ForeignKey
END
print 'delete ' + @TableName
Exec ('delete from ' + @TableName + ' where ' + @TableKey + ' = ''' + @ID + '''')
--print 'delete from ' + @TableName + ' where ' + @TableKey + ' = ''' + convert(nvarchar(50),@ID) + ''''
CLOSE @ChildTables
DEALLOCATE @ChildTables
END
August 13, 2008 at 3:35 pm
You should not need a cursor to run a delete. What's wrong with a simple SP that does the following?
BEGIN TRANSACTION
DELETE c1
FROM Child1 c1
INNER JOIN Parent p ON p.Key = c1.Key
WHERE
DELETE c2
FROM Child2 c2
INNER JOIN Parent p ON p.Key = c2.Key
WHERE
DELETE Parent
WHERE
COMMIT TRANSACTION
August 13, 2008 at 3:39 pm
This is looping through multiple children and then deleting out there children.
orders
orderdetail
orderdetaillines
order group
ordergroupdetails
It works dynamically from the table setup.
August 13, 2008 at 3:52 pm
OK, then you have to rely on the setup table maintaining your data integrity? Why add another layer? If you were going to go this route, why not just build it to work off of the built in DM views that already house the table/object relationships?
Anyway, I would still wrap this all in a SP. I don't see that you are buying anything by doing it dynamically. In fact, you're adding in inefficiencies with dynamic SQL and cursurs. When all you really need to do is manage the SET based deletes in the correct order.
orders
orderdetail
orderdetaillines
order group
ordergroupdetails
BEGIN TRANSACTION
--delete level 2 children
DELETE dbo.OrderGroupDetails
FROM dbo.OrderGroupDetails ogd
INNER JOIN dbo.OrderGroup og ON od.ID = ogd.ID
INNER JOIN dbo.Orders o on o.ID = og.ID
WHERE o.ParentCriteria = Value
DELETE dbo.OrderDetailLines
FROM dbo.OrderDetailLines odl
INNER JOIN dbo.OrderDetail od ON od.ID = odl.ID
INNER JOIN dbo.Orders o on o.ID = od.ID
WHERE o.ParentCriteria = Value
--delete level 1 children
DELETE dbo.OrderDetail
FROM dbo.OrderDetail od
INNER JOIN dbo.Orders o ON o.ID = od.ID
WHERE o.ParentCriteria = Value
DELETE dbo.OrderGroup
FROM dbo.OrderGroup og
INNER JOIN dbo.Orders o ON o.ID = og.ID
WHERE o.ParentCriteria = Value
--delete top level parent
DELETE dbo.Orders
WHERE ParentCriteria = Value
COMMIT TRANSACTION
August 13, 2008 at 4:03 pm
a good data modelling tool can generate a cascade delete trigger for you. so that proc/table seems to be reinventing a wheel.
August 13, 2008 at 4:05 pm
you have to think larger scale on this. This is one proc that you can send any table in a relational DB and it deletes all data pertaining to that record child wise. I am running it and it is only about 2 seconds slower then cascade. So instead of doing that static code you are putting in place in the proc I am putting the relations in a table and allowing it to be viewed by the programmers and users in the app. So if you are using express and want to delete a group of data since you are at your 4 gig limit. Run this and then run a shrink on the db.
August 13, 2008 at 4:06 pm
I had delete cascade working before this but they wanted more flexibility. They said if this works it would help in the long run.
August 13, 2008 at 4:11 pm
I've got to roll out for the day, but I'm real curious as to what some of the other heavy hitters are going to say.
You mentioned thinking bigger. I'd be real concerned about what will happen as you try to scale this out to a DB with 1M row + tables. It's gonna be a dog compared to static deletes. If anything, why not generate the sql delete code based off of the relationships?
August 13, 2008 at 5:28 pm
JKSQL (8/13/2008)
you have to think larger scale on this. This is one proc that you can send any table in a relational DB and it deletes all data pertaining to that record child wise. I am running it and it is only about 2 seconds slower then cascade. So instead of doing that static code you are putting in place in the proc I am putting the relations in a table and allowing it to be viewed by the programmers and users in the app. So if you are using express and want to delete a group of data since you are at your 4 gig limit. Run this and then run a shrink on the db.
dbArtisan can write a cascade delete for you also and it does so much more. my point is that there are already tools that do this AND so much more.
these tools help reduce the time it takes to do common things (like cascade deletes). your proc may be slick, but i'm sure you (and your fellow developers) are being paid to do things specific to your business' needs that an off-the-shelf package can't do. now if your company won't pay for such tools (and they can be expensive), that's another issue.
August 14, 2008 at 8:35 am
I've seen this kind of recursive cursor call before. Not the same situation, but quite similar.
It worked just fine, on very, very small data sets. Was a couple seconds slower than a more standard approach, so long as the test data was only a few dozen rows and no more than two or three levels deep.
As soon as the data began to grow, it got slower, and slower, and slower, till at one point, it was taking over 8 minutes to get things done.
I replaced it with a more standard solution, and got that 8 minutes down to less than 1 second.
It's one of those things that looks like a good idea at first glance, and the initial tests on it look quite promising, but in the long run, it causes more problems than it solves, and is a pain to fix.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 8:51 am
Thanks for the advice. Everything I have ever been taught says No to cursors especially two cursors recursively calling themselves. I have to imagine when I put this up to a larger record set it will die and bomb out. I am going to test that out today and then discuss this with my team lead. I appreciate the advise on the subject. Compared to this what are the downfalls associated to delete cascade if I go that way. Am I going to hit the same bottle necks?
August 14, 2008 at 12:04 pm
The main problems with cascading deletes, at least in my experience, are the limitations on multiple chains hitting the same table, and issues with triggers that weren't designed correctly.
On the first point, see what happens if you build a many-to-many table, and set up the FKs on it to cascade delete if either parent table has a row deleted. You get an error message.
On badly designed triggers, I'm refering specifically to On Delete or Instead of Delete triggers on sub-tables (where the FK is). If those roll-back or block the deletion, you may not even get any notification, but you won't get your deletion.
Outside of those limitations, cascading deletes aren't too bad. Of course, chaining through 100 levels of, "If you delete from TableA, you have to delete from TableB; if you delete from TableB, you have to delete from TableC; ad infinitum", will result in performance issues when you delete from TableA. No way to avoid that (well, except design the database better, if possible).
Set-based triggers, and cascading FKs, are going to be faster than this recursive cursor solution, but how much faster really depends on your database design.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 12:09 pm
I just wanted to say thanks. Once I put this proc into a production setting it was destroyed not only time wise but it locked up. The Team is now looking back to what your routes are more like. The ones above suggested.
August 14, 2008 at 1:12 pm
Glad I could help. You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply