March 23, 2004 at 10:21 am
I have a table with about 7 million records, and I need to do a fairly simple UPDATE command which will affect the majority of the rows on the table.
Currently this command is taking well over an hour to execute.
This may be exacerbated by the fact the the table is being replicated (push transactional) to another server, but even on my test server, with no replication, it's still taking a long time to execute.
Is there anything I can do to speed this up?
March 23, 2004 at 10:34 am
Use the same technique as this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=107483
More about it here:
http://www.sqlservercentral.com/columnists/sjones/batching.asp
March 23, 2004 at 3:39 pm
Hi Steve,
Thanks for the suggestion. I tried it out on my test database, which only has 1.8 million records on the relevant table, with the following results:
A straightforward
UPDATE table1 SET field1 = 0 WHERE field1 = 1
took 13 minutes. I then did a "batched" versions along the lines of the article you suggested, as follows:
DECLARE @x INT
SET @x = 1
SET ROWCOUNT 5000
WHILE @x > 0
BEGIN
BEGIN TRAN
update table1 set field1 = 0 where field1 = 1
SET @x = @@rowcount
COMMIT TRAN
WAITFOR DELAY '00:00:01'
END
This took 33 minutes!!
I also tried it with different batch sizes, from 100 to 10000, but none of these were any quicker than the 13 minutes for the straight version.
Of course, I might get better results on the live server, with 7 million records rather than 1.8 million - maybe this technique only bears dividends above a certain threshold? But if so, it would need to be a dramatic difference.
Or is there something else I've missed? Surely there must be a quick and efficient way of doing a simple global update to all records on a large table?
Mike.
March 23, 2004 at 3:47 pm
Is field1 indexed? And is it used often? Finally run it once normal but run
SET SHOWPLAN_TEXT ON
GO
Before it and post the execution plan here so we can take a look.
March 24, 2004 at 6:17 am
The WAITFOR DELAY '00:00:01' statement waits for 1 second. For 1.8 million rows with a batch size of 5000, that's 6 minutes of waiting. 1 second seems like a long time to wait for just 5000 rows. I guess you want to wait a bit to permit all of the changes to be comitted. You could experiment with shorter delays. Maybe WAITFOR DELAY '00:00:0.2' (two-tenths of a second) might work better.
Mike
March 24, 2004 at 6:48 am
As dumb as it sounds, something that has worked for me in the past is to preceed the update with a select statement having the same predicates as the update:
select col_list from table_a where predicate_list
go
update table_a set whatever where predicate_list
March 24, 2004 at 10:43 am
Hi everyone ... thanks for your various ideas and suggestions.
I'm afraid that in the end I ran out of time and worked around the problem by changing the application logic!
March 24, 2004 at 12:16 pm
Mike,
If that tables being used during replication it's probably heavily indexed. re-building indexes is usually the killer during updates (but did you check the execution plan as suggested?).
If the update is not highly selective you'll have better luck dropping indexes, updating the table, then re-building the indexes.
Signature is NULL
March 25, 2004 at 10:16 am
I have a question on the coding snippet. Obviously I'm a bit new working with SQL in a large database environment and am trying out ideas as I come across them that will make some of the work I do easier.
As such, I have a 19 million record database that I needed to concatenate two fields together in (FName = col1 + col2)
Therefore I put up a test machine with 4 million records in it.
Using a straight update I was able to accomplish the 4 million updates in 43 minutes.
Using the code snippet from above caused me to simply kill the query (20 hours later).
I modified it to :
DECLARE @x INT
SET @x = 1
SET ROWCOUNT 5000
WHILE @x > 0
BEGIN
BEGIN TRAN
update testing set fname = col1 + col2
SET @x = @@rowcount
COMMIT TRAN
WAITFOR DELAY '00:00:0.2' (as per mkeast posting)
END
Any ideas on why this might be? Indexing wasn't done on any of the records simply so that I could hopefully compare apples to apples before I attempt to perform this update on the full database.
March 25, 2004 at 10:40 am
You need a WHERE clause - something like
update testing set fname = col1 + col2 where fname is null
otherwise it will just update the same 5000 records over and over for ever!
I found that having an index on the field being tested in the WHERE clause made things rather faster, since the overhead of having to update the index seemed to be outweighed by the time it would take to do the record selection without an index to help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply