April 14, 2011 at 3:13 am
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
April 14, 2011 at 3:20 am
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
April 14, 2011 at 9:26 am
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.
April 15, 2011 at 12:30 am
+ 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?)
April 20, 2011 at 7:39 am
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