Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

how to do this

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
wasabi3689
Asked:
wasabi3689
2 Solutions
 
Paul MacDonaldDirector, Information SystemsCommented:
There's no better syntax.  Run it on your fastest server is all I can say.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
wasabi3689Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
wasabi3689Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
wasabi3689Author Commented:
My original question is

how to make it faster to update a million records table?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
Anthony PerkinsCommented:
how to make it faster to update a million records table?
I am afraid given the preexisting conditions you can't.
0
 
wasabi3689Author Commented:
no where clause in update statement
0
 
Harish VargheseProject LeaderCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now