UPDATING QUERY VERY SLOW IN SQL SERVER 2005

  • 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. THE TRIGGER UPDATING ANOTHER TABLE 'TRFILE_LOG' WHEN UPDATING TRFILE TABLE'S EVERY ROW

    THE TRFILE CONTAIN 1 CLUSTERED INDEX AND 10 NON CLUSTERED INDEX AND FOREIGN KEY

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

    Thanking You

    Debasis Bag

  • Just Try This

    UPDATE TRFILE

    SET branchid = 'HO',

    subcd = 'CS00000001'

    WHERE co = '01'

    AND NOT EXISTS (SELECT 'X'

    FROM TRFILE TF

    WHERE tf.co = TRFILE.co

    AND tf.branchid = 'CLNT')

  • Have you checked for blocking?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The first thing I would do is see what a select statement with this as the WHERE clause looks like:

    WHERE CO='01' AND BRANCHID<>'CLNT'

    What kind of execution plan are you getting? Is it scans or seeks? If it's scans, there's your problem.

    Further, you have a fairly large number of indexes on that table. Are the values that you're updating part of the clustered index? If so, when you update the cluster, you're also updating all the non-clustered indexes. That could be a major piece of the puzzle too.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Please don't keep posting the same question over and over. It fragments the responses and makes it harder for everyone.

    See the original thread here

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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