Solved

how to do this

Posted on 2013-02-07
13
162 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 33

Expert Comment

by:paulmacd
Comment Utility
There's no better syntax.  Run it on your fastest server is all I can say.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:wasabi3689
Comment Utility
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
Comment Utility
>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
Comment Utility
My original question is

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

Expert Comment

by:Aneesh Retnakaran
Comment Utility
>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
Comment Utility
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
Comment Utility
no where clause in update statement
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 250 total points
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

771 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

11 Experts available now in Live!

Get 1:1 Help Now