Looping over update statement gets progressively slower with SQL Server
<p><strong>Overview</strong></p>

<p>I'm converting a <code>UnitsOrdered</code> column from an <code>int</code> to a floating point. My strategy was to create a new column with the new datatype, and populate it with the old column's data. This will be performed on a table with hundreds of millions of records. To keep the transaction smaller, I was hoping to chunk the process, looping over some fixed number of rows.</p>

<p><strong>Issue</strong></p>

<p>The issue I've found is that the update get progressively slower by a lot. I was hoping that there was a simple explanation (and consequently a fix). My hunch would be that updating rows 1-5 would be the same speed as 6-10 which would be the same as 1000001-1000005, but that doesn't appear to be the case.</p>

<p><strong>Things I've tried:</strong></p>

<ul>
<li>increasing log size (e.g. to 25GB)</li>
<li>changing <code>option(recompile)</code> and <code>with(tablock)</code></li>
<li>rebuilding stats at first, and during (e.g. every 10 million records)</li>
<li>Using a cte, not using a cte, inserting between ranges, etc.</li>
<li>changing the number of records updated at once</li>
</ul>

<p><strong>Notes</strong></p>

<ul>
<li>There are no indexes on <code>UnitsOrdered</code></li>
<li>There is a clustered primary key on <code>OrderItemID</code> </li>
<li><p>Happy to provide any more information.  The schema of the table is super vanilla (by design)...it's roughly: </p>

<pre><code>Create table dbo.OrderItems
(
     OrderItemID int primary key identity, 
     UnitsOrdered int NOT NULL, 
     UnitsOrdered1 real NOT NULL
)
</code></pre></li>
</ul>

<p><strong>SQL:</strong></p>

<pre><code>print 'Populating column data...';
GO
set nocount on;
declare 
    @totalRows int = 0,
    @affectedRows int = 0,
    @rowsFetched int = 1000000,
    @rowsProcessed int = 0,
    @statusMessage nvarchar(100),
    @time datetime,
    @refreshEvery int = 10000000

select @totalRows = Count(*)
from dbo.OrderItems

while(1 = 1) begin

    set @time = getUtcDate();

    ;with cte as(
        select *
        from dbo.OrderItems with(tablock)
        order by 
            OrderItemID

        offset @rowsProcessed ROWS
        fetch next @rowsFetched ROWS ONLY
    )

    update cte
    set
        UnitsOrdered1 = UnitsOrdered
        option(RECOMPILE) 


    set @affectedRows = @@ROWCOUNT;

    if(@affectedRows = 0) begin
        break;
    end

    --increment processed rows
    set @rowsProcessed = @rowsProcessed + @affectedRows;

    --set status message (%% is escaped)
    set @statusMessage = Concat(    '-&gt;',
                                    @affectedRows,
                                    ' rows updated in ',
                                    datediff(s,@time,getUtcDate()),
                                    ' seconds. ',
                                    (Cast(@rowsProcessed as float) / Cast(NULLIF(@totalRows,0) as float))*100,
                                    '%% complete...');

    --we use raiseerror so we can output the message instead of buffering it.
    raiserror(@statusMessage,0,1) with nowait

end
GO
</code></pre>

<p><strong>Progress:</strong></p>

<pre><code>    Populating column data...
    -&gt;1000000 rows updated in 1 seconds. 2.61037% complete...
    -&gt;1000000 rows updated in 3 seconds. 5.22074% complete...
    -&gt;1000000 rows updated in 2 seconds. 7.83111% complete...
    -&gt;1000000 rows updated in 3 seconds. 10.4415% complete...
    -&gt;1000000 rows updated in 3 seconds. 13.0519% complete...
    -&gt;1000000 rows updated in 4 seconds. 15.6622% complete...
    -&gt;1000000 rows updated in 4 seconds. 18.2726% complete...
    -&gt;1000000 rows updated in 4 seconds. 20.883% complete...
    -&gt;1000000 rows updated in 4 seconds. 23.4933% complete...
    -&gt;1000000 rows updated in 5 seconds. 26.1037% complete...
    -&gt;1000000 rows updated in 9 seconds. 28.7141% complete...
    -&gt;1000000 rows updated in 5 seconds. 31.3245% complete...
    -&gt;1000000 rows updated in 6 seconds. 33.9348% complete...
    -&gt;1000000 rows updated in 6 seconds. 36.5452% complete...
    -&gt;1000000 rows updated in 6 seconds. 39.1556% complete...
    -&gt;1000000 rows updated in 7 seconds. 41.7659% complete...
(etc. for several million records)

    -&gt;1000000 rows updated in 71 seconds. 84.3763% complete...
    -&gt;1000000 rows updated in 74 seconds. 86.9867% complete...
    -&gt;1000000 rows updated in 87 seconds. 89.597% complete...
    -&gt;1000000 rows updated in 92 seconds. 92.2074% complete...
</code></pre>

<p>Thanks SO!</p>