| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2006-03-21 : 06:40:33
|
| i have some code that i have added to some of my UPDATE SProcs. But they significantly slow down the save time for the user. If i put this logic in a trigger instead, will that run on a seperate process and thus free up the update statement to return executing quicker? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-03-21 : 07:30:49
|
| Triggers are fired within the same process (SPID) so your end users wount see any performance gain. What you also need to think about is that a trigger fires every single time a table is updated/inserted/deleted (depending on the type of trigger) and this might cause alot more overhead than what you are looking for. Personally I try to avoid using triggers as long as I can...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-03-21 : 07:59:53
|
| same here, i'll see if i can do something else i think. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-03-21 : 11:22:20
|
| As Lumbago said, Its best to keep you logic in your SP. Maybe you just might have a bug somewhere in the code. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-21 : 12:07:53
|
| Depends on the logic. If it is related to data-integrity or relational integrity, then you should keep it as close to the data as possible, and that means triggers.Have you reviewed your code to make sure it is efficient to start with? Ineffecient code is not going to run quickly wherever you put it. Post it to the thread if you would like one of us to review it for you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-21 : 13:41:48
|
| If you need to "modify" the row being saved the Trigger will, in effect, cause the row to be updated twice which, IME, will be slower.So .. the row will be updated by the SProc, the [AFTER] trigger will fire, and then re-save the same row(s).But if data could get into the data bypassing the trigger (bulk load, sloppy developer code, etc.) then triggers are "safer".Look at optimising the SProc first I reckon ...Kristen |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-03-21 : 14:31:18
|
| If certain changes are made, I need to update another with that change. The query is optimized. But I may be able to add an index or two and see if the optimizer picks them. |
|
|
|
DBASlut
Yak Posting Veteran
71 Posts |
Posted - 2006-03-21 : 19:18:04
|
| I would avoid triggers unless it for audit trails on a row by row level. My biggest concern is maintainablity. What if you had to do the same thing to another table? Would you add another trigger there? If so now you have to go to 2 places to modify code if the logic changes. If its in a proc, then you go to the one proc and change it. This way you force the developers to go through a proc so there aren't any embedded SQL in the app. IMHO I'd avoid triggers and stick with procs for better maintainablity. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-21 : 20:17:42
|
quote:
Originally posted by DBASlut
What if you had to do the same thing to another table? Would you add another trigger there? If so now you have to go to 2 places to modify code if the logic changes. If its in a proc, then you go to the one proc and change it.
You have only one sproc in your application? Wow. I guess you don't have to worry about redundant code then, but it must be hellaciously long. |
|
|
|
|
triggers faster than sprocs?