بهبود عملکرد SELECT INTO در SQL Server راهکارهای TSQL

بهبود چشمگیر عملکرد SELECT INTO در SQL Server: راهکارهای بهینه‌سازی T-SQL

دستور T-SQL SELECT INTO در SQL Server ابزاری قدرتمند برای ایجاد یک جدول جدید و درج نتایج یک کوئری در آن است. این دستور به ویژه زمانی مفید است که نیاز دارید یک جدول تازه از داده‌های موجود در یک یا چند جدول دیگر بسازید و نمی‌خواهید ساختار جدول را به صورت دستی و از پیش تعریف کنید. هدف این مقاله بررسی دقیق چندین روش برای استفاده از SELECT INTO و مقایسه عملکرد و کارایی هر رویکرد است تا به بهینه‌سازی پایگاه داده شما کمک کند.

برای ارزیابی و مقایسه عملکرد SELECT INTO و روش‌های جایگزین، ابتدا یک سناریوی آزمایشی با ایجاد دو جدول و درج داده در آنها فراهم می‌کنیم. این جداول شامل یک جدول اصلی `EmployeeTest` و یک جدول مقصد `EmployeeTestDestination` برای مقایسه ساختار خواهند بود.

برای راه‌اندازی محیط آزمایشی، کدهای زیر را اجرا کنید:

SET NOCOUNT ON;

IF OBJECT_ID('EmployeeTest') IS NOT NULL
  DROP TABLE EmployeeTest;

IF OBJECT_ID('EmployeeTestDestination') IS NOT NULL
  DROP TABLE EmployeeTestDestination;

CREATE TABLE EmployeeTest
(
  ID INT IDENTITY(1,1),
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  City VARCHAR(20),
  Designation VARCHAR(20),
  DateofJoining DATE
);
GO

INSERT INTO EmployeeTest
VALUES
('Jignesh','Thakkar','Mumbai','Developer','2013-05-12'),
('Mahesh','Thakkar','Mumbai','Developer','2013-05-12'),
('Jignesh','Thakkar','Mumbai','Developer','2013-05-12'),
('Chintan','Thakkar','Mumbai','Developer','2013-05-12'),
('Jignesh','Thakkar','Mumbai','Developer','2013-05-12'),
('Amit','Thakkar','Mumbai','Developer','2013-05-12');
GO

-- Insert 100,000 records
INSERT INTO EmployeeTest
SELECT FirstName, LastName, City, Designation, DateofJoining
FROM EmployeeTest;
GO 8

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

روش ۱: استفاده از SELECT INTO با ستون IDENTITY
این روش ساده‌ترین راه برای ایجاد یک جدول جدید و کپی کردن داده‌ها با استفاده از `SELECT INTO` است. ستون `IDENTITY` به طور خودکار در جدول مقصد بازسازی می‌شود و مقادیر جدیدی را اختصاص می‌دهد. این روش اغلب بسیار کارآمد است زیرا SQL Server بهینه‌سازی‌های داخلی را برای آن اعمال می‌کند.

کد زیر نحوه استفاده از `SELECT INTO` را نشان می‌دهد:

IF OBJECT_ID('EmployeeTestDestination') IS NOT NULL
  DROP TABLE EmployeeTestDestination;

SELECT ID, FirstName, LastName, City, Designation, DateofJoining
INTO EmployeeTestDestination
FROM EmployeeTest;
GO

روش ۲: استفاده از SELECT INTO با IDENTITY_INSERT ON
یکی از تصورات غلط رایج این است که می‌توان `IDENTITY_INSERT ON` را با `SELECT INTO` استفاده کرد. با این حال، دستور `IDENTITY_INSERT ON` فقط برای عملیات `INSERT` روی یک جدول *موجود* کاربرد دارد و نمی‌تواند با `SELECT INTO` که یک جدول *جدید* ایجاد می‌کند، استفاده شود. اگر سعی کنید این کار را انجام دهید، با خطای زیر مواجه خواهید شد:
`Msg 1934, Level 16, State 1, Line 1`
`SELECT INTO must be a new table or an existing empty table.`

این نشان می‌دهد که برای حفظ مقادیر اصلی ستون `IDENTITY`، نیاز به رویکرد متفاوتی دارید که ابتدا جدول را ایجاد کند.

روش ۳: استفاده از CREATE TABLE و سپس INSERT INTO با IDENTITY_INSERT ON
برای حفظ مقادیر ستون `IDENTITY` از جدول مبدا در جدول مقصد، باید ابتدا جدول مقصد را به صورت دستی با `CREATE TABLE` ایجاد کنید. سپس، با استفاده از `SET IDENTITY_INSERT ON` می‌توانید مقادیر `IDENTITY` را در عملیات `INSERT INTO` به صورت صریح درج کنید. این روش انعطاف‌پذیری بیشتری در کنترل مقادیر IDENTITY فراهم می‌کند.

کدهای زیر این فرآیند را نشان می‌دهند:

IF OBJECT_ID('EmployeeTestDestination') IS NOT NULL
  DROP TABLE EmployeeTestDestination;

CREATE TABLE EmployeeTestDestination
(
  ID INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  City VARCHAR(20),
  Designation VARCHAR(20),
  DateofJoining DATE
);
GO

