July 4, 2008 at 3:18 am
Hello guys,
I have a questions:
I have a stored proc that delete lots of records by time in some tables, the problem is that these tables have a "delete" triggers.
We put into the trigger a clause that recognise this procedure with a "RETURN" to have not action by the trigger when the delete is operated by stored proc.
In this case we reached aou golas to avoid that stored proc delete records and do not make action internally the trigger.
At this point the problem is "Performance": when the store proc starts deleting record, triggers fires (because the clause is inside the triggers) and the application connected with the DB goes in Time-out.
I would like to develop something making sure that when the stored proc starts, it does not fire the triggers.
I also would like to say that triggers must be enabled to make sure that all "delete action" not executed by the stored procedures (eg the application) must fire the trigger and perform the action.
Any suggestions?
Thank
July 4, 2008 at 4:51 am
You could disable the trigger, run the delete and reenable it. There's the risk that another delete runs while the trigger is disabled.
Is there anything about the session/connection properties that identifies the delete as coming from the app? If so, you could put a condition in the trigger so it doesn't do anything if the delete is from the specific application.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 7:27 am
I'm sorry - I think there is no solution that would meet all your requirements.
- if trigger is enabled, trigger will fire on any delete; if trigger is disabled, it will never fire
- some deletes should not result in any action -> this has to be checked inside the trigger, no way to check it before trigger fires
Result => you can not require that the trigger does not fire sometimes.
Maybe, if this is a serious problem, you should reconsider the design and choose some other way.
July 4, 2008 at 7:58 am
I'm surprised that the performance is poor just firing the trigger.
What is the "clause" you have put in the trigger to recognize the stored procedure... is this clause causing the slowdown?
July 4, 2008 at 2:08 pm
records that are to be deleted are around 10*5 records at time, lot of tables to be deleted so the performance decrease.
July 4, 2008 at 2:09 pm
Can you maybe post the code of the trigger?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 3:10 pm
Actually, I think that the problem is more likely to be your stored procedure than your trigger.
Are you using a cursor or a While loop to delete these records?
Please show us the stored procedure's code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 4, 2008 at 9:14 pm
Actually.... I want to see both the trigger and the proc... it would also be nice if you'd post the Create statements for the table being deleted from and any indexes it might have. Can't help unless we can see... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply