SQL中替代双层循环实现供需匹配的优化方案问询

Hey there! Your original loop-based matching logic is slow because SQL Server is built for set operations, not iterative row-by-row processing. Let's rewrite this to use bulk set-based operations, which will drastically boost performance, especially as your DEMAND and SUPPLY tables grow.

Core Optimization Approach #

Instead of looping through each demand one by one, we'll:

• Rank demands by publish time: Ensure we process older demands first, just like your original logic.
• Match eligible supplies in bulk: For each demand, find the cheapest qualifying supply (mirroring your TOP 1 price-sorted logic) that meets the degree/reputation and price/bid criteria.
• Calculate transaction quantities: Use the minimum of demand and supply quantity for each match.
• Update/delete source tables in bulk: Adjust remaining quantities or remove fully matched rows, and log all deals in one atomic operation.

Full Set-Based Implementation #

We'll use CTEs (Common Table Expressions) to rank demands, match supplies, and compute transaction details, then use MERGE and UPDATE statements to modify source tables and populate the DEAL table.

BEGIN TRANSACTION;

-- Step 1: Rank demands by PublishTime to maintain processing order (oldest first)
WITH RankedDemands AS (
    SELECT 
        BuyerID,
        Bid,
        Quantity AS DemandQty,
        ItemID,
        LowestDegreeNew,
        LowestReputation,
        PublishTime,
        ROW_NUMBER() OVER (ORDER BY PublishTime) AS DemandRank
    FROM dbo.DEMAND
),
-- Step 2: Match each demand to the cheapest eligible supply
MatchedPairs AS (
    SELECT
        rd.BuyerID,
        s.SellerID,
        rd.ItemID,
        rd.Bid AS BuyerBid,
        s.Price AS SellerPrice,
        -- Transaction quantity = smaller of demand and supply quantity
        CASE 
            WHEN rd.DemandQty <= s.Quantity THEN rd.DemandQty
            ELSE s.Quantity 
        END AS DealQty,
        -- Track remaining quantities for post-deal updates
        rd.DemandQty - CASE WHEN rd.DemandQty <= s.Quantity THEN rd.DemandQty ELSE s.Quantity END AS RemainingDemandQty,
        s.Quantity - CASE WHEN rd.DemandQty <= s.Quantity THEN rd.DemandQty ELSE s.Quantity END AS RemainingSupplyQty
    FROM RankedDemands rd
    INNER JOIN dbo.SUPPLY s 
        ON rd.ItemID = s.ItemID 
        AND rd.Bid > s.Price
    INNER JOIN dbo.SELLER se 
        ON s.SellerID = se.ID 
        AND rd.LowestDegreeNew >= se.DegreeNew 
        AND rd.LowestReputation >= se.Reputation
    -- Ensure we only pick the cheapest eligible supply for each demand
    WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.SUPPLY s2
        INNER JOIN dbo.SELLER se2 ON s2.SellerID = se2.ID
        WHERE s2.ItemID = rd.ItemID
          AND rd.Bid > s2.Price
          AND rd.LowestDegreeNew >= se2.DegreeNew
          AND rd.LowestReputation >= se2.Reputation
          AND s2.Price < s.Price
    )
),
-- Step 3: Insert all deal records into the DEAL table
InsertDeals AS (
    INSERT INTO dbo.DEAL (BuyerID, SellerID, ItemID, Quantity, BuyerBid, SellerPrice, DealTime)
    SELECT 
        BuyerID, 
        SellerID, 
        ItemID, 
        DealQty, 
        BuyerBid, 
        SellerPrice, 
        GETDATE() AS DealTime
    FROM MatchedPairs
    OUTPUT inserted.BuyerID, inserted.ItemID, inserted.SellerID, mp.RemainingDemandQty, mp.RemainingSupplyQty
    FROM MatchedPairs mp
)
-- Step 4: Update or delete remaining demand rows
UPDATE dbo.DEMAND
SET Quantity = id.RemainingDemandQty
FROM dbo.DEMAND d
JOIN InsertDeals id ON d.BuyerID = id.BuyerID AND d.ItemID = id.ItemID
WHERE id.RemainingDemandQty > 0;

DELETE FROM dbo.DEMAND
WHERE EXISTS (
    SELECT 1
    FROM InsertDeals id
    WHERE d.BuyerID = id.BuyerID AND d.ItemID = id.ItemID AND id.RemainingDemandQty = 0
);

-- Step 5: Update or delete remaining supply rows
UPDATE dbo.SUPPLY
SET Quantity = id.RemainingSupplyQty
FROM dbo.SUPPLY s
JOIN InsertDeals id ON s.SellerID = id.SellerID AND s.ItemID = id.ItemID
WHERE id.RemainingSupplyQty > 0;

DELETE FROM dbo.SUPPLY
WHERE EXISTS (
    SELECT 1
    FROM InsertDeals id
    WHERE s.SellerID = id.SellerID AND s.ItemID = id.ItemID AND id.RemainingSupplyQty = 0
);

COMMIT TRANSACTION;

Key Notes on This Implementation #

• Atomic Transaction: Wrapped everything in a transaction to ensure all changes (deals, updates, deletes) are rolled back if anything fails—no partial matches left hanging.
• Order Preservation: The ROW_NUMBER() in RankedDemands ensures we process demands in the exact order of their PublishTime, just like your original loop.
• Efficient Matching: The NOT EXISTS subquery guarantees we only pair each demand with the lowest-priced eligible supply, matching your original TOP 1 logic.
• Bulk Operations: All updates and deletes happen in bulk, which is far more efficient than row-by-row processing in SQL Server.

Scheduling with SQL Server Agent #

To run this automatically on a schedule:

1. Open SQL Server Management Studio (SSMS), navigate to SQL Server Agent > Jobs.
2. Create a new job, add a Transact-SQL (T-SQL) step, and paste the code above.
3. Set your desired schedule (e.g., every 5 minutes, hourly) under the Schedules tab.
4. Save the job—it will run automatically at the specified intervals.

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