如何高效将RecycleProductID表的一次性ProductID批量分配至Product表？

Hey there, that row-by-row loop you're using is definitely the culprit here—processing millions of rows one at a time is going to take forever, as you've already seen. Let's ditch that agonizing loop and use set-based SQL operations that'll handle this in a fraction of the time.

Why Your Current Approach Is So Slow #

Your while loop is a classic RBAR (Row By Agonizing Row) pattern:

• You're updating only 1 row per loop iteration, which means you'd need 1.5 million iterations just to fill all empty Product slots (and your loop is even worse, running up to the max RecycleProductID value which is 60 million+).
• Each loop involves multiple queries (fetching the next unused ID, updating Product, updating RecycleProductID) with extra overhead from the temporary UsedProductID table.
• The @Min/@Max loop logic is totally unnecessary—you only need to fill 1.5 million empty slots, not iterate through every possible ID up to 60 million.

The Fast Set-Based Solution #

We'll use common table expressions (CTEs) to map unused recycle IDs directly to empty Product slots in bulk, then update both tables in two efficient operations.

BEGIN TRANSACTION; -- Wrap in a transaction to roll back if anything goes wrong

-- Step 1: Get the unused RecycleProductIDs we need (exactly as many as empty Product slots)
WITH UnusedRecycleIDs AS (
    SELECT 
        ProductID,
        ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS RowNum
    FROM dbo.RecycleProductID
    WHERE Used IS NULL
    -- Only fetch the number of IDs needed to fill empty Product rows
    FETCH NEXT (SELECT COUNT(*) FROM dbo.Product WHERE ProductID IS NULL) ROWS ONLY
),
-- Step 2: Get the empty Product slots, numbered to match the recycle IDs
EmptyProductSlots AS (
    SELECT 
        ProductID,
        ProductName,
        -- Use ORDER BY (SELECT NULL) if you don't care about which slot gets which ID
        -- Replace with a specific column (e.g., ProductName) if you need ordered assignment
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM dbo.Product
    WHERE ProductID IS NULL
)
-- Step 3: Bulk assign recycle IDs to empty Product slots
UPDATE p
SET p.ProductID = r.ProductID
FROM EmptyProductSlots p
INNER JOIN UnusedRecycleIDs r ON p.RowNum = r.RowNum;

-- Step 4: Bulk mark the used recycle IDs as "Used = 1"
UPDATE rp
SET rp.Used = 1
FROM dbo.RecycleProductID rp
INNER JOIN UnusedRecycleIDs r ON rp.ProductID = r.ProductID;

COMMIT TRANSACTION;

Key Optimizations & Notes #

• Set-Based Operations: This approach processes all 1.5 million rows in two batch updates, not one at a time.
• Targeted Data: We only fetch the exact number of recycle IDs needed (no extra rows from RecycleProductID).
• No Temporary Table Overhead: The CTEs handle the mapping without needing a separate UsedProductID table.
• Indexing Tip: Speed up the UnusedRecycleIDs CTE by adding an index on RecycleProductID:

  CREATE NONCLUSTERED INDEX IX_RecycleProductID_Used_ProductID 
  ON dbo.RecycleProductID(Used, ProductID);
  

• Batch Size Adjustment: If your server can't handle updating 1.5 million rows at once, split it into smaller batches (e.g., 100k rows each) using TOP in the CTEs and a simple loop—this is still way faster than your original row-by-row approach.

内容的提问来源于stack exchange，提问作者volume one