مقایسه عملکرد CTE Temp Table و Table Variable در SQL Server برای بهینه سازی

مقایسه عملکرد CTE، Temp Table و Table Variable در SQL Server: راهنمای بهینه‌سازی پرس‌وجوها

در دنیای توسعه‌دهندگان SQL Server، یکی از بحث‌های رایج و مهم، انتخاب بهترین روش برای مدیریت داده‌های موقت و پرس‌وجوهای پیچیده است. سه گزینه اصلی که اغلب برای این منظور مورد استفاده قرار می‌گیرند، Common Table Expressions (CTE)، Temp Tables (جداول موقت) و Table Variables (متغیرهای جدولی) هستند. انتخاب درست بین این سه، می‌تواند تأثیر چشمگیری بر عملکرد و کارایی کلی پایگاه داده شما داشته باشد.

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

تنظیمات پایگاه داده و جداول برای تست

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

ابتدا، یک پایگاه داده جدید به نام `TestDB` ایجاد می‌کنیم و سپس یک جدول با ساختار مشخص در آن ایجاد می‌کنیم. این جدول شامل ستون‌هایی برای ID، Name و Age خواهد بود.

CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE TestTable (
    Id INT IDENTITY(1,1),
    Name VARCHAR(100),
    Age INT
);
GO

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

ما از یک حلقه `WHILE` برای وارد کردن تعداد زیادی ردیف به جدول استفاده می‌کنیم. در هر تکرار حلقه، یک ردیف با مقادیر تصادفی برای `Name` و `Age` اضافه می‌شود. این رویکرد به ما امکان می‌دهد تا به راحتی حجم داده‌ها را برای تست‌های مختلف تنظیم کنیم.

برای تولید یک رشته تصادفی، از `NEWID()` و `LEFT(NEWID(), 8)` استفاده می‌کنیم که یک بخش از GUID تولید شده را به عنوان نام برمی‌گرداند. برای سن تصادفی، از `ABS(CHECKSUM(NEWID())) % 60 + 1` استفاده می‌کنیم که یک عدد بین ۱ تا ۶۰ تولید می‌کند.

-- برای وارد کردن 1000000 ردیف:
DECLARE @i INT = 1;
WHILE @i <= 1000000
BEGIN
    INSERT INTO TestTable (Name, Age)
    VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO

با این اسکریپت، می‌توانیم جدول `TestTable` را با حجم‌های مختلف داده (مثلاً ۱۰، ۱۰۰، ۱۰۰۰، ۱۰۰۰۰، ۱۰۰۰۰۰ و ۱۰۰۰۰۰۰ ردیف) پر کنیم تا تأثیر حجم داده بر عملکرد را بررسی کنیم. هر بار قبل از اجرای تست با حجم جدید، جدول را خالی می‌کنیم.

مثال Common Table Expression (CTE)

CTEها یک راه قدرتمند برای تعریف یک مجموعه نتیجه موقت نام‌گذاری شده هستند که می‌توانید در یک دستور SELECT، INSERT، UPDATE یا DELETE به آن ارجاع دهید. آن‌ها اغلب برای بهبود خوانایی پرس‌وجوهای پیچیده و همچنین برای پرس‌وجوهای بازگشتی استفاده می‌شوند.

در مثال زیر، ما یک CTE به نام `MyCTE` تعریف می‌کنیم که شامل تمام ردیف‌های `TestTable` است. سپس از این CTE برای انتخاب تمام داده‌ها استفاده می‌کنیم.

WITH MyCTE AS (
    SELECT Id, Name, Age
    FROM TestTable
)
SELECT Id, Name, Age FROM MyCTE;

مثال Temp Table (جدول موقت)

جداول موقت (Temp Tables) جداولی هستند که به صورت فیزیکی در پایگاه داده `tempdb` ایجاد می‌شوند و فقط برای مدت زمان جلسه فعلی یا تراکنش فعلی وجود دارند. آن‌ها برای ذخیره مجموعه‌های داده بزرگ و انجام عملیات پیچیده مانند JOINها و INDEXها بر روی آن‌ها بسیار مفید هستند.

در این مثال، ما یک جدول موقت به نام `#MyTempTable` ایجاد می‌کنیم، داده‌ها را از `TestTable` به آن وارد می‌کنیم و سپس از این جدول موقت برای انتخاب داده‌ها استفاده می‌کنیم. در پایان، جدول موقت را حذف می‌کنیم.

CREATE TABLE #MyTempTable (
    Id INT,
    Name VARCHAR(100),
    Age INT
);

INSERT INTO #MyTempTable (Id, Name, Age)
SELECT Id, Name, Age FROM TestTable;

SELECT Id, Name, Age FROM #MyTempTable;

DROP TABLE #MyTempTable;

مثال Table Variable (متغیر جدولی)

متغیرهای جدولی (Table Variables) شبیه به جداول موقت هستند اما به عنوان متغیر در حافظه تعریف می‌شوند. آن‌ها در محدوده یک بچ (batch) یا تابع/رویه ذخیره شده خاص وجود دارند. متغیرهای جدولی اغلب برای مجموعه‌های داده کوچکتر ترجیح داده می‌شوند زیرا سربار کمتری نسبت به جداول موقت دارند، اما نمی‌توانند ایندکس داشته باشند یا بخشی از تراکنش‌های بزرگ باشند.

در این مثال، ما یک متغیر جدولی به نام `@MyTableVariable` تعریف می‌کنیم، داده‌ها را از `TestTable` به آن وارد می‌کنیم و سپس از این متغیر جدولی برای انتخاب داده‌ها استفاده می‌کنیم.

DECLARE @MyTableVariable TABLE (
    Id INT,
    Name VARCHAR(100),
    Age INT
);

INSERT INTO @MyTableVariable (Id, Name, Age)
SELECT Id, Name, Age FROM TestTable;

SELECT Id, Name, Age FROM @MyTableVariable;

اسکریپت کامل تست عملکرد

برای مقایسه عملکرد، یک اسکریپت جامع طراحی شده است که هر یک از روش‌ها (CTE، Temp Table، Table Variable) را برای اندازه‌گیری زمان اجرای آن‌ها روی حجم‌های مختلف داده، اجرا می‌کند. این اسکریپت شامل مراحل زیر است:

1. پاک کردن داده‌های قبلی از `TestTable`.
2. وارد کردن تعداد مشخصی از ردیف‌ها به `TestTable`.
3. اجرای کوئری CTE و اندازه‌گیری زمان.
4. اجرای کوئری Temp Table و اندازه‌گیری زمان.
5. اجرای کوئری Table Variable و اندازه‌گیری زمان.

این فرآیند برای حجم‌های مختلف داده تکرار می‌شود تا تأثیر مقیاس‌پذیری هر روش مشاهده شود.

-- قبل از اجرای تست ها، مطمئن شوید که جدول TestTable در TestDB وجود دارد.
USE TestDB;
GO

SET STATISTICS TIME ON;
GO

-- حذف جدول موقت قبل از شروع تست (در صورت وجود)
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
GO

-- تست با 10 ردیف
TRUNCATE TABLE TestTable;
GO
DECLARE @i INT = 1;
WHILE @i <= 10
BEGIN
    INSERT INTO TestTable (Name, Age) VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO
