۱۰ نکته و ترفند T-SQL برای بهینه سازی عملکرد SQL Server و کدنویسی

۱۰ نکته و ترفند T-SQL برای بهینه‌سازی عملکرد و کدنویسی در SQL Server

نکته ۱: ساده‌سازی زیرپرس‌وجوها با CROSS APPLY

برای ساده‌سازی پرس‌وجوها و بهبود خوانایی کد، به جای استفاده از زیرپرس‌وجوهای پیچیده، می‌توان از عملگر **CROSS APPLY** استفاده کرد. این روش به‌ویژه زمانی مفید است که زیرپرس‌وجو برای هر ردیف از پرس‌وجوی اصلی اجرا می‌شود و بهینه‌سازی عملکرد SQL Server را به ارمغان می‌آورد.

نمونه کد نامناسب:

SELECT
   o.OrderID,
   o.OrderDate,
   (SELECT CustomerName FROM Customers WHERE CustomerID = o.CustomerID) AS CustomerName
FROM
   Orders o;

نمونه کد بهینه با **CROSS APPLY**:

SELECT
   o.OrderID,
   o.OrderDate,
   c.CustomerName
FROM
   Orders o
CROSS APPLY
   (SELECT CustomerName FROM Customers WHERE CustomerID = o.CustomerID) c;

نکته ۲: استفاده از EXCEPT به جای NOT IN یا LEFT JOIN/IS NULL

هنگامی که نیاز دارید ردیف‌هایی را پیدا کنید که در یک جدول وجود دارند اما در جدول دیگر نیستند، استفاده از عملگر **EXCEPT** اغلب کارآمدتر و خواناتر از عبارات **NOT IN** یا ترکیب **LEFT JOIN** با **IS NULL** است. این روش به شما کمک می‌کند تا کوئری‌های کارآمدتری برای مقایسه داده‌ها بنویسید و عملکرد SQL Server را ارتقا دهید.

نمونه کد نامناسب:

SELECT ProductID FROM Products WHERE ProductID NOT IN (SELECT ProductID FROM OrderDetails);

نمونه کد بهینه با **EXCEPT**:

SELECT ProductID FROM Products
EXCEPT
SELECT ProductID FROM OrderDetails;

نکته ۳: ساده‌سازی دستورات CASE پیچیده با CHOOSE

در SQL Server 2012، تابع **CHOOSE** معرفی شد که می‌تواند دستورات **CASE** پیچیده را به طور قابل توجهی ساده کند. زمانی که نیاز دارید بر اساس یک مقدار عددی (اینتجر) یک مقدار را برگردانید، **CHOOSE** یک گزینه عالی برای نوشتن کد تمیزتر و کوتاه‌تر است.

نمونه کد نامناسب:

SELECT
    ProductName,
    CASE CategoryID
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Books'
        WHEN 3 THEN 'Clothing'
        ELSE 'Other'
    END AS CategoryName
FROM
    Products;

نمونه کد بهینه با **CHOOSE**:

SELECT
    ProductName,
    CHOOSE(CategoryID, 'Electronics', 'Books', 'Clothing') AS CategoryName
FROM
    Products;

نکته ۴: ساده‌سازی دستکاری‌های پیچیده رشته‌ای با CONCAT_WS

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

نمونه کد نامناسب:

SELECT
    FirstName + ', ' + MiddleName + ', ' + LastName AS FullName
FROM
    Customers
WHERE
    MiddleName IS NOT NULL; -- Handles NULLs

نمونه کد بهینه با **CONCAT_WS**:

SELECT
    CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName
FROM
    Customers;

نکته ۵: ساده‌سازی یافتن مقدار N-ام با OFFSET / FETCH NEXT

به جای استفاده از زیرپرس‌وجوهای پیچیده یا توابع پنجره‌ای مانند **ROW_NUMBER()** با یک شرط **WHERE**، می‌توانید از ترکیب **OFFSET** و **FETCH NEXT** (معرفی شده در SQL Server 2012) برای بازیابی آسان محدوده خاصی از ردیف‌ها یا ردیف N-ام استفاده کنید. این روش بهینه‌سازی کوئری و افزایش خوانایی کد را به دنبال دارد.

نمونه کد نامناسب:

SELECT
    *
FROM
    (
        SELECT
            ProductName,
            ROW_NUMBER() OVER (ORDER BY ProductID) AS rn
        FROM
            Products
    ) AS SubQuery
WHERE
    rn = 5; -- Get the 5th product

نمونه کد بهینه با **OFFSET / FETCH NEXT**:

SELECT
    ProductName
FROM
    Products
ORDER BY
    ProductID
OFFSET 4 ROWS
FETCH NEXT 1 ROW ONLY; -- Get the 5th product

نکته ۶: استفاده از انواع داده صحیح

استفاده از انواع داده (Data Types) مناسب برای ستون‌های جدول، برای عملکرد، فضای ذخیره‌سازی و یکپارچگی داده‌ها بسیار حیاتی است. به عنوان مثال، همیشه برای اعداد صحیح از **INT** به جای **NVARCHAR** استفاده کنید. انتخاب صحیح دیتا تایپ‌ها یکی از مهم‌ترین نکات برای بهینه‌سازی پایگاه داده SQL Server است.

