April 2, 2014 at 7:17 am
how long will a update statement will take to inish its job on some 5 million records.
I am having table with 5 million records, i addeded a column with int datatype and updating 0 on that column
i tried
Update table_name set newcolumn_name=0
took 1 hour
tried again with updlock
update table_name with (updlock) set newcolumn_name=0
took 50min
the table is refered by fkey but not the new column,fkey is already enabled.
why it is taking more time to update?
noother statement or functions are running during that time. only this query is running, some time CPU goes for 100%.
why it is so?
i am not updating on batchwise.
April 2, 2014 at 8:36 am
Your answer starts here. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
You need to post details with your questions instead of vague scenarios with no information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2014 at 8:47 am
The other part of this is that you are trying to update 5 million rows of data at once. Remember that SQL Server needs to write all the changes to the transaction log before it can commit the changes to the databases. That means it needs to record the current state of the data prior to updating the column in 5 million rows and then the new state of the data in those 5 million rows of data. This takes time.
April 2, 2014 at 10:34 am
Lynn Pettis (4/2/2014)
The other part of this is that you are trying to update 5 million rows of data at once. Remember that SQL Server needs to write all the changes to the transaction log before it can commit the changes to the databases. That means it needs to record the current state of the data prior to updating the column in 5 million rows and then the new state of the data in those 5 million rows of data. This takes time.
Potentially it also grows the log because it needs space for 10mm changes. 5mm to update and 5mm for a rollback if it's needed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply