Solved

how to do this

Posted on 2013-02-07
13
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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
 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

729 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