افزایش سرعت کوئری SQL Server با Sargable

بهبود عملکرد کوئری SQL Server با آرگومان‌های قابل جستجو (Sargable)

بهینه‌سازی عملکرد کوئری در SQL Server از جنبه‌های کلیدی مدیریت پایگاه داده است. یکی از مهم‌ترین مفاهیم برای دستیابی به این هدف، استفاده از آرگومان‌های قابل جستجو یا به اصطلاح “Sargable” است. یک آرگومان Sargable به SQL Server اجازه می‌دهد تا از ایندکس‌های موجود برای فیلتر کردن سریع و کارآمد داده‌ها بهره‌برداری کند، در نتیجه سرعت اجرای کوئری‌ها را به طور چشمگیری افزایش می‌دهد و منابع سیستم را بهینه‌تر مصرف می‌کند. در مقابل، آرگومان‌های غیر قابل جستجو (Non-Sargable) مانع استفاده از ایندکس‌ها می‌شوند و منجر به اسکن کامل جدول (Table Scan) می‌گردند که در مجموعه‌های داده بزرگ، بسیار کند و ناکارآمد است.

آرگومان‌های Sargable به گونه‌ای فرموله می‌شوند که SQL Server بتواند به سرعت و مستقیماً به ردیف‌های مرتبط با شرط WHERE دسترسی پیدا کند. این امر با اجتناب از اعمال هرگونه تابع یا عملیات بر روی ستون‌های ایندکس شده در سمت چپ اپراتور مقایسه (مانند `=`, `>`, `<`, `LIKE` و غیره) محقق می‌شود. در ادامه، به بررسی تفاوت بین کوئری‌های Sargable و Non-Sargable و نحوه تبدیل آن‌ها به فرم بهینه می‌پردازیم.

درک آرگومان‌های قابل جستجو (Sargable)

بیایید با یک مثال رایج شروع کنیم. فرض کنید جدولی به نام Orders داریم که ستونی به نام OrderDate دارد و روی آن ایندکس ایجاد شده است. هدف ما یافتن تمام سفارشات مربوط به سال 2020 است. در ابتدا ممکن است کوئری را به شکل زیر بنویسیم:

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE YEAR(OrderDate) = 2020;

در این کوئری، تابع YEAR() بر روی ستون OrderDate اعمال شده است. اگرچه از نظر منطقی نتیجه درستی می‌دهد، اما این کوئری “Non-Sargable” است. دلیل آن این است که SQL Server نمی‌تواند به طور مستقیم از ایندکس روی OrderDate استفاده کند. در عوض، مجبور است تابع YEAR() را برای تک تک ردیف‌های جدول Orders ارزیابی کند و سپس نتیجه را با 2020 مقایسه کند. این فرآیند منجر به اسکن کامل جدول می‌شود که برای جداول بزرگ بسیار ناکارآمد است.

برای تبدیل این کوئری به یک فرم “Sargable” و بهره‌برداری از ایندکس، باید شرط WHERE را به گونه‌ای بازنویسی کنیم که هیچ تابعی روی ستون ایندکس شده اعمال نشود. راه حل این است که به جای استخراج سال، ستون OrderDate را مستقیماً با یک بازه تاریخی مقایسه کنیم:

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2020-01-01' AND OrderDate < '2021-01-01';

این کوئری اصلاح شده “Sargable” است. SQL Server می‌تواند به سرعت با استفاده از ایندکس روی OrderDate، به اولین ردیف با تاریخ ‘2020-01-01’ دسترسی پیدا کرده (Index Seek) و سپس به صورت ترتیبی به سمت جلو حرکت کند تا به آخرین ردیف قبل از ‘2021-01-01’ برسد. این روش بسیار کارآمدتر از اسکن کامل جدول است و بهبود چشمگیری در عملکرد کوئری ارائه می‌دهد.

نمونه‌های رایج از کوئری‌های Non-Sargable و راه‌حل‌ها

علاوه بر استفاده از توابع تاریخ، الگوهای دیگری نیز وجود دارند که می‌توانند باعث Non-Sargable شدن کوئری‌ها شوند. در ادامه به برخی از این الگوها و نحوه بهینه‌سازی آن‌ها می‌پردازیم:

1. اعمال توابع بر روی ستون‌ها

هرگونه تابعی که به ستون ایندکس شده اعمال شود، باعث می‌شود کوئری Non-Sargable گردد. این شامل توابع رشته‌ای، توابع عددی و سایر توابع سیستمی می‌شود.

مثال Non-Sargable:

SELECT ProductID, ProductName
FROM Products
WHERE LEFT(ProductName, 1) = 'A';

در این حالت، تابع LEFT() مانع استفاده از ایندکس روی ProductName می‌شود.

