April 3, 2003 at 3:31 am
Anyone have any comments on which is better to use.
The scenario in question is as follows, I have tables let's call them Foo1 and Foo2 and I want to insert Foo entries into the database. Now I want a single interface to the database, so I have chosen to use stored procedures to do all the insertions for me. Now in the stored procedure I do some checks on the data and then either insert into Foo1 or Foo2 and bubble through any database errors incurred.
Now my question is as follows, would it be faster to use INSTEAD OF triggers on either a view or even Foo1 directly that can implement the logic and redirect to Foo2? It does have an up-side as it would allow for bulk-insert/update operations to be performed and the logic would still be executed.
Anyone have any thoughts on this?
April 3, 2003 at 5:57 am
I would let the stored procedure to do the logic instead of the trigger.
April 3, 2003 at 6:14 am
For what reasons? Please justify your opinion with at least some form of logic. 🙂
April 3, 2003 at 7:27 am
Well the stored procedure is compiled on creation and the execution plan is saved in memory so usually run faster than individual sentences.
But will aso depende of how you create the stored procedure. The trigger should do the checking once for every row inserted, and if you do a massive insert, then it could take some time, and even if you us a cursor in the stored procedure to check the data, I also think that it could be better. Anyway, if I were you, I would create both, and do some testing to really know wich one is better.
April 3, 2003 at 7:29 am
I think you've got a good case for the instead of trigger. Whether on the table or a view would depend on the frequency - if 10% of the time it goes to the 2nd table, why incur the overhead (though it's not a lot I think) to check and process all inserts? Possibly you could do a combo of the two, put the trigger on the view and use for bulk, put the logic in the proc and hit the tables directly. Downside is you dupe your logic.
The overriding factor for me is if you have a need to do bulk loads that you don't want to run through the proc.
Andy
April 3, 2003 at 8:49 am
Thanks racosta & Andy for your feedback.
Now I am considering the possiblity of using both approaches. Firstly, my logic is already in a stored procedure. Does anything prevent me creating the cursor in the INSTEAD OF trigger and simply invoking the stored procedure to do the actual insertions?
Any ideas on that?
It allows for bulk-loads as well as for direct stored procedure calls. The question that remains is whether inserting a single row into a view or table with an INSTEAD OF trigger defined calling a stored procedure, is going to be seriously slower than just calling the stored procedure directly.
All thoughts welcome... 🙂
April 7, 2003 at 10:42 pm
Also, as someone who spends considerable time debugging and trouble shooting other peoples DB's, complex triggers can be pretty tough to debug. I usually try to use triggers only when I have to, and when I do I try to keep them as simple as possible.
April 10, 2003 at 7:20 am
Since all thoughts are welcome:
Why don't you put the business logic in a programming object? Whether it is an MTS object or a class module in a client server app, business rules are best placed there. They can be made more modular than stored procedures, and, because for almost all situations the database is the bottleneck, you want to reduce the processing time that takes place here.
With few exceptions, we use stored procedures for data retrieval and data manipulation of data that has already had the business rules applied to it in the business object.
As far as stored procs vs triggers, we use triggers only for auditing or to maintain low level data integrity that can't be maintained through the standard constraints. For example, a few of our work functions require equipment. As items are added and removed from the table that tracks the required equipment, a trigger activates or deactivates the flag in the work table so the business object knows to check for required equipment. Another example, a resolved entry in the problem detail table triggers a closed date entry in the problem main table. Each of these cases represents an integrity issue (eg it would be inconsistent to have a piece of required equipment for a work function without having it so marked; it would also be inconsistent to have a resolved entry in the detail without a final resolution date in the main).
Each of these cases represents a conscious denormalization we've made for one reason or another, usually performance. As a trade-off for this, we use triggers to ensure that the data does not get out of sync.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply