October 21, 2010 at 1:14 pm
I apolagize if this explination is confusing but I'm trying to be as brief as possible and this is a confusing issue.
If I have a DELETE DML that takes say 10 seconds to run (because of the size of the table and the indexes that must be mainatined) and I add to the table an INSETAD OF DELETE Trigger that does nothing but prevent the DELETE action that woudl have occurred, should that greealty reduce the time the DELETE normally takes?
I know this is an odd question but there's a godo reason for it. In short I'm not tring to prevent a DELETE thatw oudl actuall do something, just subsequent calls to the exact same delete which actually don;t delete anyting because the first time the Delete was called it did delete everything that met the criteria that is used.
This is from a thrid party process I can't control that because it was designed by typical procedural programmer/developers, the DELETE is called more times then it shoudl be, about 105 times more then it needs be.
Thoughts?
Thanks
Kindest Regards,
Just say No to Facebook!October 21, 2010 at 2:00 pm
I'm not sure how an Instead of Delete trigger would know to run (actually delete stuff) the first time and not run in subsequent calls, without potentially complex "mutex" implementations that might be non-ACID.
Because of the way SQL handles locking, subsequent delete calls shouldn't actually be all that big a deal, unless they are being run in such a way that they take a long time to figure out that the data is already gone. I've never seen that be an issue, but that doesn't mean it can't be. It is something you should verify before going into some non-standard solution on this.
What is it that makes you think the subsequent unnecessary deletes are causing a problem?
- 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
October 22, 2010 at 10:42 am
GSquared (10/21/2010)
I'm not sure how an Instead of Delete trigger would know to run (actually delete stuff) the first time and not run in subsequent calls, without potentially complex "mutex" implementations that might be non-ACID.Because of the way SQL handles locking, subsequent delete calls shouldn't actually be all that big a deal, unless they are being run in such a way that they take a long time to figure out that the data is already gone. I've never seen that be an issue, but that doesn't mean it can't be. It is something you should verify before going into some non-standard solution on this.
What is it that makes you think the subsequent unnecessary deletes are causing a problem?
Let me reword this as I see now I did not explain it properly and thats on me.
If I have a DELETE statement that takes on average 2 minutes to run and I drop an INSETAD OF DELETE Trigger on it, should that Triger, assuming it does not really do anything but prevent the delete itself, run very quickly or will it still probably take that same 2 minutes?
Thanks
Kindest Regards,
Just say No to Facebook!October 22, 2010 at 11:22 am
It'll take as long as whatever you have the trigger do takes.
If, for example, the trigger just immediately returns, then it'll take microseconds.
If, for example, the trigger writes a large amount of XML to a log, and builds it through string functions, then it might take a LONG time.
It depends on the trigger.
But if all it's doing is blocking the delete, it won't take very long at all.
- 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
October 22, 2010 at 3:13 pm
GSquared (10/22/2010)
It'll take as long as whatever you have the trigger do takes.If, for example, the trigger just immediately returns, then it'll take microseconds.
If, for example, the trigger writes a large amount of XML to a log, and builds it through string functions, then it might take a LONG time.
It depends on the trigger.
But if all it's doing is blocking the delete, it won't take very long at all.
Thanks; I hoped that would be the most likely result.
Now that I have an extra few minutes I'd like to add some info to this rather odd question so that it makes more sense why anyone would do this anyway.
In short I have to run a third party process that imports data from over 100 files each day. This normally runs around 4AM but for the immediate future we have to run it during business hours. The process does a DELETE FROM followed by an INSERT into a single table using the data from the flat files it is importing. The DELETE has but 1 criteria, any data imported more then 90 days from the current date.
This delete will not delete any rows after the first time it runs (b/c the date has not changed and so no more data meets the criteria after the first time the DELETE runs) however it still runs once for each file being imported. I can't change how this process works.
We are now having to run this during normally bussiness hours and so it is having an impact on everyone. We now have other systems trying to access this table (a lot) while the import is trying to delete from it. My plan was to setup a job that would run several hours before anyone comes in and then prevent the process from running its Deletes by using the INSTEAD OF TRIGGER.
This way I reduce the locks and access to the table as much as I can.
I hope this helps explain this rather odd question.
Thanks for replying
Kindest Regards,
Just say No to Facebook!October 25, 2010 at 1:48 pm
That makes some sense then.
I'm assuming the clustered index on the table is NOT based on some sort of "date added" column. If it were, that would make the deletes very, very fast.
Have you looked into solutions like partitioned tables? Those work well in situations like the one you have, and can make the whole process more efficient, if done correctly.
- 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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply