Database

DROP TABLE IF EXISTS #ProcessedIDs;
CREATE TABLE #ProcessedIDs (id int NOT NULL Primary Key);

DECLARE @RC int = 5000;

WHILE @RC = 5000
BEGIN
    UPDATE TOP (5000)
        T
    SET
        ...
    OUTPUT
        inserted.id INTO #ProcessedIDs
    FROM
        Target As T
        INNER JOIN Source As S
        ON S.id = T.id
    WHERE
        Not Exists
        (
            SELECT 1
            FROM #ProcessedIDs As P
            WHERE P.id = T.id
        )
    ;
    
    SET @RC = @@ROWCOUNT;
END;

DROP TABLE IF EXISTS #ProcessedIDs;

Richard Deeming wrote:

When you use the TOP clause with the UPDATE statement, there's no guarantee that the rows to update will be picked in any particular order. Using the MAX(id) option, you could end up missing rows.

Richard Deeming wrote:

I notice you've replaced the temporary table with a table variable. Was there a reason for that?

Richard Deeming wrote:

IIRC, execution plans for table variables tend to assume they contain a very low number of rows, which might explain the poor performance.

Jörgen Andersson wrote:

The other problem is that the ID sequence is having gaps in it.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

DECLARE
        @LastID int = 0,
        @NextID int,
        @RC int = 1;
    
WHILE (@RC > 0)
BEGIN
    SELECT  TOP 5000
            @NextID = s.id
    FROM    Source s WITH(NOLOCK)
    WHERE   s.id> @LastID
    ORDER BY s.id
    ;
    UPDATE  t
        SET  ------
    FROM    Source s
    JOIN    Target t WITH(NOLOCK) ON t.id = s.id
    WHERE   s.id > @LastID 
        AND s.id <= @NextID 
    ;
    SET @RC = @@ROWCOUNT;
    SET @LastID = @NextID ;
END

Quote:

SQL

UPDATE  t
...
FROM    Source s
JOIN    Target t WITH(NOLOCK) ON t.id = s.id
...

Avoid using NOLOCK on SQL Server UPDATE and DELETE statements[^]:

SQL

UPDATE t1 
SET t1.x = something
FROM dbo.t1 WITH (NOLOCK)
INNER JOIN ...;

jschell wrote:

Because the key is generated on the client not the server.

public int GetNextPrimaryKey(string collectionName)
{
    lock(_lockObj)
    {
        // Get the PrimaryKeys collection from the DB
        IMongoCollection<PrimaryKeyEntity> pkCol = GetCollection<PrimaryKeyEntity>("PrimaryKeys");

        // Attempt to find the row for the collection 
        PrimaryKeyEntity entity = pkCol.Find(x => x.CollectionName == collectionName).FirstOrDefault();

        // If it doesn't exist...
        if (entity == null)
        {
            // Create and insert the row for the desired collection
            entity = new PrimaryKeyEntity
            {
                PrimaryKey = 1,
                CollectionName = collectionName
            };
            pkCol.InsertOne(entity);
        }
        else
        {
            // Increment the PK
            entity.PrimaryKey = entity.PrimaryKey + 1;

            var filter = Builders<PrimaryKeyEntity>.Filter.Eq(x => x.CollectionName, collectionName);
            var update = Builders<PrimaryKeyEntity>.Update.Set(x => x.PrimaryKey, entity.PrimaryKey);
            var result = pkCol.UpdateOneAsync(filter, update).Result;
        }

        return entity.PrimaryKey;
    }
}

Kevin Marois wrote:

So what if I created a PK table on the sever: