Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mary_itohan
Posting Yak Master
191 Posts |
 Posted - 2008-10-05 : 04:24:22
|
| Hello,I have learnt that triggers impact performance. How much impact does it have and why ?I have only one trigger in our application which basically calls a SP to process the work on completion, so as the calling application will be free to give a status code to the user. ie1. user runs a command via front end2. SP_Before returns a status code to the users and logs the records3. trigger fires and runs another SP_After4. SP_After takes it time to run which the user is completely unaware ofREASON is thus, SP_After takes a much longer time to execute and wont want to keep the frontend waiting thereby causing timeouts.Is the above approach correct ? Or are there better ways of doing thisThanks_____________________Yes O ! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 04:38:31
|
| What does SP_After logic do? |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-05 : 05:01:05
|
| Trigger code does not run asyncronously if that's what you mean. Sounds like you need to kick off a job or something. Also you need to consider what the transactional requirements are for the whole process? |
|
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-10-05 : 05:03:52
|
quote:
Originally posted by visakh16
What does SP_After logic do?
It does a lot of billing and logging and also connects to a remote server to update products purchases, which "MUST" be realtime and online.We used this approach, because once the user has enough credits/money to purchase a product, he gets a status code as ok, which is done by SP_BeforeAnd SP_After does the real work which is oblivious to the user_____________________Yes O ! |
|
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-10-05 : 05:08:06
|
quote:
Originally posted by LoztInSpace
Trigger code does not run asyncronously if that's what you mean. Sounds like you need to kick off a job or something. Also you need to consider what the transactional requirements are for the whole process?
If triggers are so bad, then WHEN do I use and trigger. And why were they created in the first place.Sounds to me that they are worse than cursors._____________________Yes O ! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 05:42:37
|
quote:
Originally posted by mary_itohan
quote:
Originally posted by LoztInSpace
Trigger code does not run asyncronously if that's what you mean. Sounds like you need to kick off a job or something. Also you need to consider what the transactional requirements are for the whole process?
If triggers are so bad, then WHEN do I use and trigger. And why were they created in the first place.Sounds to me that they are worse than cursors._____________________Yes O !
its a fact that they will have a hit on performance. they are mostly used for cases like logging changes in data and capturing data changes (audits). Its not advisable to put really complex logic onto trigger.I think you need to schedule a job for performing the activities like connects to a remote server to update products purchases. you could design the job such that it picks up incremental (delta) changes every 10 mins or so and does the update in remote server. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-06 : 05:58:23
|
| Ignore triggers for a moment. How did you plan to perform other other stuff oblivious to the user and yet keep it in the same transaction? Either a) you have it in the same transaction, whereby the user must be aware of it and the entire process must wait for completionb) You can have it working in the background but you will not know the outcome until it has completed and so have to check for it.If b) you need a process to run it, deal with errors, retries etc, notify the user that things have finished/not gone to plan. There is no magic solution here - you are going to have to decide what you want to do and put some analysis and hard work in. |
|
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-10-06 : 06:36:47
|
quote:
Originally posted by LoztInSpace
Ignore triggers for a moment. How did you plan to perform other other stuff oblivious to the user and yet keep it in the same transaction? Either a) you have it in the same transaction, whereby the user must be aware of it and the entire process must wait for completionb) You can have it working in the background but you will not know the outcome until it has completed and so have to check for it.If b) you need a process to run it, deal with errors, retries etc, notify the user that things have finished/not gone to plan. There is no magic solution here - you are going to have to decide what you want to do and put some analysis and hard work in.
this wont work when you need real time transactions that the user needs to know the results asp on the frontend.We are looking at implementing jobs when transactions are high. I am searching for articles on jobs. Books online seem to be getting less user friendly now :(_____________________Yes O ! |
|
|
|
|
|
|
|
|
Question about triggers, SP and resource usage