بهینه سازی SQL Server جایگزینی Cursors با عملیات مبتنی بر مجموعه OUTPUT و MERGE برای عملکرد برتر

بهینه سازی SQL Server: جایگزینی Cursors با عملیات مبتنی بر مجموعه (OUTPUT و MERGE) برای عملکرد برتر

Cursors در SQL Server برای پردازش سطر به سطر نتایج یک مجموعه داده استفاده می‌شوند، اما اغلب به دلیل سربار بالا و مصرف منابع زیاد، به یک گلوگاه عملکردی تبدیل می‌گردند. در بسیاری از سناریوها، عملیات مبتنی بر مجموعه (Set-Based Operations) می‌توانند همان نتایج را با کارایی و سرعت بسیار بالاتر ارائه دهند. در این مقاله، روش‌هایی برای جایگزینی Cursorها با استفاده از بند OUTPUT و دستور MERGE در SQL Server بررسی می‌شود.

آماده‌سازی داده‌های نمونه

ابتدا، برای شبیه‌سازی یک سناریوی واقعی، یک پایگاه داده جدید به همراه جداول مورد نیاز و داده‌های نمونه ایجاد می‌کنیم.

CREATE DATABASE CursorReplacementDemo;
GO

USE CursorReplacementDemo;
GO

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    StockQuantity INT,
    LastUpdated DATETIME DEFAULT GETDATE()
);
GO

CREATE TABLE ProductAudit (
    AuditID INT PRIMARY KEY IDENTITY(1,1),
    ProductID INT,
    OldPrice DECIMAL(10, 2),
    NewPrice DECIMAL(10, 2),
    OldStockQuantity INT,
    NewStockQuantity INT,
    ChangeDate DATETIME DEFAULT GETDATE(),
    ChangeType NVARCHAR(50)
);
GO

پس از ایجاد جداول، تعدادی رکورد به جدول Products اضافه می‌کنیم تا بتوانیم عملیات را روی آن‌ها انجام دهیم.

INSERT INTO Products (ProductName, Price, StockQuantity) VALUES
('لپ تاپ X', 1200.00, 50),
('گوشی Y', 800.00, 120),
('تبلت Z', 500.00, 80),
('مانیتور A', 300.00, 60),
('کیبورد B', 75.00, 200);
GO

سناریوی مشکل: به‌روزرسانی سطر به سطر

فرض کنید نیاز داریم قیمت برخی محصولات را افزایش دهیم و همزمان، تغییرات قیمت و موجودی قبلی و جدید را در جدول ProductAudit ثبت کنیم. یک راهکار سنتی و ناکارآمد برای این کار استفاده از Cursor است.

رویکرد مبتنی بر Cursor

در این روش، با استفاده از یک Cursor، روی هر محصول به صورت جداگانه تکرار می‌کنیم، قیمت آن را به‌روزرسانی کرده و سپس جزئیات تغییر را در جدول ProductAudit درج می‌کنیم. این عملیات می‌تواند برای تعداد کمی از رکوردها قابل قبول باشد، اما برای مجموعه‌های داده بزرگ، به شدت زمان‌بر و ناکارآمد خواهد بود.

DECLARE @ProductID INT, @OldPrice DECIMAL(10, 2), @NewPrice DECIMAL(10, 2);
DECLARE @OldStockQuantity INT, @NewStockQuantity INT;
DECLARE @ProductName NVARCHAR(100);

-- Cursor برای انتخاب محصولاتی که نیاز به به‌روزرسانی دارند
DECLARE ProductCursor CURSOR FOR
SELECT ProductID, Price, StockQuantity, ProductName
FROM Products
WHERE ProductID IN (1, 3); -- فقط برای محصولات خاصی که میخواهیم قیمتشان افزایش یابد

OPEN ProductCursor;

FETCH NEXT FROM ProductCursor INTO @ProductID, @OldPrice, @OldStockQuantity, @ProductName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- محاسبه قیمت جدید
    SET @NewPrice = @OldPrice * 1.10; -- 10% افزایش قیمت
    SET @NewStockQuantity = @OldStockQuantity - 5; -- کاهش موجودی برای مثال

    -- به‌روزرسانی جدول محصولات
    UPDATE Products
    SET Price = @NewPrice, StockQuantity = @NewStockQuantity, LastUpdated = GETDATE()
    WHERE ProductID = @ProductID;

    -- درج اطلاعات تغییر در جدول Audit
    INSERT INTO ProductAudit (ProductID, OldPrice, NewPrice, OldStockQuantity, NewStockQuantity, ChangeType)
    VALUES (@ProductID, @OldPrice, @NewPrice, @OldStockQuantity, @NewStockQuantity, 'Price & Stock Update (Cursor)');

    FETCH NEXT FROM ProductCursor INTO @ProductID, @OldPrice, @OldStockQuantity, @ProductName;
END;

CLOSE ProductCursor;
DEALLOCATE ProductCursor;

SELECT * FROM Products;
SELECT * FROM ProductAudit;
GO

رویکرد مبتنی بر مجموعه با استفاده از بند OUTPUT

بند OUTPUT در SQL Server به شما امکان می‌دهد تا داده‌های متاثر از یک دستور DML (مانند INSERT، UPDATE، DELETE، MERGE) را بلافاصله پس از اجرا بازیابی کنید. این ویژگی برای ثبت تغییرات در جدول Audit بسیار مفید است و نیاز به Cursor را از بین می‌برد.

