مقایسه عملکرد INSERT SELECT و INSERT EXEC در SQL Server برای بهینه‌سازی

افزایش عملکرد کوئری‌های SQL Server: مقایسه INSERT…SELECT و INSERT EXEC

در این مقاله به بررسی تفاوت‌های عملکردی بین دستورات INSERT...SELECT و INSERT...EXEC در SQL Server می‌پردازیم. این مقایسه به شما کمک می‌کند تا در سناریوهای مختلف، از جمله کار با حجم بالای داده و رویه‌های ذخیره‌شده، بهترین گزینه را برای بهینه‌سازی عملکرد پایگاه داده خود انتخاب کنید. هدف این است که درک بهتری از زمان استفاده از هر یک از این روش‌ها برای بهبود کارایی و سرعت اجرای کوئری‌ها در SQL Server پیدا کنید.

INSERT…SELECT چیست؟

دستور INSERT...SELECT به شما امکان می‌دهد نتایج یک عبارت SELECT را مستقیماً در یک جدول دیگر (موقت یا دائمی) درج کنید. این روش برای انتقال داده‌ها از یک یا چند جدول به جدول دیگر بسیار کارآمد است و اغلب در سناریوهایی که نیاز به کپی‌برداری یا تجمیع داده‌ها بدون نیاز به منطق پیچیده میانی دارید، استفاده می‌شود. عملکرد INSERT...SELECT معمولاً در مقایسه با روش‌های دیگر، به‌ویژه برای عملیات‌های بزرگ، سریع‌تر است.

INSERT…EXEC چیست؟

دستور INSERT...EXEC برای درج نتایج یک رویه ذخیره‌شده (Stored Procedure) یا یک رشته T-SQL که یک عبارت SELECT را برمی‌گرداند، استفاده می‌شود. این روش زمانی مفید است که منطق پیچیده‌ای برای تولید داده‌ها وجود دارد که در یک رویه ذخیره‌شده کپسوله شده است. به عنوان مثال، اگر رویه ذخیره‌شده شما شامل فیلتر کردن، تجمیع یا پیوستن به چندین جدول باشد، می‌توانید از INSERT...EXEC برای درج خروجی آن به یک جدول دیگر استفاده کنید. با این حال، INSERT...EXEC معمولاً سربار بیشتری نسبت به INSERT...SELECT دارد و می‌تواند بر عملکرد کلی کوئری شما تأثیر بگذارد، به‌ویژه هنگام کار با جداول موقت.

تست تفاوت عملکرد بین INSERT…SELECT و INSERT…EXEC

برای درک بهتر تفاوت‌های عملکردی، اجازه دهید یک سناریوی عملی را با استفاده از SQL Server بررسی کنیم. ما یک جدول تستی و یک رویه ذخیره‌شده ساده ایجاد می‌کنیم و سپس عملکرد هر دو دستور INSERT...SELECT و INSERT...EXEC را اندازه‌گیری می‌کنیم.

تنظیمات اولیه

ابتدا، یک جدول موقت به نام #TestTable_A ایجاد می‌کنیم که در مثال‌های ما برای درج داده استفاده خواهد شد. همچنین، یک رویه ذخیره‌شده به نام usp_TestTable ایجاد می‌کنیم که داده‌ها را از جدول سیستمی sys.objects انتخاب کرده و یک جدول موقت دیگر به نام #TestTable_B را پر می‌کند. این رویه از برخی متغیرها برای شبیه‌سازی منطق کاری استفاده می‌کند.

کد ایجاد جدول و رویه ذخیره‌شده به شرح زیر است:

CREATE TABLE #TestTable_A (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255),
    create_date DATETIME
);

CREATE PROCEDURE usp_TestTable
    @i INT
AS
BEGIN
    CREATE TABLE #TestTable_B (
        id INT IDENTITY(1,1) PRIMARY KEY,
        name VARCHAR(255),
        create_date DATETIME
    );

    INSERT INTO #TestTable_B (name, create_date)
    SELECT
        o.name,
        o.create_date
    FROM sys.objects o
    WHERE o.object_id % @i = 0; -- Example filtering
END;
GO

حالا دو جدول تست موقت ایجاد می‌کنیم تا داده‌ها را در آن‌ها درج کنیم.

CREATE TABLE #Test_Table_Select (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255),
    create_date DATETIME
);

CREATE TABLE #Test_Table_Exec (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255),
    create_date DATETIME
);
GO

مثال INSERT…SELECT

در این مثال، داده‌ها را مستقیماً از sys.objects به #Test_Table_Select با استفاده از INSERT...SELECT درج می‌کنیم.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DELETE FROM #Test_Table_Select; -- Clean up before test

INSERT INTO #Test_Table_Select (name, create_date)
SELECT
    o.name,
    o.create_date
FROM sys.objects o;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

SELECT 'INSERT...SELECT Rows:', COUNT(*) FROM #Test_Table_Select;

مثال INSERT…EXEC

در این مثال، خروجی رویه ذخیره‌شده usp_TestTable را به #Test_Table_Exec با استفاده از INSERT...EXEC درج می‌کنیم. توجه داشته باشید که رویه ذخیره‌شده باید داده‌ها را به بیرون از خود برگرداند، نه اینکه آن‌ها را در یک جدول موقت داخلی درج کند.

-- Modified stored procedure for INSERT...EXEC compatibility
ALTER PROCEDURE usp_TestTable
    @i INT
AS
BEGIN
    SELECT
        o.name,
        o.create_date
    FROM sys.objects o
    WHERE o.object_id % @i = 0; -- Example filtering
END;
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DELETE FROM #Test_Table_Exec; -- Clean up before test

DECLARE @filter_param INT = 2; -- Example parameter for SP
INSERT INTO #Test_Table_Exec (name, create_date)
EXEC usp_TestTable @i = @filter_param;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

SELECT 'INSERT...EXEC Rows:', COUNT(*) FROM #Test_Table_Exec;

مقایسه نتایج

پس از اجرای کدهای بالا، می‌توانید خروجی SET STATISTICS IO ON و SET STATISTICS TIME ON را بررسی کنید. معمولاً متوجه خواهید شد که INSERT...SELECT دارای خواندن‌های منطقی (Logical Reads) و مدت زمان اجرای (Duration) کمتری است. این نشان می‌دهد که INSERT...SELECT به طور کلی کارآمدتر است.

نکات مهم دیگر

در هنگام استفاده از INSERT...EXEC، به خصوص با جداول موقت (Temporary Tables)، باید به چند نکته توجه داشت:

• سربار اضافه: INSERT...EXEC یک SQL batch جدید را آغاز می‌کند که می‌تواند منجر به سربار اضافی شود، به‌ویژه در محیط‌های با کارایی بالا.
• محدودیت‌های Scope: جداول موقتی که درون یک رویه ذخیره‌شده (که توسط INSERT...EXEC فراخوانی می‌شود) ایجاد می‌شوند، تنها در همان رویه ذخیره‌شده قابل دسترسی هستند و نمی‌توان خروجی آن‌ها را مستقیماً از طریق INSERT...EXEC به یک جدول خارجی درج کرد. باید رویه ذخیره‌شده خود را طوری تغییر دهید که نتایج را مستقیماً به عنوان خروجی SELECT برگرداند، همانطور که در مثال اصلاح شده بالا نشان دادیم.
• برنامه‌ریزی کوئری (Query Plan): INSERT...EXEC می‌تواند در مدیریت برنامه‌ریزی کوئری و برآورد هزینه (Cost Estimation) توسط بهینه‌ساز SQL Server پیچیدگی ایجاد کند.

به طور کلی، برای درج داده‌های حجیم که نیازی به منطق پیچیده یک رویه ذخیره‌شده ندارند، INSERT...SELECT گزینه بهتری برای بهینه‌سازی عملکرد کوئری است. اگر نیاز به استفاده از منطق پیچیده یک رویه ذخیره‌شده برای تولید داده دارید، INSERT...EXEC قابل استفاده است، اما باید از تأثیر آن بر عملکرد آگاه باشید و در صورت امکان رویه ذخیره‌شده را برای برگرداندن مستقیم نتایج بهینه کنید.