April 19, 2011 at 12:41 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. 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
April 19, 2011 at 1:04 am
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')
April 19, 2011 at 1:51 am
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
April 19, 2011 at 5:18 am
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
April 19, 2011 at 9:08 am
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