Update Statement Working very slow

  • Dear Sir,

    I am in great trouble, I have database which size is 30 GB. around 500 tables, on of them is TRFILE table. which contains above 2.5 millions records. the table has clustered index and non clustered indexes.

    I just want to update the entire table with below statement.

    USE PC1011

    GO

    UPDATE TRFILE

    SET BRANCHID='HO', SUBCD='CS00000001' WHERE CO='01' AND BRANCHID<>'CLNT'

    Here this query update record 2.00 million record. But When I execute the query It taking 10 hrs. still did not update.

    Now My machine Configuration is

    IBM XEONG SERVER

    Intel original mother board

    16 GB Ram

    2x2 dual Quare Processor(4 prodessor)

    2.6 GHZ Processor Speed

    1000 GB Hard Disk.

    SQL SERVER CONFIGURATION.

    SQL SERVER 2005

    ENTERPRISED EDITION

    SERVICE PACK 2

    DATABASE IN SIMPLE RECOVERY MODEL

    UPDATE STATISTIC AND REINDEX HAVE DONE

    TRFILE TABLE CONTAIN TRIGGER.

    Still this query too slow .but why it's slow please guide me.

    Thanking You

    Debasis Bag

  • Clustered index and non clustered indexes? On what? Please post table and index DDL so that we can help you. Is this update a one-off, or something that is done regularly?

    John

  • Part of the problem is the <>, which can make you scan the entire table. Plus this can be a large commit, lots of log space required, and more. Typically if you can, I would batch this in groups of 1000-10000 for the update.

  • + the trigger

    What is the trigger doing?

    Is it also updating something?

    (you mentioned that the table contains trigger.

    Is it fired on/after update?)

  • If you need to stay online while this is going on, you must batch the updates into smaller groups of say 1000 to 5000 records at a time using an index seek plan.

    Triggers can be a big problem here.

    Foreign keys can be a big problem here, especially if they are unindexed.

    Tlog growth can be big problem here.

    LOTS of other potential issues.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

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