SET IDENTITY_INSERT EmployeeTestDestination ON;
INSERT INTO EmployeeTestDestination (ID, FirstName, LastName, City, Designation, DateofJoining)
SELECT ID, FirstName, LastName, City, Designation, DateofJoining
FROM EmployeeTest;
GO

SET IDENTITY_INSERT EmployeeTestDestination OFF;

روش ۴: استفاده از CREATE TABLE، TRUNCATE و INSERT INTO با IDENTITY_INSERT ON
این روش شبیه به روش ۳ است، با این تفاوت که از دستور `TRUNCATE TABLE` برای خالی کردن سریع جدول مقصد قبل از درج داده‌ها استفاده می‌شود. `TRUNCATE TABLE` سریع‌تر از `DELETE` عمل می‌کند زیرا لاگ تراکنش کمتری تولید کرده و فضای دیسک را بلافاصله آزاد می‌کند. این گزینه برای سناریوهایی که جدول مقصد ممکن است حاوی داده‌های قدیمی باشد و نیاز به پاکسازی کامل قبل از درج مجدد باشد، بسیار مناسب است.

کدهای مربوط به این روش به شرح زیر است:

IF OBJECT_ID('EmployeeTestDestination') IS NOT NULL
  DROP TABLE EmployeeTestDestination;

CREATE TABLE EmployeeTestDestination
(
  ID INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  City VARCHAR(20),
  Designation VARCHAR(20),
  DateofJoining DATE
);
GO

TRUNCATE TABLE EmployeeTestDestination;
SET IDENTITY_INSERT EmployeeTestDestination ON;
INSERT INTO EmployeeTestDestination (ID, FirstName, LastName, City, Designation, DateofJoining)
SELECT ID, FirstName, LastName, City, Designation, DateofJoining
FROM EmployeeTest;
GO

SET IDENTITY_INSERT EmployeeTestDestination OFF;

روش ۵: استفاده از CREATE TABLE و سپس INSERT INTO بدون IDENTITY_INSERT ON
در این روش، نیز ابتدا جدول مقصد را با `CREATE TABLE` ایجاد می‌کنیم، اما ستون `ID` را به عنوان یک ستون `IDENTITY` تعریف می‌کنیم. سپس، در `INSERT INTO`، فقط ستون‌های غیر-`IDENTITY` را درج می‌کنیم. این کار باعث می‌شود که مقادیر `ID` در جدول مقصد به طور خودکار توسط SQL Server تخصیص یابد و با مقادیر اصلی جدول مبدا متفاوت خواهد بود. این رویکرد زمانی مناسب است که حفظ `ID`های اصلی اهمیت ندارد و فقط می‌خواهیم یک کپی از داده‌ها با `ID`های جدید ایجاد کنیم.

کدهای این رویکرد به صورت زیر است:

IF OBJECT_ID('EmployeeTestDestination') IS NOT NULL
  DROP TABLE EmployeeTestDestination;

CREATE TABLE EmployeeTestDestination
(
  ID INT IDENTITY(1,1),
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  City VARCHAR(20),
  Designation VARCHAR(20),
  DateofJoining DATE
);
GO

INSERT INTO EmployeeTestDestination (FirstName, LastName, City, Designation, DateofJoining)
SELECT FirstName, LastName, City, Designation, DateofJoining
FROM EmployeeTest;
GO

مقایسه عملکرد و کارایی روش‌ها
پس از اجرای این روش‌ها، مشاهده می‌شود که عملکرد SELECT INTO با ایجاد خودکار جدول مقصد (روش ۱) به طور قابل توجهی سریع‌تر از سایر روش‌ها است. این به دلیل بهینه‌سازی‌های داخلی SQL Server است که برای این دستور خاص اعمال می‌شود.

در مقایسه، روش‌هایی که نیاز به `CREATE TABLE` دستی و سپس `INSERT INTO` (حتی با `IDENTITY_INSERT ON` یا `TRUNCATE`) دارند، به زمان بیشتری برای اجرا نیاز دارند. این افزایش زمان به دلیل سربار عملیات اضافی مانند ایجاد جدول، مدیریت `IDENTITY_INSERT` و یا اجرای `TRUNCATE` است. به عنوان مثال، در یک تست با ۱۰۰,۰۰۰ رکورد، `SELECT INTO` (روش ۱) حدود ۰ ثانیه زمان می‌برد، در حالی که `CREATE TABLE` + `INSERT INTO` (روش ۳) حدود ۰.۵ ثانیه و `CREATE TABLE` + `TRUNCATE` + `INSERT INTO` (روش ۴) حدود ۰.۶ ثانیه زمان می‌برند. روش ۵ نیز با ۰.۵ ثانیه در رده میانی قرار می‌گیرد.

بنابراین، برای بهینه‌سازی عملکرد در ایجاد جداول جدید از نتایج کوئری‌ها، استفاده از `SELECT INTO` (روش ۱) توصیه می‌شود، مگر اینکه نیاز مبرمی به حفظ مقادیر اصلی ستون `IDENTITY` داشته باشید که در این صورت باید از روش‌های `CREATE TABLE` و `INSERT INTO` با `IDENTITY_INSERT ON` استفاده کنید. انتخاب روش مناسب بستگی به نیازهای خاص پروژه و اهمیت حفظ مقادیر IDENTITY دارد.