Solved

how to do this

Posted on 2013-02-07
13
163 Views
Last Modified: 2013-02-11
I have a simple update statement

update table_name set col1= 0 col2=0

This update statement will update millions reports. It takes me hours to complete. Do you know a better and faster way to do this? please advice.
0
Comment
Question by:wasabi3689
13 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38864559
There's no better syntax.  Run it on your fastest server is all I can say.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38864570
there:
SET ROWCOUNT 100000

begin tran
update table_name
set col1 = 0, col2 = 0
where col1<> 0 and  col2 <> 0
commit tran
Waitfor delay '00:00:05'

goto there;
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38864581
>Do you know a better and faster way to do this
Per above, not via T-SQL.

A couple of other possibilities, if you have privs to do so:
*  Investigate if there are any update triggers on this table that would fire with each row update.
*  Investigate the indexes on this table, as if the table does a lot of frequent updating then more indexes would mean more overhead.
0
 

Author Comment

by:wasabi3689
ID: 38864590
hi aneeshattingal

the table has million records to update. You "SET ROWCOUNT 100000". Do you mean update every 100000 at a time til to the end?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38864619
>the table has million records to update. You "SET ROWCOUNT 100000".
Yup, thats correct; you can try a lower value if you find lot of locking; but on one of my servers I did this to reduce the impact of the lock escallation; that may take a while to excute but wont lock the tables;
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 38864631
You need to put one more condition

there:
SET ROWCOUNT 100000

begin tran
update table_name
set col1 = 0, col2 = 0
where col1<> 0 and  col2 <> 0
If @@ROWCOUNT  = 0
  Go to end1;
commit tran
Waitfor delay '00:00:05'

goto there;

End1:
 Commit tran
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:wasabi3689
ID: 38866021
Hi aneeshattingal,

I tried your code to update 5 million records, it still takes more than 1 hour to complete.  Any advice to make it faster too??
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38866065
>I tried your code to update 5 million records, it still takes more than 1 hour to complete.  Any advice to make it faster too??

I didnt say it will be a faster ; one think thats guaranteed is there wont be any locking issues;

Aneesh
0
 

Author Comment

by:wasabi3689
ID: 38866214
My original question is

how to make it faster to update a million records table?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38866233
>how to make it faster to update a million records table?
Disable all the indexes on that table, run the update and enable the indexes;
do this during a maintennace window because its gonna create a lot of blocking issues (timeouts at the front end); and will create a huge log;
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38866346
how to make it faster to update a million records table?
I am afraid given the preexisting conditions you can't.
0
 

Author Comment

by:wasabi3689
ID: 38866396
no where clause in update statement
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 250 total points
ID: 38871787
You are updating col1 and col2 to zero which means, only those records which has a non-zero value in either col1 or col2 need to be updated.
You may prefer not to use a WHERE clause only if you think that every time you run this query, all/most of the rows will have non-zero value in either of these columns and will have to be updated.

But if you want to run this query periodically and there will be very few rows which will have non-zero value in either of these columns (since most of the rows would have been updated when you executed it before), then you need to think about using indexes.

Since you have two columns to be updated when one of them have non-zero values, you will have to use WHERE Col1 <> 0 or Col2 <> 0.
The "or" condition and "<>" operators will make the index on these columns useless.

In that case, one option may be to create a new NOT NULL column "updateflag" and set it as '0' by default. Any new record in this table should get a value 0.
And when you update Col1 and Col2 to 0, update "updateflag" to 1.
Update table_name set Col1 = 0, Col2 = 0, UpdateFlag = 1 
WHERE UpdateFlag = 0

Open in new window

And create a non clustered index on "updateflag" column which will improve the performance to a great extent. If you are using SQL Server 2008, you can also use FilteredIndex to minimize the index cost and to improve the performance even more.
set quoted_identifier on
create index idx_tablename_updateflag on tablename (updateflag) 
Where updateflag = 0

Open in new window

Please provide actual business need if you find this solution is not feasible so that we can think about alternative approach.

-Harish
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now