Solved

how to do this

Posted on 2013-02-07
13
167 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Free eBook: Backup on AWS

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

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 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