April 5, 2002 at 4:51 am
Hi All,
What are the performance benefits of using SP's instead of Triggers.
Do these still hold when the server application
can generate dynamic sql via a ODBC Wire Protocal ?
http://www.datadirect-technologies.com/odbc/connectodbc/opxconnectodbcwire.asp
[Helpfull article ?:
Optimizing Database Performance
Effects of Stored Procedures on Application Performance
(Microsoft TechNet)]
April 5, 2002 at 5:31 am
The major advantage of SP's is their ability to use stored execution plans to quickly make logic decisions when access the database and all you have to do is grants execution prviledges to the SP to the user and no table level rights need be given (as long as you are not using Dynamic SQL and all objects have the same owner preferable dbo).
Triggers however can affect any insert, delete, or update directly to the table via stored procedure or otherwise whereas SP's have to be programmed to handle the insert all at one time with any code that would be in your trigger.
The ODBC wireless provider should be able to take advantage of either situation SP or direct table access but again I would opt for SP to protect from direct access to tables.
Note you can also use SPs for inserts and such with a trigger on the table to do whatever is required at the table level.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 6:15 am
Hi,
I only see two problems here:
1) Triggers also cache the execution plan on SQL 2000-
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_7cmm.asp
2) Preventing direct data-manipulation does not result in a performance benefit.
What are the direct comparision result when trying to do the same thing with a trigger or vs a SP, with small / large record sets ?
Does the holding of "inserted"/"deleted"/"updated" tables cause noticable negative performance ?
Within the SP there is the advantage of being able to perform the data-validation before performing the actual updates to various table structures as single last statements of the SP (which can be grouped together within a single transaction).
Does anybody know of / have any evidence of this ?
Regards,
dan.
April 5, 2002 at 8:49 am
Triggers should always be lightweight and bulletproof. The only problem with calling a stored proc from a trigger is that you can't pass the inserted/deleted tables as a parameter, forcing you to use a cursor type operation to call the proc once for each row affected. An alternative is to have a flag set on the row which the stored proc clears when it runs - allowing you to run it from a job/asynchronously.
Andy
April 5, 2002 at 9:00 am
Hi All,
Triggers-> Should be light and bulletproof -> But at what point is it better to encapsulate the business logic/process logic into the SP for performance reasons.
Info: The only problem with calling a stored proc from a trigger is that you can't pass the inserted/deleted tables as a parameter, forcing you to use a cursor type operation to call the proc once for each row affected->
There are alteratives:
[Multiple Records -> They have to come from somewhere, usefull to have file-base entry
and use Bulk Insert or something else giving a SPBatchInsert / Delte/Update SP's]
Single Record Data-Entrys: Compare Data-In with Current-Data then perform update
..Does not require a cursor as most web-apps are single record entry/updates.
Are there any performance guidelines on this anywhere ?
Regards,
Dan.
April 30, 2002 at 8:21 am
continue this thread
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 30, 2002 at 10:09 am
It seems like you are looking for whether it's better to put logic in a trigger or place it in a stored procedure. I'd tend to argue there is no real difference. There is a slight difference in the overhead of instantiating the stored procedure and holding the isnerted deleted tables while the stored procedure executes.
In light of this, I'd argue that you should place the logic in the trigger "UNLESS" you can use the same logic elsewhere. Like having an app call the stored procedure in some case. Then it would be nice to keep only one copy of the business logic.
Steve Jones
April 30, 2002 at 10:57 am
Is there a difference between SQL7 and SQL2000 regarding discussed matter?
April 30, 2002 at 11:51 am
SQL 2000 has more trigger options, but I'm not sure there is any other difference.
Steve Jones
April 30, 2002 at 7:41 pm
Also 2000 SP and Triggers are actually compiled code. It was that way in 6.x but not in 7. When I say compiled it is like create a VB executable in that it is moved to a native format when stored. But this has nothing to do with the recompile option in that it does not drop the stored execution plan.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply