بهینه‌سازی DML در SQL Server با پردازش دسته‌ای

افزایش کارایی عملیات DML در SQL Server: راهنمای جامع پردازش دسته‌ای

عملیات DML (Data Manipulation Language) نظیر INSERT، UPDATE و DELETE روی جداول بزرگ در SQL Server می‌تواند چالش‌برانگیز باشد. این فرآیندها اغلب زمان‌بر هستند، باعث افزایش حجم لاگ تراکنش (Transaction Log) می‌شوند و ممکن است رقابت (Contention) و قفل‌شدگی (Locking) بالایی را در پایگاه داده ایجاد کنند. در نتیجه، این مسائل می‌توانند به افت شدید کارایی سیستم و تجربه کاربری ضعیف منجر شوند.

مدیران پایگاه داده (DBAها) و توسعه‌دهندگان همواره به دنبال روش‌هایی برای بهینه‌سازی این عملیات برای کاهش تأثیر منفی آن‌ها بر روی کارایی کلی سیستم هستند. رویکرد پردازش دسته‌ای (Batch Processing) یکی از مؤثرترین استراتژی‌ها برای مقابله با این چالش‌هاست. در این مقاله به بررسی چندین روش برای اجرای کارآمدتر عملیات DML بزرگ با استفاده از پردازش دسته‌ای خواهیم پرداخت.

توصیف مشکل: چالش‌های DML در مقیاس بزرگ

هنگام اجرای یک عملیات DML واحد و بزرگ، SQL Server تمامی تغییرات را به عنوان یک تراکنش منفرد در نظر می‌گیرد. این رویکرد پیامدهای متعددی دارد:

• مصرف منابع بالا: اجرای یکباره تعداد زیادی عملیات می‌تواند به مصرف بیش از حد CPU، حافظه و IO منجر شود.
• افزایش حجم لاگ تراکنش: تمامی تغییرات در لاگ تراکنش ثبت می‌شوند. یک تراکنش بزرگ می‌تواند به رشد سریع و عظیم فایل لاگ منجر شده و مدیریت آن را دشوار سازد.
• قفل‌شدگی و رقابت: عملیات DML بزرگ ممکن است منابع زیادی را برای مدت طولانی قفل (Lock) کنند و باعث کندی یا توقف دیگر عملیات در پایگاه داده شوند.
• زمان بازیابی طولانی: در صورت نیاز به Rollback یک تراکنش بزرگ، فرآیند بازیابی می‌تواند بسیار طولانی و پرمصرف باشد.

راه‌حل: پردازش DML به صورت دسته‌ای

ایده اصلی این است که عملیات DML را به قطعات کوچک‌تر تقسیم کنیم که هر بار تعداد مشخصی از سطرها را پردازش می‌کنند. این کار باعث می‌شود که هر دسته به عنوان یک تراکنش مستقل یا بخشی از یک تراکنش بزرگ‌تر با محدودیت‌های کنترل‌شده اجرا شود. مزایای این رویکرد عبارتند از:

• کاهش مصرف منابع: هر دسته، منابع کمتری را به صورت همزمان مصرف می‌کند.
• کنترل بهتر بر لاگ تراکنش: با Commit کردن تراکنش‌های کوچک‌تر، لاگ تراکنش می‌تواند به صورت منظم‌تر تخلیه (Truncate) شود.
• کاهش قفل‌شدگی: مدت زمان نگهداری قفل‌ها کاهش می‌یابد و این امر به بهبود هم‌زمانی (Concurrency) در پایگاه داده کمک می‌کند.
• مدیریت آسان‌تر خطا: در صورت بروز خطا، فقط دسته فعلی نیاز به Rollback دارد.

مثال ۱: ایده اصلی پردازش دسته‌ای با WHILE و ROWCOUNT

این مثال نحوه حذف سطرها به صورت دسته‌ای را با استفاده از یک حلقه WHILE و تابع @@ROWCOUNT نشان می‌دهد.

ابتدا، یک جدول تستی ایجاد کرده و داده‌های نمونه را وارد می‌کنیم:

IF OBJECT_ID('dbo.testDML') IS NOT NULL
    DROP TABLE dbo.testDML;
GO

CREATE TABLE dbo.testDML (
    ID INT IDENTITY(1,1),
    SomeValue VARCHAR(50)
);
GO

INSERT INTO dbo.testDML (SomeValue)
SELECT TOP (100000) 'Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.all_objects a, sys.all_objects b;
GO

SELECT COUNT(*) AS InitialRowCount FROM dbo.testDML;
GO

در ادامه، از یک حلقه WHILE برای حذف سطرها به صورت دسته‌های ۱۰ هزارتایی استفاده می‌کنیم تا زمانی که هیچ سطری برای حذف باقی نماند.

DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = @BatchSize;

