slow update query

  • I recently tried to run a update statemene with joins on a table that has 200,000 records. I suppose it should be very quick.

    But it turned out it ran30 minutes, and still ran. I canceled it, thinking it might be something wrong. I tried to put the joins in a temp table and then updated with 1 join. but still takes long time.

    By clicking through the object , I finally found there is a trigger for the table, once there is a change it adds records into other table.

    Any other way to troubleshoot the slow query and found the trigger without knowing there is a trigger to the table?

    thanks

  • My guess would be that the log file needed to grow dynamically, and that is very slow.  That partly would depend on the specific UPDATEs that were done and how wide the data involved was.

    But, no matter the size, pre-allocating sufficient log file space will make it run much faster if the current log file doesn't have enough empty space in it.  Log file space must be pre-formatted, which slows down those allocations.  You can shrink the log back down afterward if you prefer.

    Also, verify that the other trigger is very efficiently written.  Really well-written triggers should be no problem, but poorly written ones can be deadly to performance.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • sqlfriend wrote:

    I recently tried to run a update statemene with joins on a table that has 200,000 records. I suppose it should be very quick.

    But it turned out it ran30 minutes, and still ran. I canceled it, thinking it might be something wrong. I tried to put the joins in a temp table and then updated with 1 join. but still takes long time. By clicking through the object , I finally found there is a trigger for the table, once there is a change it adds records into other table.

    Any other way to troubleshoot the slow query and found the trigger without knowing there is a trigger to the table?

    thanks

    Post the code, please.  There's a form of joined-Update that I simply refer to as an "illegal update".  I've fixed it in the past for folks where it slammed a handful of CPU's into the wall and took hours to run.  After repairing the "illegal update", it ran in seconds and barely showed up on monitoring systems for CPU, Read, and Writes.

    --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)
    Intro to Tally Tables and Functions

  • Yes,  I did check the log space, I see there is plenty space while it is running.

    This is a non-production environment for me to scramble data.

    So I was able to disable the trigger, then do the update, it takes only 1 second. then I re-enable the trigger.

    What I meant is how to find out when I ran the update query what makes it slow.

    My guess is to use SQL profiler or extended event to monitor what SQL it executes to trace to the trigger.

  • Also the trigger is to add logs to another table. This is usually used when user change address from user interface web site, so it is usually one record one user at that time. so not problem. But for batch loading into the table, the trigger really slow down the process , so need to disable.

    • This reply was modified 7 months, 3 weeks ago by  sqlfriend.
  • sqlfriend wrote:

    Also the trigger is to add logs to another table. This is usually used when user change address from user interface web site, so it is usually one record one user at that time. so not problem. But for match loading into the table, the trigger really slow down the process , so need to disable.

    So it seems that the trouble is in the trigger.  Either post the code for the trigger and maybe the DDL for the table or the only way we can help is to say "learn the proper way to write a trigger".  And STOP disabling triggers in production... 😉  They're there for a reason!

     

    --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)
    Intro to Tally Tables and Functions

  • table and script trigger attached.

    Thanks

  • sqlfriend wrote:

    table and script trigger attached.

    Thanks

    Where?  I see no attachments anywhere on this thread,

    --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)
    Intro to Tally Tables and Functions

  • sorry, the extension sql is not allowed, changed to .txt, attached

    Attachments:
    You must be logged in to view attached files.
  • if I got it correct the following likely to perform better - do look at the comment inside it - and as with everything test test and test again.

    alter  TRIGGER [dbo].[trg_stuAddressChangeLog] ON [dbo].[stuAddress]
    FOR UPDATE
    AS
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM deleted) AND EXISTS (SELECT 1 FROM inserted)

    BEGIN

    INSERT INTO dbo.stuAddressChangeLog
    ( StudentID ,
    AddressID ,
    ApartmentNum ,
    StartDate,
    EndDate,
    CreateDt ,
    CreatedBy

    )
    SELECT del.StudentID ,
    del.AddressID ,
    del.ApartmentNum ,
    COALESCE(del.ChangeDt,del.CreateDt),
    SYSDATETIME(),
    SYSDATETIME(),
    COALESCE(del.ChangedBy,del.CreatedBy)
    from deleted del
    --inner hash join inserted ins -- hash join may perform better for high volumnes - use it only after extensive testing and with high volumes of a single update transaction
    inner join inserted ins
    on ins.studentid = del.studentid
    and (ins.addressid <> del.addressid
    or ISNULL(ins.ApartmentNum,'') <> ISNULL(del.ApartmentNum,'')
    )

    -- FROM deleted del
    -- WHERE NOT EXISTS
    --(SELECT *
    --FROM inserted AS ins
    --JOIN deleted AS del
    --ON ins.studentid=del.studentid AND ins.addressid=del.addressid
    --AND ISNULL(ins.ApartmentNum,'')=ISNULL(del.ApartmentNum,'')

    --)

    END


    GO

    ALTER TABLE [dbo].[stuAddress] ENABLE TRIGGER [trg_stuAddressChangeLog]
    GO
  • yes, the performance are better if I test small batches of updating.

Viewing 11 posts - 1 through 11 (of 11 total)

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