Can a trigger affect .Net app performance?

Question

Can a trigger affect .Net app performance?

dubem1-878067

Ten Centuries

Points: 1044
More actions
November 30, 2008 at 12:44 pm

#198236

Well I think I guess the answer but I not sure so I ask.
If my .Net application calls a Stored procedure to do and insert. And there is a trigger on this table doing a job on insert that take two minutes to execute (I exagerate, it's only for the purpose of the question).
What I want to know is, will the .net app will be idle until the trigger is done? My guess is the database will give back the control to the app after the insert is done and will execute the trigger on is side.
Thank you
Martin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Triggers are synchronous, not asynchronous, and are part of your applications synchronous batch of SQL requests. So unless you are using asynchronous .net calls, your app will be stalled until the trigger is done.

Any trigger that takes 2 minutes to complete would be considered unaccaptable in any environment that I have ever worked. Even 2 seconds would be too long 99% of the time.

Anything that takes that long should not be done in a trigger.

[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]

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 2

Thanks for you answer

The 2 minutes execution was just for the purpose of the question, to help to figure the meaning of the question.

I know a trigger shouldn't be that long.

Martin

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Glad I could help.

[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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

In general, triggers should not start jobs, send email, launch applications or anything of the sort. A trigger should be doing small data verifications or changes, nothing more.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

I just want to add to Gail's statment that if you feel that you do need to do these things in a trigger, you should consider using Service Broker to de-synch these activities from the trigger and the originating batch.

[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]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

I definately agree with rbarryyoung on this. Service Broker conversations are async and will allow you to queue the work and continue processing.