PRINT '--- تست با 10 ردیف ---';
PRINT 'CTE:';
WITH MyCTE AS (SELECT Id, Name, Age FROM TestTable) SELECT Id, Name, Age FROM MyCTE;
PRINT 'Temp Table:';
CREATE TABLE #MyTempTable (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO #MyTempTable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM #MyTempTable;
DROP TABLE #MyTempTable;
PRINT 'Table Variable:';
DECLARE @MyTableVariable TABLE (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO @MyTableVariable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM @MyTableVariable;
GO

-- حذف جدول موقت قبل از شروع تست (در صورت وجود)
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
GO

-- تست با 100 ردیف
TRUNCATE TABLE TestTable;
GO
DECLARE @i INT = 1;
WHILE @i <= 100
BEGIN
    INSERT INTO TestTable (Name, Age) VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO
PRINT '--- تست با 100 ردیف ---';
PRINT 'CTE:';
WITH MyCTE AS (SELECT Id, Name, Age FROM TestTable) SELECT Id, Name, Age FROM MyCTE;
PRINT 'Temp Table:';
CREATE TABLE #MyTempTable (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO #MyTempTable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM #MyTempTable;
DROP TABLE #MyTempTable;
PRINT 'Table Variable:';
DECLARE @MyTableVariable TABLE (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO @MyTableVariable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM @MyTableVariable;
GO

-- حذف جدول موقت قبل از شروع تست (در صورت وجود)
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
GO

-- تست با 1000 ردیف
TRUNCATE TABLE TestTable;
GO
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO TestTable (Name, Age) VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO
PRINT '--- تست با 1000 ردیف ---';
PRINT 'CTE:';
WITH MyCTE AS (SELECT Id, Name, Age FROM TestTable) SELECT Id, Name, Age FROM MyCTE;
PRINT 'Temp Table:';
CREATE TABLE #MyTempTable (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO #MyTempTable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM #MyTempTable;
DROP TABLE #MyTempTable;
PRINT 'Table Variable:';
DECLARE @MyTableVariable TABLE (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO @MyTableVariable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM @MyTableVariable;
GO

-- حذف جدول موقت قبل از شروع تست (در صورت وجود)
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
GO

-- تست با 10000 ردیف
TRUNCATE TABLE TestTable;
GO
DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO TestTable (Name, Age) VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO
PRINT '--- تست با 10000 ردیف ---';
PRINT 'CTE:';
WITH MyCTE AS (SELECT Id, Name, Age FROM TestTable) SELECT Id, Name, Age FROM MyCTE;
PRINT 'Temp Table:';
CREATE TABLE #MyTempTable (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO #MyTempTable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM #MyTempTable;
DROP TABLE #MyTempTable;
PRINT 'Table Variable:';
DECLARE @MyTableVariable TABLE (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO @MyTableVariable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM @MyTableVariable;
GO

-- حذف جدول موقت قبل از شروع تست (در صورت وجود)
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
GO

-- تست با 100000 ردیف
TRUNCATE TABLE TestTable;
GO
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT INTO TestTable (Name, Age) VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO
PRINT '--- تست با 100000 ردیف ---';
PRINT 'CTE:';
WITH MyCTE AS (SELECT Id, Name, Age FROM TestTable) SELECT Id, Name, Age FROM MyCTE;
PRINT 'Temp Table:';
CREATE TABLE #MyTempTable (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO #MyTempTable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM #MyTempTable;
DROP TABLE #MyTempTable;
PRINT 'Table Variable:';
DECLARE @MyTableVariable TABLE (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO @MyTableVariable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM @MyTableVariable;
GO

-- حذف جدول موقت قبل از شروع تست (در صورت وجود)
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
GO

-- تست با 1000000 ردیف
TRUNCATE TABLE TestTable;
GO
DECLARE @i INT = 1;
WHILE @i <= 1000000
BEGIN
    INSERT INTO TestTable (Name, Age) VALUES (LEFT(NEWID(), 8), ABS(CHECKSUM(NEWID())) % 60 + 1);
    SET @i = @i + 1;
END;
GO
PRINT '--- تست با 1000000 ردیف ---';
PRINT 'CTE:';
WITH MyCTE AS (SELECT Id, Name, Age FROM TestTable) SELECT Id, Name, Age FROM MyCTE;
PRINT 'Temp Table:';
CREATE TABLE #MyTempTable (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO #MyTempTable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM #MyTempTable;
DROP TABLE #MyTempTable;
PRINT 'Table Variable:';
DECLARE @MyTableVariable TABLE (Id INT, Name VARCHAR(100), Age INT);
INSERT INTO @MyTableVariable (Id, Name, Age) SELECT Id, Name, Age FROM TestTable;
SELECT Id, Name, Age FROM @MyTableVariable;
GO

SET STATISTICS TIME OFF;
GO

نتایج آزمایش عملکرد

پس از اجرای اسکریپت تست برای حجم‌های مختلف داده، نتایج زیر به دست آمد که بینش‌های مهمی در مورد زمان اجرای هر روش ارائه می‌دهد. درک این نتایج برای بهینه‌سازی کوئری‌های SQL Server حیاتی است.

**برای تعداد ردیف‌های کم (10 تا 1000):**

• **Table Variable:** در این محدوده، متغیرهای جدولی معمولاً سریع‌ترین عملکرد را دارند. سربار کمتری برای ایجاد و مدیریت دارند و برای داده‌های کوچک مناسب هستند.
• **CTE:** CTEها نیز عملکرد بسیار خوبی از خود نشان می‌دهند و اغلب نزدیک به متغیرهای جدولی هستند. آن‌ها بهینه‌سازی می‌شوند و گاهی اوقات حتی توسط بهینه‌ساز SQL Server از بین می‌روند.
• **Temp Table:** جداول موقت به دلیل نیاز به ایجاد فیزیکی در `tempdb` و سربار ورودی/خروجی دیسک، کمی کندتر عمل می‌کنند، اما تفاوت ناچیز است.

**برای تعداد ردیف‌های متوسط (10000 تا 100000):**

• **Temp Table:** در این محدوده، جداول موقت شروع به برتری می‌کنند. توانایی آن‌ها در پشتیبانی از ایندکس‌ها (اگرچه در این تست ساده ایجاد نشده است) و مدیریت کارآمدتر داده‌ها در دیسک، به آن‌ها کمک می‌کند.
• **CTE:** عملکرد CTEها همچنان خوب است، اما با افزایش حجم داده، گاهی اوقات ممکن است کمی کندتر از جداول موقت شوند.
• **Table Variable:** متغیرهای جدولی با افزایش حجم داده‌ها شروع به کند شدن می‌کنند. عدم پشتیبانی از ایندکس و مدیریت تمام داده‌ها در حافظه می‌تواند باعث افت عملکرد شود.

**برای تعداد ردیف‌های بالا (1000000 و بیشتر):**

• **Temp Table:** جداول موقت به وضوح برنده هستند. با قابلیت ایجاد ایندکس‌های سفارشی و کنترل دقیق‌تر بر روی ذخیره‌سازی داده‌ها در `tempdb`، آن‌ها بهترین گزینه برای حجم‌های عظیم داده هستند.
• **CTE:** عملکرد CTE در این مقیاس، هرچند قابل قبول، اما به طور قابل توجهی از جداول موقت کندتر است.
• **Table Variable:** در این سطح از داده‌ها، متغیرهای جدولی به شدت کند می‌شوند و اغلب توصیه نمی‌شوند.

**خلاصه کلی عملکرد:**

• **CTE:** بهترین گزینه برای افزایش خوانایی و پیچیدگی پرس‌وجو، خصوصاً برای داده‌های کوچکتر تا متوسط. بهینه‌ساز پرس‌وجو می‌تواند آن‌ها را به خوبی مدیریت کند.
• **Temp Table:** مناسب‌ترین انتخاب برای حجم‌های بزرگ داده و زمانی که نیاز به ایندکس‌گذاری، آمار و قابلیت‌های تراکنش دارید. آن‌ها انعطاف‌پذیری بیشتری در بهینه‌سازی ارائه می‌دهند.
• **Table Variable:** ایده‌آل برای داده‌های بسیار کوچک (زیر چند هزار ردیف) و در زمانی که سربار کمتر و مدیریت ساده‌تر مد نظر است. برای حجم‌های بالا توصیه نمی‌شوند.

این نتایج نشان می‌دهد که انتخاب بین CTE، Temp Table و Table Variable بستگی زیادی به حجم داده‌هایی دارد که با آن‌ها کار می‌کنید و همچنین نیازهای خاص پرس‌وجوی شما. همیشه توصیه می‌شود که گزینه‌های مختلف را در محیط خود آزمایش کنید تا بهترین راه حل را برای افزایش کارایی SQL Server پیدا کنید.