February 10, 2015 at 3:52 pm
Thanks for clarifying that Jeff.
Doug
February 15, 2015 at 5:16 pm
It sounds like you're just trying to do too much at once. I would suggest batching your update.
Here's the skeleton of one I use successfully on some really large update jobs I have to run, though I tend to apply it to anything that will be affecting over 100k rows. It was adapted from the delete code in this article[/url], and Michael was even cool enough to give me some additional feedback in the comments.
DECLARE @HighKey INT = -1 ,
@BatchMx INT;
WHILE EXISTS ( SELECT TOP 1
*
FROM dbo.YourTable
WHERE 1 = 1 --
-- Put all your where conditions here
)
BEGIN
SELECT TOP ( 10000 )
@BatchMx = --Use your clustered ID column here for sorting
FROM dbo.YourTable
WHERE /*YourIDColumn*/ > @HighKey
--Repeat your where clause from the exists loop here
ORDER BY /*YourIDColumn*/;
UPDATE t
SET t.col = [whatever]
FROM dbo.YourTable t
WHERE ArtifactID > @HighKey
AND ArtifactID <= @BatchMx
AND --Repeat your where clause from the exists loop here in case rows within the ID range may be outside its scope
SET @HighKey = @BatchMx;
END
February 26, 2015 at 6:17 pm
You could add a identity column to the table, IDENTITY(1,1). This can be your guide for doing the updates in the table one batch at a time.
Borrowing on Jeff's tally table, the basic design goes like :
DECLARE@lowerID int=0, @higherID int, @maxID int;
SELECT@maxID = max(n) from tally;
SET@higherID = @lowerID +5000;
WHILE@lowerID<= @maxID
BEGIN
/* just to illustrate the concept */
select n from tally
where n between @lowerID and @higherID
/* here you would apply this to your table like so ... */
--update table yourtable
--set column_34 = ( CASE WHEN -- ... )
--where identityColumn Between @lowerID and @higherID
set @lowerID = @higherID+1;
set @higherID = @higherID+5000
END
You could also optionally include a checkpoint at the end of every batch with a waitfor delay
----------------------------------------------------
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply