بهینه سازی عملکرد TSQL در SQL Server بهترین روشها

بهینه‌ سازی عملکرد T-SQL در SQL Server: بهترین روش‌ها و نکات کلیدی (بخش ۱)

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

۱. از SELECT * در SQL Server استفاده نکنید

یکی از اشتباهات رایج در نوشتن کوئری‌های SQL، استفاده از `SELECT *` برای بازگرداندن تمام ستون‌های یک جدول است. این کار به دلایل متعددی توصیه نمی‌شود و می‌تواند تأثیر منفی بر عملکرد داشته باشد:

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

همیشه فقط ستون‌هایی را که واقعاً نیاز دارید، انتخاب کنید.

-- توصیه نمی‌شود (NOT RECOMMENDED)
SELECT *
FROM Sales.SalesOrderDetail;

-- توصیه می‌شود (RECOMMENDED)
SELECT SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail;

۲. از پارامترها در کوئری‌های SQL Server استفاده کنید

استفاده از پارامترها در کوئری‌ها، یک روش بسیار مؤثر برای افزایش امنیت، بهبود کارایی و مدیریت بهتر برنامه‌های کاربردی دیتابیس است. این روش به خصوص در مواجهه با حملات SQL Injection و همچنین بهینه‌سازی استفاده از Execution Plan توسط SQL Server مفید است:

* **امنیت (جلوگیری از SQL Injection):** پارامترها به SQL Server کمک می‌کنند تا داده‌ها را از کد SQL جدا کند و از تزریق کدهای مخرب جلوگیری شود.
* **بازاستفاده از Execution Plan:** SQL Server می‌تواند Execution Plan را برای کوئری‌های پارامتردار کش (cache) کند و در فراخوانی‌های بعدی از آن استفاده کند. این کار باعث کاهش زمان کامپایل کوئری و بهبود چشمگیر عملکرد می‌شود.
* **کاهش کامپایل مجدد (Recompilation):** کوئری‌های غیرپارامتردار که مقادیر آنها مستقیماً در رشته کوئری قرار می‌گیرند، ممکن است باعث کامپایل مجدد Execution Plan در هر بار اجرا شوند که هزینه‌بر است.

-- توصیه نمی‌شود (استفاده از مقادیر مستقیم)
DECLARE @productID INT = 700;
SELECT SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID = 700;

-- توصیه می‌شود (استفاده از پارامتر)
DECLARE @productID INT = 700;
SELECT SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID = @productID;

۳. ایندکس‌های تکراری در SQL Server ایجاد نکنید

ایجاد ایندکس‌های تکراری (Duplicate Indexes) یا ایندکس‌هایی که اطلاعات مشابهی را پوشش می‌دهند، منابع سیستم را بیهوده مصرف می‌کند و می‌تواند عملکرد را کاهش دهد. اگرچه ایندکس‌ها برای بهبود سرعت بازیابی داده‌ها حیاتی هستند، اما باید با دقت مدیریت شوند:

* **مصرف فضای دیسک:** هر ایندکس فضای ذخیره‌سازی اضافی را اشغال می‌کند. ایندکس‌های تکراری، این فضا را به صورت غیرضروری افزایش می‌دهند.
* **افزایش زمان عملیات DML:** هنگام درج، به‌روزرسانی یا حذف داده‌ها (INSERT, UPDATE, DELETE)، SQL Server باید تمام ایندکس‌های مربوطه را نیز به‌روزرسانی کند. وجود ایندکس‌های تکراری، این عملیات را کندتر می‌کند.
* **پیچیدگی مدیریت:** تعداد زیاد ایندکس‌ها، نگهداری و عیب‌یابی پایگاه داده را دشوارتر می‌کند.

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

۴. از ساب‌کوئری‌ها در SQL Server اجتناب کنید

ساب‌کوئری‌ها (Subqueries) یا کوئری‌های تو در تو، در برخی موارد می‌توانند به کاهش کارایی کوئری کمک کنند، به خصوص ساب‌کوئری‌های Correlated که به ازای هر سطر از کوئری بیرونی یک بار اجرا می‌شوند. در بسیاری از موارد، می‌توان ساب‌کوئری‌ها را با JOINها یا CTEها (Common Table Expressions) جایگزین کرد که منجر به عملکرد بهتر می‌شود:

* **ساب‌کوئری‌های Correlated:** این نوع ساب‌کوئری‌ها به شدت بر عملکرد تأثیر می‌گذارند زیرا به ازای هر رکورد پردازش شده توسط کوئری اصلی، مجدداً اجرا می‌شوند.
* **بهینه‌سازی دشوارتر:** SQL Server در بهینه‌سازی کوئری‌هایی که شامل ساب‌کوئری‌های پیچیده هستند، ممکن است چالش‌هایی داشته باشد.

مثال زیر، یک ساب‌کوئری را با یک JOIN جایگزین می‌کند تا عملکرد را بهبود بخشد:

-- توصیه نمی‌شود (استفاده از ساب‌کوئری)
SELECT a.SalesOrderID, a.CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS a
WHERE a.LineTotal = (SELECT MAX(b.LineTotal) FROM Sales.SalesOrderDetail AS b WHERE a.ProductID = b.ProductID);

-- توصیه می‌شود (استفاده از INNER JOIN با CTE یا Derived Table)
WITH MaxLineTotals AS (
    SELECT ProductID, MAX(LineTotal) AS MaxTotal
    FROM Sales.SalesOrderDetail
    GROUP BY ProductID
)
SELECT a.SalesOrderID, a.CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS a
INNER JOIN MaxLineTotals AS m ON a.ProductID = m.ProductID AND a.LineTotal = m.MaxTotal;

اگر فرمولی در متن اصلی اشاره شده باشد که می‌بایست به شکل انگلیسی و در خط جداگانه آورده شود، مانند مثال فرضی زیر، به این شکل عمل می‌کنیم:

در برخی موارد، ساب‌کوئری‌ها، به ویژه ساب‌کوئری‌های همبسته (correlated subqueries) مانند نمونه زیر، می‌توانند عملکرد را به شدت کاهش دهند:

(SELECT COUNT(*) FROM Table_1 a WHERE a.cola = Table_2.cola)

این نوع ساب‌کوئری به ازای هر سطر از Table_2، یک بار اجرا می‌شود که می‌تواند بسیار کند باشد.

۵. از عملگر OR در SQL Server خودداری کنید

استفاده از عملگر `OR` در شرط `WHERE` می‌تواند مانع استفاده بهینه SQL Server از ایندکس‌ها شود و به اسکن کامل جدول (Table Scan) منجر شود که بسیار کندتر از جستجو با ایندکس است:

* **عدم استفاده از ایندکس:** در بسیاری از موارد، SQL Server نمی‌تواند از ایندکس‌ها به صورت مؤثر برای شرط‌های شامل `OR` استفاده کند، به خصوص اگر ایندکس‌ها روی ستون‌های مختلفی باشند.
* **افزایش زمان اجرا:** به دلیل اسکن کامل جدول، زمان اجرای کوئری به طور قابل توجهی افزایش می‌یابد.

در صورت امکان، عملگر `OR` را با `UNION ALL` یا `IN` جایگزین کنید تا SQL Server بتواند از ایندکس‌ها به شکل بهتری بهره‌مند شود.

-- توصیه نمی‌شود (استفاده از OR)
SELECT SalesOrderID, OrderQty, ProductID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = 700 OR OrderQty > 5;

-- توصیه می‌شود (جایگزینی با UNION ALL)
SELECT SalesOrderID, OrderQty, ProductID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = 700
UNION ALL
SELECT SalesOrderID, OrderQty, ProductID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID  700 AND OrderQty > 5;

-- یا در برخی موارد (اگر شرایط روی یک ستون است):
SELECT SalesOrderID, OrderQty, ProductID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID IN (700, 707);

۶. برای JOINها در SQL Server از INNER JOIN استفاده کنید و از WHERE بپرهیزید

با اینکه SQL Server می‌تواند جوین‌ها را چه در بند `FROM` (با استفاده از `INNER JOIN`) و چه در بند `WHERE` (با استفاده از `WHERE` clause) پردازش کند، اما استفاده از `INNER JOIN` در بند `FROM` به شدت توصیه می‌شود. این روش نه تنها خوانایی کد را افزایش می‌دهد بلکه یک روش استاندارد و مدرن برای نوشتن کوئری‌های SQL است:

* **خوانایی کد:** استفاده صریح از `INNER JOIN` مشخص می‌کند که هدف شما از ترکیب جداول چیست و شرایط جوین به وضوح از شرایط فیلتر جدا می‌شوند.
* **استاندارد SQL:** این روش با استانداردهای SQL مطابقت دارد و توسط اکثر سیستم‌های مدیریت پایگاه داده پشتیبانی می‌شود.
* **بهینه‌سازی:** SQL Server معمولاً در بهینه‌سازی جوین‌های صریح کارآمدتر عمل می‌کند، اگرچه در بسیاری از موارد، نتایج عملکردی بین این دو روش ممکن است یکسان باشد، اما استفاده از `INNER JOIN` بهترین رویکرد برای وضوح و نگهداری کد است.

-- توصیه نمی‌شود (جوين در بند WHERE)
SELECT p.Name, sod.OrderQty
FROM Production.Product p, Sales.SalesOrderDetail sod
WHERE p.ProductID = sod.ProductID;

-- توصیه می‌شود (جوين صریح در بند FROM)
SELECT p.Name, sod.OrderQty
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID;