بهینه‌سازی عملکرد SQL Server: جایگزینی حلقه‌های WHILE با Set Based Logic در T-SQL

در SQL Server، حلقه‌های `WHILE` اغلب برای تکرار عملیات روی مجموعه‌ای از رکوردها استفاده می‌شوند. با این حال، استفاده از حلقه‌های `WHILE` معمولاً منجر به عملکرد ضعیف و زمان اجرای طولانی‌تر می‌شود، به خصوص با افزایش حجم داده‌ها. در مقابل، منطق مبتنی بر مجموعه (set-based logic) رویکردی کارآمدتر و بهینه‌تر برای پردازش داده‌ها در SQL Server است که می‌تواند به طور قابل توجهی عملکرد کوئری‌ها را بهبود بخشد. این مقاله به بررسی چگونگی جایگزینی حلقه‌های `WHILE` با راهکارهای مبتنی بر مجموعه می‌پردازد و مزایای عملکردی آن‌ها را نشان می‌دهد.

**سناریوی اولیه: ایجاد جدول و درج داده‌های نمونه**

برای نمایش تفاوت بین رویکردهای حلقه `WHILE` و مبتنی بر مجموعه، ابتدا یک جدول نمونه ایجاد می‌کنیم و چند رکورد آزمایشی در آن درج می‌کنیم. این جدول شامل ستون `Id` برای شناسایی منحصر به فرد و ستون `Value` برای ذخیره یک مقدار متنی است.

CREATE TABLE SampleData (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Value NVARCHAR(100)
);
GO

INSERT INTO SampleData (Value)
SELECT 'Sample Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10))
FROM sys.objects AS o1
CROSS JOIN sys.objects AS o2
CROSS JOIN sys.objects AS o3
TOP 1000000 -- Insert 1,000,000 rows
GO

این کد یک جدول `SampleData` ایجاد می‌کند و 1,000,000 رکورد در آن درج می‌کند. این حجم داده به ما امکان می‌دهد تفاوت‌های عملکردی را به وضوح مشاهده کنیم.

**رویکرد مبتنی بر حلقه WHILE**

در این رویکرد، ما از یک حلقه `WHILE` برای به روز رسانی ستون `Value` در جدول `SampleData` استفاده می‌کنیم. در هر تکرار، یک دسته (batch) از ردیف‌ها را پردازش می‌کنیم. این روش نیاز به مدیریت یک متغیر شمارنده و انتخاب مداوم رکوردهای بعدی برای پردازش دارد.

DECLARE @BatchSize INT = 10000;
DECLARE @StartId INT = (SELECT MIN(Id) FROM SampleData);
DECLARE @EndId INT = (SELECT MAX(Id) FROM SampleData);

WHILE @StartId <= @EndId
BEGIN
    UPDATE SampleData
    SET Value = 'Updated Value ' + CAST(Id AS NVARCHAR(10))
    WHERE Id BETWEEN @StartId AND @StartId + @BatchSize - 1;

    SET @StartId = @StartId + @BatchSize;
END;
GO

این حلقه در دسته‌های 10,000 تایی رکوردها را به‌روزرسانی می‌کند. این روش در مقایسه با منطق مبتنی بر مجموعه، منابع بیشتری مصرف کرده و زمان بیشتری می‌برد.

**رویکرد مبتنی بر مجموعه با استفاده از CTE و ROW_NUMBER()**

یکی از قدرتمندترین روش‌های مبتنی بر مجموعه برای پردازش دسته‌ای، استفاده از Common Table Expression (CTE) و تابع `ROW_NUMBER()` است. این روش به شما امکان می‌دهد تا داده‌ها را به صورت منطقی به دسته‌هایی تقسیم کنید و سپس هر دسته را به یکباره پردازش کنید، بدون نیاز به حلقه صریح `WHILE`. این رویکرد به ویژه برای سناریوهایی که نیاز به پردازش بخش‌های متوالی از داده دارید مفید است.

برای ایجاد دسته‌ها با `ROW_NUMBER()`، ما ردیف‌ها را شماره‌گذاری کرده و سپس با استفاده از یک فرمول ساده، آن‌ها را به دسته‌ها تقسیم می‌کنیم. این فرمول هر دسته را بر اساس اندازه دسته تعریف شده تعیین می‌کند.