راه حل Sargable:

SELECT ProductID, ProductName
FROM Products
WHERE ProductName LIKE 'A%';

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

2. تبدیل‌های ضمنی (Implicit Conversions)

زمانی که انواع داده‌ها در شرط WHERE با هم مطابقت ندارند، SQL Server ممکن است یک تبدیل ضمنی (Implicit Conversion) انجام دهد. این تبدیل‌ها می‌توانند باعث Non-Sargable شدن کوئری شوند.

مثال Non-Sargable:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE EmployeeID = '123';

اگر EmployeeID از نوع INT باشد، SQL Server سعی می‌کند ‘123’ (رشته) را به INT تبدیل کند که باعث Non-Sargable شدن کوئری می‌شود.

راه حل Sargable:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE EmployeeID = 123;

با اطمینان از مطابقت انواع داده‌ها (در اینجا استفاده از عدد صحیح)، از تبدیل ضمنی جلوگیری کرده و امکان استفاده از ایندکس را فراهم می‌کنیم.

3. استفاده از LIKE با وایلدکارد پیشرو

استفاده از وایلدکارد در ابتدای عبارت LIKE نیز باعث Non-Sargable شدن کوئری می‌شود، زیرا ایندکس‌ها برای جستجو از ابتدای رشته طراحی شده‌اند.

مثال Non-Sargable:

SELECT Description
FROM Products
WHERE Description LIKE '%ارد';

این کوئری باعث اسکن کامل جدول می‌شود. برای این نوع الگوها، سیستم‌های Full-Text Search یا ایندکس‌های خاص (در صورت پشتیبانی) گزینه‌های بهتری هستند، اما با ایندکس‌های استاندارد LIKE ‘%pattern’ همیشه Non-Sargable خواهد بود.

4. استفاده از OR در برخی شرایط

در برخی موارد، استفاده از اپراتور OR در شرط WHERE می‌تواند باعث Non-Sargable شدن کوئری شود، به خصوص اگر ستون‌ها دارای ایندکس‌های جداگانه باشند و SQL Server نتواند از Index Merge یا تکنیک‌های مشابه استفاده کند.

مثال Non-Sargable:

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE CustomerID = 101 OR TotalAmount > 500;

اگر CustomerID و TotalAmount هر دو ایندکس داشته باشند، ممکن است به جای استفاده از Index Seek، یک Table Scan انجام شود یا پلن اجرا پیچیده شود.

راه حل Sargable (جایگزین):

در مواردی که OR مشکل‌ساز است، می‌توان کوئری را به دو بخش جداگانه تقسیم کرد و آن‌ها را با UNION ALL ترکیب کرد. این روش در برخی سناریوها می‌تواند به SQL Server اجازه دهد تا از ایندکس‌ها به طور مؤثرتری استفاده کند.

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE CustomerID = 101

UNION ALL

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE TotalAmount > 500
  AND CustomerID  101; -- برای جلوگیری از تکرار نتایج در صورتی که نیاز به DISTINCT نباشد

این رویکرد به SQL Server اجازه می‌دهد تا دو عملیات Index Seek مستقل را انجام دهد و سپس نتایج را ترکیب کند که می‌تواند در مقایسه با یک Table Scan واحد، سریع‌تر باشد. نکته مهم این است که اگر احتمال تکرار نتایج وجود دارد و نیاز به حذف تکرارها دارید، به جای UNION ALL از UNION استفاده کنید (که عملیات اضافی DISTINCT را به همراه دارد).

برای اطمینان از بهترین عملکرد کوئری در SQL Server، همواره به دنبال ایجاد آرگومان‌های قابل جستجو (Sargable) در بند WHERE باشید. این امر با رعایت اصول زیر حاصل می‌شود:

• از اعمال توابع یا عملیات بر روی ستون‌های ایندکس شده در بند WHERE خودداری کنید. به جای آن، توابع را روی سمت راست اپراتور مقایسه (روی مقداری که با آن مقایسه می‌شود) اعمال کنید.
• از مطابقت انواع داده‌ها اطمینان حاصل کنید تا از تبدیل‌های ضمنی غیرضروری جلوگیری شود.
• در صورت استفاده از LIKE، وایلدکارد را در ابتدای رشته قرار ندهید (یعنی از ‘pattern%’ استفاده کنید، نه ‘%pattern’).
• پلن‌های اجرای کوئری (Execution Plans) را به دقت بررسی کنید تا هرگونه عملیات Table Scan یا Index Scan غیرضروری را شناسایی و رفع کنید.

با رعایت این نکات کلیدی، می‌توانید عملکرد کوئری‌های SQL Server خود را به طور قابل توجهی بهبود بخشیده و تجربه کاربری بهتری را فراهم آورید.