WHILE (@RowsAffected = @BatchSize)
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.testDML
    WHERE ID BETWEEN 1 AND 50000; -- فقط 50000 سطر اول را حذف می کنیم

    SET @RowsAffected = @@ROWCOUNT;
    -- می توانید LOG_BACKUP را در اینجا اجرا کنید یا بعداً با یک Job مجزا
END;
GO

SELECT COUNT(*) AS RemainingRowCount FROM dbo.testDML;
GO

در این مثال، دستور DELETE TOP (@BatchSize) به صورت خودکار فقط تعداد مشخصی از سطرها را حذف می‌کند. پس از هر حذف، می‌توانیم تعداد سطرهای متاثر را با استفاده از عبارت زیر بررسی کنیم:

این تابع یک متغیر سیستمی است که تعداد سطرهایی که توسط آخرین دستور T-SQL تحت تأثیر قرار گرفته‌اند را باز می‌گرداند. حلقه WHILE تا زمانی ادامه می‌یابد که تعداد سطرهای حذف شده در یک دسته کمتر از اندازه دسته (@BatchSize) باشد که نشان‌دهنده اتمام سطرها برای پردازش است.

مثال ۲: استفاده از TOP (N) و مشکل بدون ORDER BY

هنگام استفاده از TOP (N) برای حذف سطرها، اگر از عبارت ORDER BY استفاده نکنید، ترتیب حذف سطرها تضمین شده نیست. این ممکن است در برخی موارد مشکل‌ساز باشد.

در اینجا یک سناریو را بررسی می‌کنیم که در آن می‌خواهیم سطرها را از یک جدول بدون ایندکس کلاستر حذف کنیم.

IF OBJECT_ID('dbo.testDML_NoClusteredIndex') IS NOT NULL
    DROP TABLE dbo.testDML_NoClusteredIndex;
GO

CREATE TABLE dbo.testDML_NoClusteredIndex (
    ID INT IDENTITY(1,1),
    SomeValue VARCHAR(50),
    CreationDate DATETIME DEFAULT GETDATE()
);
GO

INSERT INTO dbo.testDML_NoClusteredIndex (SomeValue)
SELECT TOP (100000) 'Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.all_objects a, sys.all_objects b;
GO

CREATE NONCLUSTERED INDEX IX_SomeValue ON dbo.testDML_NoClusteredIndex (SomeValue);
GO

SELECT COUNT(*) AS InitialRowCount FROM dbo.testDML_NoClusteredIndex;
GO

اکنون، حذف دسته‌ای را با استفاده از TOP (N) در یک حلقه WHILE انجام می‌دهیم:

DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = @BatchSize;
DECLARE @MaxIDToDelete INT;

SELECT @MaxIDToDelete = MAX(ID) FROM dbo.testDML_NoClusteredIndex WHERE ID BETWEEN 1 AND 50000;

WHILE (@RowsAffected = @BatchSize)
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.testDML_NoClusteredIndex
    WHERE ID <= @MaxIDToDelete;

    SET @RowsAffected = @@ROWCOUNT;
END;
GO

SELECT COUNT(*) AS RemainingRowCount FROM dbo.testDML_NoClusteredIndex;
GO

در این حالت، به دلیل عدم وجود ORDER BY صریح، بهینه‌ساز کوئری ممکن است از ایندکس غیرکلاستر IX_SomeValue یا حتی یک اسکن جدول استفاده کند. اگرچه TOP (@BatchSize) تضمین می‌کند که فقط تعداد مشخصی سطر حذف می‌شود، اما تضمینی برای حذف سطرها بر اساس ترتیب ID وجود ندارد، مگر اینکه ID ستون کلید کلاستر باشد. برای تضمین ترتیب، باید از ORDER BY صریح استفاده کرد. ترفند رایج برای ORDER BY بدون داشتن ستون مرتب‌سازی طبیعی، استفاده از عبارت زیر است:

این عبارت بهینه‌ساز را مجبور می‌کند تا سطرها را بدون ترتیب خاصی انتخاب کند، اما می‌تواند در ترکیب با TOP کار کند.

مثال ۳: استفاده از TOP (N) با ایندکس کلاستر

اگر جدول شما دارای ایندکس کلاستر باشد، حذف با TOP (N) بسیار کارآمدتر خواهد بود، زیرا سطرها به صورت فیزیکی بر اساس کلید کلاستر مرتب شده‌اند. ایندکس کلاستر، سطرها را به ترتیب منطقی و فیزیکی مرتب می‌کند و این به TOP کمک می‌کند تا به سرعت به سطرها دسترسی پیدا کند.

ابتدا، یک جدول با ایندکس کلاستر ایجاد می‌کنیم:

