SQL Server Performance

Help me to fine tune the sql query in Trigger

Discussion in 'T-SQL Performance Tuning for Developers' started by iganesh2k2, Jun 19, 2007.

  1. iganesh2k2 New Member

    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.
  2. Adriaan New Member

    Do you have an index on the CAGE_LOG_SEQ_NUM column of the CORP_CAGE table?
  3. iganesh2k2 New Member

    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.
  4. Adriaan New Member

    Try updating in smaller batches, perhaps 1,000 rows at a time.
  5. iganesh2k2 New Member

    Adriaan,
    Can you please tell me briefly how to execute these in batches in trigger. we can't set trigger to execute in batch
  6. Adriaan New Member

    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
  7. Adriaan New Member

    ... the trigger will always cover all records affected - it's the update query that needs to run in batches.
  8. Adriaan New Member

    And is the EMP_SEQ_NO column in CORP_CAGE covered by any indexes?
  9. iganesh2k2 New Member

    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?
  10. Adriaan New Member

    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.
  11. iganesh2k2 New Member

    Adriaan,
    I slightly confused,can you please send the code now. i have to update all record say 3000 by batch.
  12. Adriaan New Member

    Huh?

    Just replace the one-line UPDATE query that you already have, with the script that I posted today at 04:16:47.
  13. Adriaan New Member

    ... and leave the trigger as it is.

Share This Page