در این مثال، تمام محصولات با ProductID مشخص به صورت یکجا به‌روزرسانی می‌شوند و همزمان، اطلاعات قدیمی و جدید از طریق بند OUTPUT در جدول ProductAudit درج می‌گردند. این روش نه تنها کارایی بالاتری دارد، بلکه کد را نیز خواناتر و نگهداری آن را آسان‌تر می‌کند.

-- بازنشانی داده‌ها برای مثال بعدی
DELETE FROM ProductAudit;
UPDATE Products SET Price = 1200.00, StockQuantity = 50, LastUpdated = GETDATE() WHERE ProductID = 1;
UPDATE Products SET Price = 800.00, StockQuantity = 120, LastUpdated = GETDATE() WHERE ProductID = 2;
UPDATE Products SET Price = 500.00, StockQuantity = 80, LastUpdated = GETDATE() WHERE ProductID = 3;
GO

-- به‌روزرسانی محصولات و ثبت تغییرات با OUTPUT
UPDATE Products
SET
    Price = P.Price * 1.10, -- 10% افزایش قیمت
    StockQuantity = P.StockQuantity - 5, -- کاهش موجودی
    LastUpdated = GETDATE()
OUTPUT
    DELETED.ProductID,
    DELETED.Price AS OldPrice,
    INSERTED.Price AS NewPrice,
    DELETED.StockQuantity AS OldStockQuantity,
    INSERTED.StockQuantity AS NewStockQuantity,
    GETDATE() AS ChangeDate,
    'Price & Stock Update (OUTPUT)' AS ChangeType
INTO ProductAudit (ProductID, OldPrice, NewPrice, OldStockQuantity, NewStockQuantity, ChangeDate, ChangeType)
FROM Products AS P
WHERE P.ProductID IN (1, 3); -- فقط برای محصولات خاصی که میخواهیم قیمتشان افزایش یابد

SELECT * FROM Products;
SELECT * FROM ProductAudit;
GO

رویکرد مبتنی بر مجموعه با استفاده از دستور MERGE

دستور MERGE یک راهکار قدرتمند و انعطاف‌پذیر برای همگام‌سازی دو جدول (یک جدول منبع و یک جدول هدف) در یک عملیات واحد است. این دستور می‌تواند بسته به مطابقت رکوردها، عملیات INSERT، UPDATE و DELETE را انجام دهد. مانند UPDATE با OUTPUT، دستور MERGE نیز از بند OUTPUT برای ثبت تغییرات پشتیبانی می‌کند.

در این سناریو، یک جدول موقتی به عنوان منبع برای قیمت‌های جدید ایجاد می‌کنیم و از MERGE برای به‌روزرسانی جدول Products و ثبت تغییرات در ProductAudit استفاده می‌کنیم.

-- بازنشانی داده‌ها برای مثال بعدی
DELETE FROM ProductAudit;
UPDATE Products SET Price = 1200.00, StockQuantity = 50, LastUpdated = GETDATE() WHERE ProductID = 1;
UPDATE Products SET Price = 800.00, StockQuantity = 120, LastUpdated = GETDATE() WHERE ProductID = 2;
UPDATE Products SET Price = 500.00, StockQuantity = 80, LastUpdated = GETDATE() WHERE ProductID = 3;
GO

-- ایجاد جدول موقت برای داده‌های منبع
CREATE TABLE #PriceUpdates (
    ProductID INT,
    NewPrice DECIMAL(10, 2),
    StockChange INT
);

INSERT INTO #PriceUpdates (ProductID, NewPrice, StockChange) VALUES
(1, 1350.00, -7), -- محصول 1: قیمت جدید و کاهش موجودی
(3, 580.00, -3),  -- محصول 3: قیمت جدید و کاهش موجودی
(6, 150.00, 10);  -- محصول 6: محصول جدید برای درج

-- استفاده از MERGE برای به‌روزرسانی یا درج و ثبت تغییرات
MERGE Products AS Target
USING #PriceUpdates AS Source
ON (Target.ProductID = Source.ProductID)
WHEN MATCHED THEN
    UPDATE SET
        Target.Price = Source.NewPrice,
        Target.StockQuantity = Target.StockQuantity + Source.StockChange,
        Target.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price, StockQuantity, LastUpdated)
    VALUES (Source.ProductID, 'محصول جدید ' + CAST(Source.ProductID AS NVARCHAR(10)), Source.NewPrice, Source.StockChange, GETDATE())
OUTPUT
    ISNULL(DELETED.ProductID, INSERTED.ProductID) AS ProductID,
    DELETED.Price AS OldPrice,
    INSERTED.Price AS NewPrice,
    DELETED.StockQuantity AS OldStockQuantity,
    INSERTED.StockQuantity AS NewStockQuantity,
    GETDATE() AS ChangeDate,
    $action AS ChangeType -- $action نشان می‌دهد که عملیات INSERT یا UPDATE بوده است
INTO ProductAudit (ProductID, OldPrice, NewPrice, OldStockQuantity, NewStockQuantity, ChangeDate, ChangeType);

SELECT * FROM Products;
SELECT * FROM ProductAudit;
GO