مقایسه عملکرد MERGE با INSERT UPDATE DELETE در SQL Server

مقایسه عملکرد MERGE در SQL Server: آیا بهتر از INSERT, UPDATE و DELETE است؟

SQL MERGE در SQL Server 2008 معرفی شد و برای انجام عملیات INSERT، UPDATE یا DELETE بر روی یک جدول هدف، بر اساس نتایج یک جوین با جدول منبع به کار می‌رود. این دستور راهی مناسب برای ترکیب این سه عملیات در یک دستور واحد ارائه می‌دهد که کدنویسی را ساده‌تر کرده و تعداد رفت‌وبرگشت‌های شبکه را در مقایسه با اجرای دستورات جداگانه کاهش می‌دهد.

بحث‌های زیادی در مورد عملکرد MERGE در مقایسه با دستورات تکی INSERT، UPDATE و DELETE وجود داشته است. در این مقاله، با اجرای چند آزمایش، عملکرد دستور MERGE در برابر دستورات جداگانه INSERT، UPDATE و DELETE بررسی خواهد شد.

ساختار کلی دستور MERGE به شکل زیر است، جایی که می‌توانید شرایط مختلف برای درج، به‌روزرسانی یا حذف را تعریف کنید:

MERGE TargetTable AS T
USING SourceTable AS S
ON T.PrimaryKey = S.PrimaryKey
WHEN MATCHED THEN
    UPDATE SET T.Column1 = S.Column1
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Column1, Column2) VALUES (S.Column1, S.Column2)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

این ساختار امکان مدیریت همزمان تغییرات را در یک عملیات واحد فراهم می‌آورد و از این رو برای همگام‌سازی داده‌ها بسیار کارآمد است.

تنظیم محیط برای آزمایش

برای این آزمایش‌ها، از SQL Server 2022 Developer Edition استفاده شده است. شما می‌توانید از هر نسخه‌ای از SQL Server 2008 به بعد برای اجرای این تست‌ها بهره ببرید.

ابتدا، پایگاه داده جدیدی به نام SQLMERGEDEMO ایجاد می‌کنیم.

CREATE DATABASE SQLMERGEDEMO;

پس از ایجاد پایگاه داده، دستور GO را برای جدا کردن دسته‌ها اجرا می‌کنیم. GO یک دستور T-SQL نیست بلکه فرمانی است که ابزارهای SQL Server مانند SQL Server Management Studio آن را تشخیص می‌دهند و بلوک‌های کد را جداگانه به سرور می‌فرستند.

GO

اکنون، جدول‌های SourceTable و TargetTable را در این پایگاه داده ایجاد می‌کنیم.

USE SQLMERGEDEMO;

GO

CREATE TABLE SourceTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

CREATE TABLE TargetTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

ما جدول‌ها را با یک میلیون رکورد برای آزمایش پر می‌کنیم.

INSERT INTO SourceTable WITH (TABLOCK) (ID, Value)
SELECT TOP 1000000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
    'Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

در هنگام درج داده‌ها، از WITH (TABLOCK) استفاده می‌شود. این دستور به SQL Server می‌گوید که یک قفل جدول (Table Lock) بر روی SourceTable اعمال کند که می‌تواند درج‌های انبوه را برای حجم زیادی از داده‌ها سریع‌تر کند، زیرا نیاز به قفل‌های ردیفی کمتری است و از سربار مدیریت قفل‌ها کاسته می‌شود.

WITH (TABLOCK)

INSERT INTO TargetTable WITH (TABLOCK) (ID, Value)
SELECT TOP 500000
    ID, Value
FROM SourceTable
WHERE ID <= 500000;

برای آماده‌سازی آزمایش‌ها، داده‌ها را در SourceTable به گونه‌ای تغییر می‌دهیم که:

• ۵۰,۰۰۰ رکورد جدید باشند (برای درج در TargetTable).
• ۵۰,۰۰۰ رکورد به‌روزرسانی شوند (رکوردهای منطبق در TargetTable، اما با مقادیر متفاوت).
• ۵۰,۰۰۰ رکورد حذف شوند (موجود در TargetTable اما نه در SourceTable).