IF OBJECT_ID('dbo.testDML_ClusteredIndex') IS NOT NULL
    DROP TABLE dbo.testDML_ClusteredIndex;
GO

CREATE TABLE dbo.testDML_ClusteredIndex (
    ID INT IDENTITY(1,1),
    SomeValue VARCHAR(50),
    CreationDate DATETIME DEFAULT GETDATE(),
    CONSTRAINT PK_testDML_ClusteredIndex PRIMARY KEY CLUSTERED (ID)
);
GO

INSERT INTO dbo.testDML_ClusteredIndex (SomeValue)
SELECT TOP (100000) 'Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.all_objects a, sys.all_objects b;
GO

SELECT COUNT(*) AS InitialRowCount FROM dbo.testDML_ClusteredIndex;
GO

حذف دسته‌ای سطرها را با استفاده از TOP (N) انجام می‌دهیم:

DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = @BatchSize;
DECLARE @MinID BIGINT;

SELECT @MinID = MIN(ID) FROM dbo.testDML_ClusteredIndex WHERE ID BETWEEN 1 AND 50000;

WHILE (@RowsAffected = @BatchSize)
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.testDML_ClusteredIndex
    WHERE ID >= @MinID; -- استفاده از یک محدوده از ID برای حذف

    SET @RowsAffected = @@ROWCOUNT;
END;
GO

SELECT COUNT(*) AS RemainingRowCount FROM dbo.testDML_ClusteredIndex;
GO

با وجود ایندکس کلاستر بر روی ستون ID، عملیات DELETE TOP (@BatchSize) به صورت کارآمدتری سطرها را بر اساس ترتیب ID حذف می‌کند. ایندکس کلاستر جستجو و حذف سطرها را تسریع می‌بخشد، به خصوص در سناریوهایی که محدوده ID برای حذف مشخص شده است.

مثال ۴: استفاده از جدول موقت (Temporary Table) و TOP (N)

یکی دیگر از روش‌های قدرتمند برای پردازش دسته‌ای، استفاده از یک جدول موقت برای شناسایی شناسه‌های (IDs) سطرها در هر دسته است. این روش به ویژه زمانی مفید است که شرایط WHERE پیچیده‌تر باشد یا نیاز به Join با جداول دیگر برای تعیین سطرها برای حذف/آپدیت داشته باشید.

ابتدا، جدول تستی را با داده‌های نمونه ایجاد می‌کنیم:

IF OBJECT_ID('dbo.testDML_TempTable') IS NOT NULL
    DROP TABLE dbo.testDML_TempTable;
GO

CREATE TABLE dbo.testDML_TempTable (
    ID INT IDENTITY(1,1),
    SomeValue VARCHAR(50),
    CreationDate DATETIME DEFAULT GETDATE()
);
GO

INSERT INTO dbo.testDML_TempTable (SomeValue)
SELECT TOP (100000) 'Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.all_objects a, sys.all_objects b;
GO

CREATE NONCLUSTERED INDEX IX_SomeValue_Temp ON dbo.testDML_TempTable (SomeValue);
GO

SELECT COUNT(*) AS InitialRowCount FROM dbo.testDML_TempTable;
GO

اکنون، با استفاده از یک جدول موقت و TOP (N) سطرها را حذف می‌کنیم:

DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = @BatchSize;
DECLARE @MinID BIGINT;

SELECT @MinID = MIN(ID) FROM dbo.testDML_TempTable WHERE ID BETWEEN 1 AND 50000;

WHILE (@RowsAffected = @BatchSize)
BEGIN
    -- انتخاب IDهای دسته فعلی به یک جدول موقت
    SELECT TOP (@BatchSize) ID
    INTO #TempIDs
    FROM dbo.testDML_TempTable
    WHERE ID >= @MinID
    ORDER BY ID; -- مهم برای تضمین انتخاب IDهای صحیح

    -- حذف سطرها بر اساس IDهای موجود در جدول موقت
    DELETE T
    FROM dbo.testDML_TempTable T
    INNER JOIN #TempIDs TT ON T.ID = TT.ID;

    SET @RowsAffected = @@ROWCOUNT;

    -- حذف جدول موقت برای دسته بعدی
    DROP TABLE #TempIDs;
END;
GO

SELECT COUNT(*) AS RemainingRowCount FROM dbo.testDML_TempTable;
GO

