Do triggers decreases the performance? Inserted and deleted tables?

Question

Suppose i am having stored procedures which performs Insert/update/delete operations on table.

Depending upon some criteria i want to perform some operations.

Should i create trigger or do the operation in stored procedure itself.

Does using the triggers decreases the performance?

Does these two tables viz Inserted and deleted exists(persistent) or are created dynamically?

If they are created dynamically does it have performance issue.

If they are persistent tables then where are they?

Also if they exixts then can i access Inserted and Deleted tables in stored procedures?

Solution 1

Will it be less performant than doing the same thing in a stored proc. Probably not but with all performance questions the only way to really know is to test both approaches with a realistic data set (if you have a 2,000,000 record table don't test with a table with 100 records!)

That said, the choice between a trigger and another method depends entirely on the need for the action in question to happen no matter how the data is updated, deleted, or inserted. If this is a business rule that must always happen no matter what, a trigger is the best place for it or you will eventually have data integrity problems. Data in databases is frequently changed from sources other than the GUI.

When writing a trigger though there are several things you should be aware of. First, the trigger fires once for each batch, so whether you inserted one record or 100,000 records the trigger only fires once. You cannot assume ever that only one record will be affected. Nor can you assume that it will always only be a small record set. This is why it is critical to write all triggers as if you are going to insert, update or delete a million rows. That means set-based logic and no cursors or while loops if at all possible. Do not take a stored proc written to handle one record and call it in a cursor in a trigger.

Also do not send emails from a cursor, you do not want to stop all inserts, updates, or deletes if the email server is down.

Solution 2

Yes, a table with a trigger will not perform as well as it would without it. Logic dictates that doing something is more expensive than doing nothing.

I think your question would be more meaningful if you asked in terms of whether it is more performant than some other approach that you haven't specified.

Ultimately, I'd select the tool that is most appropriate for the job and only worry about performance if there is a problem, not before you have even implemented a solution.

Inserted and deleted tables are available within the trigger, so calling them from stored procedures is a no-go.

Solution 3

It decreases performance on the query by definition: the query is then doing something it otherwise wasn't going to do.

The other way to look at it is this: if you were going to manually be doing whatever the trigger is doing anyway then they increase performance by saving a round trip.

Take it a step further: that advantage disappears if you use a stored procedure and you're running within one server roundtrip anyway.

So it depends on how you look at it.

Comments

[Matt Wrock]

I think the question is referring to the presence of the inserted and deleted tables. Is there a performance hit by having those structures available. My gut says no, but I'm not sure.

[Panache]

You got me right i want to know "whether it is more performant than any other approach" I want to know that if Inserted and deleted tables can only be accessed in triggers then are they created when the triggers are fired or are they permanent tables? and if they are permanent tables then why cant we access them in stored procedures

[Cory]

INSERTED and DELETED are temporary tables that live in memory for the duration that your trigger is executing. Once your trigger has finished executing they are gone until something else causes the trigger to fire

[Luke T O'Brien]

Sometimes thinking about performance before getting into a solution is a good thing, there's nothing worse then going so far into a project only to realize that you have to start again - Performance is something to consider in choosing the right solution before you have implemented the wrong solution