SQLServer游标使用导致性能下降的优化措施

DECLARE OrderCursor CURSOR FOR 
SELECT OrderID, TotalAmount FROM Orders WHERE Status = 'Pending'

OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @OrderID, @Amount

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 模拟复杂计算
    UPDATE Orders SET Tax = @Amount * 0.13 WHERE CURRENT OF OrderCursor
    
    FETCH NEXT FROM OrderCursor INTO @OrderID, @Amount
END

CLOSE OrderCursor
DEALLOCATE OrderCursor

-- 原始游标方案（耗时120秒）
DECLARE cursor_emp CURSOR FOR
SELECT EmployeeID, Salary FROM Employees

OPEN cursor_emp
FETCH NEXT FROM cursor_emp INTO @id, @salary

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Employees 
    SET Tax = @salary * 0.15 
    WHERE EmployeeID = @id
    
    FETCH NEXT FROM cursor_emp INTO @id, @salary
END

-- 优化后的集合操作（耗时0.8秒）
UPDATE Employees
SET Tax = Salary * 0.15
WHERE DepartmentID = 5

SELECT * INTO #TempOrders 
FROM Orders 
WHERE OrderDate > '2023-01-01'

-- 在临时表上创建索引
CREATE CLUSTERED INDEX idx_temp ON #TempOrders(OrderID)

-- 使用WHILE循环处理
DECLARE @PageSize INT = 5000
DECLARE @PageIndex INT = 0

WHILE 1=1
BEGIN
    UPDATE TOP (@PageSize) t
    SET t.Status = 'Processed'
    FROM #TempOrders t
    WHERE t.OrderID > @PageIndex
    
    IF @@ROWCOUNT = 0 BREAK
    
    SET @PageIndex = (SELECT MAX(OrderID) FROM #TempOrders 
                     WHERE OrderID > @PageIndex)
END

DECLARE sales_cursor CURSOR 
STATIC FOR 
SELECT ProductID, SUM(Quantity) 
FROM Sales 
GROUP BY ProductID

OPEN sales_cursor
-- 处理逻辑...

DECLARE @ClientID INT
DECLARE @OrderCount INT

DECLARE client_cursor CURSOR FOR
SELECT ClientID FROM Clients WHERE Region = 'North'

OPEN client_cursor
FETCH NEXT FROM client_cursor INTO @ClientID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 更新主数据库
    UPDATE MainDB..Clients SET LastActive = GETDATE()
    
    -- 写入分析数据库
    INSERT INTO AnalyticsDB..ClientLog(ClientID, ActionTime)
    VALUES(@ClientID, GETDATE())
    
    FETCH NEXT FROM client_cursor INTO @ClientID
END

DECLARE @NodeID INT
DECLARE @ParentID INT

DECLARE hierarchy_cursor CURSOR LOCAL STATIC FOR
SELECT NodeID, ParentID FROM OrganizationTree

OPEN hierarchy_cursor
FETCH NEXT INTO @NodeID, @ParentID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 递归计算路径
    EXEC CalculateTreePath @NodeID, @ParentID
    
    FETCH NEXT INTO @NodeID, @ParentID
END

// 使用ADO.NET的SqlDataReader实现分页处理
using (SqlConnection conn = new SqlConnection(connString))
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM LargeTable", conn);
    conn.Open();
    
    using (SqlDataReader reader = cmd.ExecuteReader(
        CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
    {
        int bufferSize = 5000;
        var buffer = new object[bufferSize];
        
        while (reader.Read())
        {
            // 批量处理逻辑
            ProcessRecord(reader);
            
            if (++recordCount % bufferSize == 0)
            {
                // 每5000条提交事务
                transaction.Commit();
                transaction = conn.BeginTransaction();
            }
        }
    }
}

-- 分页计算利息
DECLARE @PageSize INT = 5000
DECLARE @StartID INT = 0

WHILE 1=1
BEGIN
    UPDATE TOP (@PageSize) a
    SET Interest = b.Balance * Rate
    FROM Accounts a
    JOIN InterestRates b ON a.AccountType = b.TypeID
    WHERE a.AccountID > @StartID
    
    SELECT @StartID = MAX(AccountID)
    FROM Accounts
    WHERE AccountID > @StartID
    
    IF @@ROWCOUNT = 0 BREAK
END