Speeding up the Execution of Triggers.

Question

Speeding up the Execution of Triggers.

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

At first blush, it would seem that the database is not normalized. If it were, you would not need to store non-key fields in two tables. That's the ultimate solution, which is not feasible for the short term.

Question regarding business rules - IF you always enforce the content for all the different fields to match what is in the primary table, why don't you simply force ALL the content to be updated each time, regardless whether it changed?

Question regarding volumes - without trying to be nosy about the business being supported (convention registrations? memberships?), how is it that you have such a high volume of records to update - a 3000:1 ratio of parent/child records?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

First, be aware that "IF UPDATE(column)" is true when the column is being set and not that the value of the column is changed.

With this SQL, which changes no values:

Update Foo set fooColumn = fooColumn

IF UPDATE(fooColumn) will return true.

Try this as an alternative:

CREATE TRIGGER tU_MCParty ON MCParty FOR UPDATE

AS

-- Begin tran not need because triggers always run in a transaction.

set nocount on

set xact_abort on

-- when zero rows are affected, exit

IF 0 = (select count(*) from inserted) return

UPDATE DCParty

SET DCParty.ParticipationPercent = inserted.ParticipationPercent

,DCParty.MPFBankInd = inserted.ParticipationTypeInd

,DCParty.LastUpdatedDate = inserted.LastUpdatedDate

,DCParty.LastUpdatedTime = inserted.LastUpdatedTime

,DCParty.LastUpdatedComments = inserted.LastUpdatedComments

,DCParty.LastUpdatedBy = inserted.LastUpdatedBy

,DCParty.OrgKey = inserted.OrgKEY

,DCParty.ContactKey = inserted.ContactKey

FROM inserted

joindeleted

on inserted.MCPartyKEY= deleted.MCPartyKEY

joinDeliveryCommitment

on DeliveryCommitment.MasterCommitmentKEY = inserted.MasterCommitmentKEY

whereRoleKEY is NOT NULL

ANDDCParty.DeliveryCommitmentKEY = DeliveryCommitment.DeliveryCommitmentKEY

AND DCParty.RoleKEY = inserted.RoleKEY

-- any column has changed values

AND( inserted.ParticipationTypeInd deleted.ParticipationTypeInd

OR inserted.LastUpdatedDate deleted.LastUpdatedDate

OR inserted.LastUpdatedTime deleted.LastUpdatedTime

OR inserted.LastUpdatedComments deleted.LastUpdatedComments

OR inserted.LastUpdatedBy deleted.LastUpdatedBy

OR inserted.OrgKEY deleted.OrgKEY

OR inserted.ContactKey deleted.ContactKey

)

go

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

The other thing about Carl's trigger, is that it will actually update more than 1 row in the DCParty table whereas the original trigger would only update one row in the DCParty table no matter how many rows were updated in the MCParty table.

There's a way to speed up even Carl's trigger, a bit... instead of doing this...

CREATE TRIGGER tU_MCParty ON MCParty FOR UPDATE

AS

-- Begin tran not need because triggers always run in a transaction.

set nocount on

set xact_abort on

-- when zero rows are affected, exit

IF 0 = (select count(*) from inserted) return

... you can do this...

CREATE TRIGGER tU_MCParty ON MCParty FOR UPDATE

AS

-- Begin tran not need because triggers always run in a transaction.

set nocount on

set xact_abort on

-- when zero rows are affected, exit

IF @@ROWCOUNT = 0 return

Yes, Virginia, there is a Santa Clause

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sharan-465448 Valued Member Points: 71 More actions · Answer 4

Thanks for your help guys..

This did not help me. The timeout error still persists.

Is ther any other way to enhance the performance of the execution of the Triggers.

- thanks

Sharan

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Please post the execution plan. And the table DDL if that has not been posted yet.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

I believe you did not even try my suggestion.

You cannot achieve anything while you stick with loops.

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

That is likely, however maybe the problem was much bigger than we anticipated.