如何在MS SQL Server中用集合方案替换游标？优化慢While循环求助

First, let's cut to the chase: your WHILE loop is still doing row-by-row (RBAR) processing—just like the cursor was. In fact, it might be slower because you're repeatedly scanning the table variable @Modules to fetch each row by ID (no index means full scans every time) and executing individual INSERT statements for every qualifying row. Cursors sometimes have built-in optimizations (like FAST_FORWARD) that can make them snappier than naive WHILE loops in these scenarios.

The real solution here is to ditch both the cursor and the WHILE loop entirely and use a set-based operation—SQL Server is engineered to handle batches of data efficiently, not single rows one at a time.

Let's Rewrite Your Logic with Set-Based Code #

Your core logic boils down to:

• Group rows by ListType
• Generate an incrementing counter starting at 0 for each group
• Insert rows into QAT_ListElid2 only if the counter doesn't match the original orderArray, updating LeftTSMKEY in the process

Here's how to do this with window functions—this will be orders of magnitude faster:

WITH ModuleGroups AS (
    SELECT 
        ListElid,
        ListType,
        orderArray,
        LeftTSMKEY,
        -- Generate counter per ListType, starting at 0
        ROW_NUMBER() OVER (PARTITION BY ListType ORDER BY orderArray) - 1 AS inc
    FROM QAT_ListElid
)
INSERT INTO QAT_ListElid2 (ListElid, ListType, orderArray, LeftTSMKEY)
SELECT 
    ListElid,
    ListType,
    inc,
    LeftTSMKEY + CAST(inc AS NVARCHAR(10)) + ']'
FROM ModuleGroups
WHERE inc <> orderArray; -- Only insert rows where counter doesn't match original order

Why This Is Better #

1. No row-by-row overhead: The entire operation runs in a single pass over your source data.
2. No table variable bloat: We work directly with QAT_ListElid instead of copying data into a temporary structure.
3. Fewer I/O operations: A single bulk INSERT is way more efficient than hundreds/thousands of individual inserts.
4. Index optimization: If QAT_ListElid has indexes on ListType and orderArray, the ROW_NUMBER() calculation will be even faster.

If You Must Keep the Loop (Not Recommended) #

If set-based code isn't an option for some reason, here are two quick fixes to speed up your WHILE loop:

• Add an index to the table variable: Since you're querying by ID, add a primary key constraint to turn lookups into index seeks:

  DECLARE @Modules TABLE ( 
      ID INT IDENTITY(1, 1) PRIMARY KEY, 
      ListElid VARCHAR(4000), 
      ListType VARCHAR(30), 
      orderArray VARCHAR(4000), 
      LeftTSMKEY VARCHAR(30) 
  )
  

• Use a FAST_FORWARD cursor: If you were using a default cursor before, switching to FAST_FORWARD might outperform your WHILE loop. But again, set-based is still the gold standard.

Final Thought #

In SQL, row-by-row processing should always be your last resort. Set-based operations are almost always faster, more maintainable, and easier to debug.

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