i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in
CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.
please find the structure of CORP_CAGE table:
1.CORP_CAGE_SEQ_NO
2.RECEIVED_DATE
3.EMP_SEQ_NO
CORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following
query from application which updates 10,000 records.
UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111
please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using
UPDATE FUNCTION.
But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum
records,this trigger is working fine.
Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS
BEGIN
IF UPDATE(EMP_SEQ_NO)
BEGIN
INSERT CORP_CAGE_LOG_HIST
(
CAGE_LOG_SEQ_NUM,
BEFORE_VALUE,
AFTER_VALUE,
ENTRY_USER,
FIELD_UPDATED
)
SELECT
i.CAGE_LOG_SEQ_NUM,
d.RECEIVED_DATE,
i.RECEIVED_DATE,
i.UPDATE_USER,
"EMP_SEQ_NO"
FROM
inserted i,
deleted d
WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
END
END
please help me on this for performance tuning the below query.
I don't have the schema of your table, which in this case is critical. However, if this statement:
Code Snippet
UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111
is updating 10,000 records then your join is going to cause an update that is the cross product of 10,000 x 10,000 or 100,000,000 logical records. This cannot be right. Look at your trigger WHERE condition:
Code Snippet
WHEREi.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
since you are updating ONLY for SEQ_NO = 111 and you are joining the INSERTED pseudo table -- with 10,000 records -- with the DELETE pseudo table -- also with 10,000 records -- and since all records of the DELETED pseudo and all records of the DELETED pseudo have the same SEQ_NO -- specifically 111 you end up with the cross product. You need to linclude the KEY information as part of the join condition.
sql