این روش انعطاف‌پذیری بالایی دارد. با انتخاب IDها به یک جدول موقت (#TempIDs) و سپس استفاده از آن برای حذف یا به‌روزرسانی در جدول اصلی، می‌توانیم کنترل دقیقی بر روی عملیات دسته‌ای داشته باشیم. این کار امکان استفاده از شرایط پیچیده‌تر و JOINها را در مرحله انتخاب فراهم می‌کند.

مثال ۵: استفاده از TOP (N) با ایندکس کلاستر در یک جدول بزرگ و محدود کردن تراکنش‌ها

برای جداول بسیار بزرگ، کنترل دقیق بر روی تراکنش‌ها و مدیریت لاگ تراکنش حیاتی است. این مثال نشان می‌دهد که چگونه می‌توان عملیات حذف دسته‌ای را درون تراکنش‌های کوچک‌تر قرار داد و لاگ تراکنش را مدیریت کرد.

ابتدا، یک جدول بزرگ با ایندکس کلاستر ایجاد می‌کنیم:

IF OBJECT_ID('dbo.testDML_LargeTable') IS NOT NULL
    DROP TABLE dbo.testDML_LargeTable;
GO

CREATE TABLE dbo.testDML_LargeTable (
    ID BIGINT IDENTITY(1,1),
    SomeValue VARCHAR(50),
    CreationDate DATETIME DEFAULT GETDATE(),
    CONSTRAINT PK_testDML_LargeTable PRIMARY KEY CLUSTERED (ID)
);
GO

-- درج میلیون ها سطر
INSERT INTO dbo.testDML_LargeTable (SomeValue)
SELECT TOP (1000000) 'Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.all_objects a, sys.all_objects b, sys.all_objects c; -- برای تولید داده بیشتر
GO

SELECT COUNT(*) AS InitialRowCount FROM dbo.testDML_LargeTable;
GO

حذف دسته‌ای با کنترل تراکنش‌ها و مدیریت لاگ:

DECLARE @BatchSize INT = 10000; -- تعداد سطرها در هر دسته
DECLARE @TotalRowsToDelete INT = 500000; -- تعداد کل سطرهایی که قصد حذف آنها را داریم
DECLARE @RowsProcessed INT = 0;
DECLARE @CurrentMinID BIGINT;

-- ابتدا حداقل ID را برای شروع حذف مشخص می کنیم
SELECT @CurrentMinID = MIN(ID) FROM dbo.testDML_LargeTable;

-- حلقه اصلی برای پردازش دسته ای
WHILE (@RowsProcessed = @CurrentMinID
        ORDER BY ID ASC;

        -- اگر هیچ سطری انتخاب نشد، حلقه را بشکنید
        IF NOT EXISTS (SELECT 1 FROM #TempIDs_Large)
        BEGIN
            COMMIT TRAN;
            BREAK;
        END

        -- حذف سطرها بر اساس IDهای موجود در جدول موقت
        DELETE T
        FROM dbo.testDML_LargeTable T
        INNER JOIN #TempIDs_Large TT ON T.ID = TT.ID;

        SET @RowsProcessed = @RowsProcessed + @@ROWCOUNT;

        -- به روز رسانی حداقل ID برای دسته بعدی
        SELECT @CurrentMinID = MAX(ID) + 1 FROM #TempIDs_Large;

        COMMIT TRAN; -- Commit کردن تراکنش برای آزاد کردن منابع و اجازه برای تخلیه لاگ
        
        -- حذف جدول موقت
        DROP TABLE #TempIDs_Large;

        -- می توانید در اینجا یک تاخیر کوچک اضافه کنید تا فشار بر روی سیستم کمتر شود
        -- WAITFOR DELAY '00:00:00.010'; 

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN; -- در صورت بروز خطا، تراکنش را Rollback کنید
        
        -- مدیریت خطا - می توانید خطا را لاگ کنید یا دوباره پرتاب کنید
        PRINT ERROR_MESSAGE();
        BREAK;
    END CATCH
END;
GO

SELECT COUNT(*) AS RemainingRowCount FROM dbo.testDML_LargeTable;
GO

این اسکریپت با استفاده از تراکنش‌های صریح BEGIN TRAN و COMMIT TRAN، هر دسته را در یک تراکنش جداگانه اجرا می‌کند. این رویکرد به SQL Server اجازه می‌دهد تا پس از هر COMMIT، لاگ تراکنش را تخلیه (Truncate) کند (به شرطی که مدل ریکاوری پایگاه داده Full و لاگ تراکنش به صورت منظم با LOG_BACKUP پشتیبان‌گیری شود). این کار حجم فایل لاگ را کنترل کرده و از رشد بی‌رویه آن جلوگیری می‌کند. همچنین، در صورت بروز خطا، فقط دسته فعلی Rollback می‌شود که زمان بازیابی را به شدت کاهش می‌دهد.

توصیه می‌شود پس از چنین عملیات‌هایی، اگر فضای لاگ تراکنش زیادی اشغال شده باشد، پس از پشتیبان‌گیری از لاگ، با دستوراتی مانند:

اقدام به کوچک‌سازی فایل لاگ کنید تا فضای هارد دیسک آزاد شود. البته، این کار را باید با احتیاط و فقط در صورت لزوم انجام داد.