SQL Server Performance Forum – Threads Archive
Help me to fine tune the sql query in Trigger
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_NO2.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.
Do you have an index on the CAGE_LOG_SEQ_NUM column of the CORP_CAGE table?
yes.we have primary key constraint on CAGE_LOG_SEQ_NUM colum. so this column will have clustered index. please give me suggestion to replace this query by other way.
Try updating in smaller batches, perhaps 1,000 rows at a time.
Adriaan,
Can you please tell me briefly how to execute these in batches in trigger. we can’t set trigger to execute in batch
SET ROWCOUNT 1000 UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111 WHILE @@ROWCOUNT > 0
BEGIN
UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111
END SET ROWCOUNT 0
… the trigger will always cover all records affected – it’s the update query that needs to run in batches.
And is the EMP_SEQ_NO column in CORP_CAGE covered by any indexes?
Adriaan,
Thank you so much.i will try this concept. in the above code,you set rowcount=1000, it will update only 1000 records, how should i update another set of record.for example, i am updating 2000 record by batch,i can able to update only 1000 record all the time and can’t update next 1000 record. what should i do?
Check the WHILE loop immediately after it. The condition for the loop is that at least 1 row has been affected by the last statement that was executed (@@ROWCOUNT > 0) – if so, then the section between BEGIN and END is repeated. After the looped query has updated the last batch of rows where EMP_SEQ_NO was 111, the loop will once more try to update rows with the same criteria, but no rows will be affected, and @@ROWCOUNT will then be zero. This ends the loop. After the loop, rowcount is reset to 0 (unrestricted). This is necessary in case you issue any query statement in this trigger after this point, but other than that you should always clean up after yourself, even if it is not strictly necessary.
Adriaan,
I slightly confused,can you please send the code now. i have to update all record say 3000 by batch.
Huh? Just replace the one-line UPDATE query that you already have, with the script that I posted today at 04:16:47.
… and leave the trigger as it is.
]]>