UPDATE sd
SET Value = 'Updated Value ' + CAST(sd.Id AS NVARCHAR(10))
FROM SampleData sd
INNER JOIN (
    SELECT Id,
           NTILE( (SELECT CEILING(COUNT(*)/CAST(@BatchSize AS DECIMAL)) FROM SampleData) ) OVER (ORDER BY Id) AS BatchNumber
    FROM SampleData
) AS Batches ON sd.Id = Batches.Id;
GO

یک روش جایگزین برای تقسیم‌بندی به دسته‌های ثابت با استفاده از `ROW_NUMBER()`، استفاده از عملگر تقسیم صحیح است تا ردیف‌ها را بر اساس یک اندازه دسته مشخص گروه‌بندی کند. این امر به شما اجازه می‌دهد تا هر ردیف را به یک شناسه دسته منحصر به فرد نگاشت کنید. فرمول برای تعیین شناسه دسته به صورت زیر است:

(ROW_NUMBER() OVER (ORDER BY Id) – 1) / @BatchSize + 1

این فرمول، شناسه دسته را بر اساس شماره ردیف و اندازه دسته محاسبه می‌کند. هر بار که `ROW_NUMBER()` از مضرب `BatchSize` عبور می‌کند، شناسه دسته جدیدی اختصاص داده می‌شود.

WITH CTE_Batch AS (
    SELECT
        Id,
        (ROW_NUMBER() OVER (ORDER BY Id) - 1) / @BatchSize + 1 AS BatchNumber
    FROM SampleData
)
UPDATE sd
SET Value = 'Updated Value ' + CAST(sd.Id AS NVARCHAR(10))
FROM SampleData sd
INNER JOIN CTE_Batch AS c ON sd.Id = c.Id;
GO

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

**رویکرد مبتنی بر مجموعه با جدول موقت و OFFSET/FETCH**

این روش از یک جدول موقت برای نگهداری شناسه ردیف‌هایی که قبلاً پردازش شده‌اند یا باید پردازش شوند، استفاده می‌کند. سپس با استفاده از `OFFSET/FETCH` دسته‌های کوچکتر از داده را استخراج کرده و پردازش می‌کند. این رویکرد می‌تواند برای سناریوهایی که نیاز به مدیریت پیشرفت در پردازش دسته‌ای دارید، مفید باشد.

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

-- ایجاد یک جدول موقت و درج IDs
SELECT Id
INTO #TempIds
FROM SampleData
ORDER BY Id;

DECLARE @Offset INT = 0;
DECLARE @RowCount INT;

WHILE 1 = 1
BEGIN
    SELECT Id
    FROM #TempIds
    ORDER BY Id
    OFFSET @Offset ROWS
    FETCH NEXT @BatchSize ROWS ONLY;

    SET @RowCount = @@ROWCOUNT;

    IF @RowCount = 0
        BREAK;

    -- در این مثال، ما فقط IDs را انتخاب می کنیم.
    -- برای به روز رسانی واقعی، باید JOIN کنیم:
    -- UPDATE sd
    -- SET Value = 'Updated Value ' + CAST(sd.Id AS NVARCHAR(10))
    -- FROM SampleData sd
    -- INNER JOIN (
    --    SELECT Id
    --    FROM #TempIds
    --    ORDER BY Id
    --    OFFSET @Offset ROWS
    --    FETCH NEXT @BatchSize ROWS ONLY
    -- ) AS BatchData ON sd.Id = BatchData.Id;

    SET @Offset = @Offset + @RowCount;
END;

DROP TABLE #TempIds;
GO

**مقایسه عملکرد و کارایی**

برای ارزیابی دقیق‌تر عملکرد، می‌توانید از دستورات `SET STATISTICS TIME ON` و `SET STATISTICS IO ON` استفاده کنید. این دستورات به شما اطلاعاتی در مورد زمان پردازش CPU، زمان سپری شده، و تعداد عملیات ورودی/خروجی (IO) انجام شده توسط کوئری می‌دهند.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- اجرای یکی از کدهای بالا (مثلاً CTE)

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

با مقایسه خروجی این دستورات برای هر دو رویکرد حلقه `WHILE` و مبتنی بر مجموعه، به وضوح مشاهده خواهید کرد که راهکارهای مبتنی بر مجموعه به طور قابل توجهی کارآمدتر هستند. آن‌ها سربار کمتری برای موتور SQL Server ایجاد می‌کنند، منابع کمتری مصرف می‌کنند، و زمان اجرای کمتری دارند. این بهینه‌سازی‌ها در مقیاس بزرگ، برای مدیریت داده‌ها و بهبود عملکرد کلی پایگاه داده ضروری هستند.