INSERT INTO SourceTable WITH (TABLOCK) (ID, Value)
SELECT TOP 50000
    ID + 1000000, 'New Value ' + CAST(ID AS VARCHAR(10))
FROM SourceTable
WHERE ID <= 50000;

برای تولید رکوردهای جدیدی که در TargetTable وجود ندارند، به شناسه‌های موجود در SourceTable یک میلیون اضافه می‌کنیم تا از تکرار جلوگیری شود و اینها به عنوان رکوردهای ‘جدید’ عمل کنند که باید درج شوند.

UPDATE SourceTable WITH (TABLOCK)
SET Value = 'Updated Value ' + CAST(ID AS VARCHAR(10))
WHERE ID BETWEEN 1 AND 50000;

در اینجا، رکوردهایی با ID بین ۱ تا ۵۰,۰۰۰ در SourceTable به‌روزرسانی می‌شوند. این رکوردها در TargetTable نیز وجود دارند و قرار است در مرحله بعدی توسط عملیات MERGE یا UPDATE به‌روز شوند.

DELETE FROM SourceTable WITH (TABLOCK)
WHERE ID BETWEEN 450001 AND 500000;

این بخش رکوردهایی با ID بین ۴۵۰,۰۰۱ تا ۵۰۰,۰۰۰ را از SourceTable حذف می‌کند. این رکوردها در TargetTable باقی می‌مانند و قرار است توسط عملیات MERGE یا DELETE حذف شوند، زیرا دیگر در منبع وجود ندارند.

آزمایش INSERT، UPDATE و DELETE

ابتدا، عملکرد دستورات جداگانه INSERT، UPDATE و DELETE را آزمایش می‌کنیم.

برای ثبت زمان‌های اجرا، آمار (Statistics) را فعال می‌کنیم.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

ابتدا، رکوردهایی را از TargetTable حذف می‌کنیم که در SourceTable نیستند.

DELETE T
FROM TargetTable T
LEFT JOIN SourceTable S ON T.ID = S.ID
WHERE S.ID IS NULL;

سپس، رکوردهایی را در TargetTable به‌روزرسانی می‌کنیم که با SourceTable منطبق هستند اما مقادیر متفاوتی دارند.

UPDATE T
SET T.Value = S.Value
FROM TargetTable T
INNER JOIN SourceTable S ON T.ID = S.ID
WHERE T.Value <> S.Value;

در نهایت، رکوردهای جدید را از SourceTable در TargetTable درج می‌کنیم.

INSERT INTO TargetTable (ID, Value)
SELECT S.ID, S.Value
FROM SourceTable S
LEFT JOIN TargetTable T ON S.ID = T.ID
WHERE T.ID IS NULL;

پس از اجرای این دستورات، زمان‌های اجرا را بررسی خواهیم کرد. در سیستم من، این مجموعه عملیات تقریباً ۳ تا ۴ ثانیه زمان برد.

آزمایش MERGE

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

مطمئن می‌شویم که آمار (Statistics) همچنان فعال هستند.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

قبل از اجرای MERGE، باید TargetTable را به حالت اولیه خود (قبل از آزمایش IUD) بازگردانیم تا مقایسه‌ای عادلانه داشته باشیم. این کار را با حذف و بازسازی TargetTable و پر کردن مجدد آن با ۵۰۰,۰۰۰ رکورد اولیه انجام می‌دهیم.

DROP TABLE IF EXISTS TargetTable;
CREATE TABLE TargetTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);
INSERT INTO TargetTable WITH (TABLOCK) (ID, Value)
SELECT TOP 500000
    ID, Value
FROM SQLMERGEDEMO.dbo.SourceTable
WHERE ID <= 500000;

اکنون، دستور MERGE را اجرا می‌کنیم.

MERGE TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED AND T.Value <> S.Value THEN
    UPDATE SET T.Value = S.Value
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Value) VALUES (S.ID, S.Value)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

در سیستم من، دستور MERGE تقریباً ۵ تا ۶ ثانیه زمان برد.