نمونه کد نامناسب:

CREATE TABLE ProductsBad (
    ProductID NVARCHAR(50), -- Should be INT
    ProductName NVARCHAR(MAX),
    Price NVARCHAR(50) -- Should be DECIMAL or MONEY
);

نمونه کد بهینه با انواع داده صحیح:

CREATE TABLE ProductsGood (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(255),
    Price DECIMAL(10, 2)
);

نکته ۷: استفاده از sp_executesql به جای EXEC برای SQL پویا

برای اجرای SQL پویا (Dynamic SQL)، استفاده از **sp_executesql** به جای **EXEC** به شدت توصیه می‌شود. این رویه ذخیره‌شده امکان پارامترایز کردن کوئری‌ها را فراهم می‌کند که به جلوگیری از حملات **SQL Injection** کمک کرده و به SQL Server اجازه می‌دهد تا طرح‌های اجرایی (Execution Plans) را به طور موثرتری کش کند. این یک نکته حیاتی برای امنیت و بهینه‌سازی عملکرد کوئری‌های داینامیک است.

نمونه کد نامناسب:

DECLARE @tableName NVARCHAR(100) = 'Customers';
DECLARE @sqlBad NVARCHAR(MAX) = 'SELECT * FROM ' + @tableName + ' WHERE CustomerID = 1;';
EXEC(@sqlBad);

نمونه کد بهینه با **sp_executesql**:

DECLARE @tableName NVARCHAR(100) = 'Customers';
DECLARE @customerID INT = 1;
DECLARE @sqlGood NVARCHAR(MAX) = 'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE CustomerID = @pCustomerID;';
EXEC sp_executesql @sqlGood, N'@pCustomerID INT', @pCustomerID = @customerID;

نکته ۸: استفاده از یک کوئری واحد برای درج چندین ردیف

به جای اجرای چندین دستور **INSERT** برای هر ردیف، می‌توانید با استفاده از یک دستور **INSERT** واحد به همراه چندین بند **VALUES**، چندین ردیف را به صورت همزمان وارد جدول کنید. این روش به شدت بهینه‌سازی عملکرد عملیات وارد کردن داده‌ها را به دنبال دارد و زمان اجرای کوئری را در SQL Server کاهش می‌دهد.

نمونه کد نامناسب:

INSERT INTO Products (ProductName, Price) VALUES ('Laptop', 1200.00);
INSERT INTO Products (ProductName, Price) VALUES ('Mouse', 25.00);
INSERT INTO Products (ProductName, Price) VALUES ('Keyboard', 75.00);

نمونه کد بهینه برای درج چند ردیف:

INSERT INTO Products (ProductName, Price) VALUES
('Laptop', 1200.00),
('Mouse', 25.00),
('Keyboard', 75.00);

نکته ۹: استفاده از متغیرهای جدولی به جای جداول موقت

برای مجموعه‌های داده کوچک‌تر، به ویژه در رویه‌های ذخیره‌شده (Stored Procedures) یا بچ‌ها، استفاده از متغیرهای جدولی (**DECLARE @tableVariable TABLE (…)**) اغلب به جداول موقت (**CREATE TABLE #tempTable (…)**) ترجیح داده می‌شود. متغیرهای جدولی بازکامپایل‌ها را کاهش می‌دهند و منابع کمتری مصرف می‌کنند، که به بهبود عملکرد و مدیریت منابع در SQL Server کمک می‌کند.

نمونه کد نامناسب با جدول موقت:

CREATE TABLE #TempCustomers (
    CustomerID INT,
    CustomerName NVARCHAR(255)
);

INSERT INTO #TempCustomers (CustomerID, CustomerName)
SELECT CustomerID, CustomerName FROM Customers WHERE CustomerID < 10;

SELECT * FROM #TempCustomers;

DROP TABLE #TempCustomers;

نمونه کد بهینه با متغیر جدولی:

DECLARE @CustomersTable TABLE (
    CustomerID INT,
    CustomerName NVARCHAR(255)
);

INSERT INTO @CustomersTable (CustomerID, CustomerName)
SELECT CustomerID, CustomerName FROM Customers WHERE CustomerID < 10;

SELECT * FROM @CustomersTable;

نکته ۱۰: ساده‌سازی تولید داده با CTEهای بازگشتی

عبارات جدول مشترک بازگشتی (**Recursive CTEs**) ابزاری قدرتمند برای کار با داده‌های سلسله‌مراتبی یا تولید مجموعه‌ای از داده‌ها مانند یک محدوده تاریخی هستند. این قابلیت به شما امکان می‌دهد تا کوئری‌های پیچیده را به بخش‌های قابل مدیریت تقسیم کرده و به سادگی، به عنوان مثال، یک لیست از تاریخ‌ها را تولید کنید که برای گزارش‌گیری و تجزیه و تحلیل داده‌ها در SQL Server بسیار مفید است.

نمونه کد برای تولید محدوده تاریخ با Recursive CTE:

WITH DateRange AS (
    SELECT CAST('2023-01-01' AS DATE) AS CalendarDate
    UNION ALL
    SELECT DATEADD(day, 1, CalendarDate)
    FROM DateRange
    WHERE CalendarDate < '2023-01-31'
)
SELECT CalendarDate FROM DateRange;