Що робить ваш запит не-SARGable

Терміни, які роблять запит не SARGable

Це кілька факторів, що роблять запит несумісним з індексами.

1. Використання стовпця всередині функції в WHERE клаузі.

select  
 s.SalesOrderID,  
 s.SalesPersonID,  
 s.OrderDate,  
 s.SalesOrderNumber  
from Sales.SalesOrderHeader s  
where YEAR(s.OrderDate) = 2011; -- Кількість рядків: 1607, Кількість прочитаних рядків: 31465

Примітка: Я створив покриваючий індекс на OrderDate:

create nonclustered index IX_Sale_OrderDate on Sales.SalesOrderHeader(OrderDate)  
Include  
(  
 SalesOrderID,  
 SalesPersonID,  
 SalesOrderNumber  
);

Розглянемо план виконання:

Є всього 1607 рядків для 2011 року, але загальна кількість прочитаних рядків 31465. Давайте поясню, що сталося. OrderDate має некластеризований індекс, але база даних не оптимізує його, і цей запит буде читати всі рядки з таблиці. Причина в тому, що функція YEAR оцінюється для кожного рядка, що викликає сканування некластеризованого індексу (сканується весь індекс, який містить всі необхідні стовпці завдяки покриваючому індексу). Якщо ви не розумієте різницю між скануванням і пошуком, я раджу вам ознайомитись з цією статтею.

Ми можемо переписати запит для оптимізації індексу на OrderDate.

select  
 s.SalesOrderID,  
 s.SalesPersonID,  
 s.OrderDate,  
 s.SalesOrderNumber  
from Sales.SalesOrderHeader s  
where s.OrderDate>= '2011-01-01' and s.OrderDate<='2011-12-31'; -- Кількість рядків: 1607, Кількість прочитаних рядків: 1607

Тепер цей запит оптимізує індекс.

2. Використання провідних символів у ключовому слові LIKE

SELECT  
 b.*  
FROM Book b  
WHERE b.Title Like '%fairy%';

Запит, визначений вище, не використовує індекс, що визначений на стовпці ‘Title’. Він виконує «сканування індексу» і читає всі рядки. Це відбувається, тому що ми повинні відсканувати всі рядки, де заголовок містить рядок ‘fairy’.

Перепишемо запит:

SELECT  
 b.*  
FROM Book b  
WHERE b.Title Like 'fairy%';

Цей запит використовує індекс і читає лише відповідні рядки. Оскільки в таблиці ‘Book’ є лише 1 запис, що починається з заголовка ‘fairy’, буде прочитано лише 1 рядок.

3. Неявні перетворення (з боку таблиці)

Коли ми порівнюємо два значення з різними типами даних, SQL Server перетворює тип даних з нижчим пріоритетом на тип з вищим пріоритетом. Розглянемо приклад нижче (я взяв його з статті mssqltips і використовую базу даних AdventureWorks2022).

-- Примітка: `CardNumber` має тип `nvarchar`, є індекс на `CardNumber`  
-- і ми порівнюємо його з цілим числом 11119775847802  
SELECT  
 CreditCardID,  
 CardNumber  
FROM Sales.CreditCard  
WHERE CardNumber = 11119775847802;

Тут CardNumber має тип nvarchar, а ми порівнюємо його з цілим числом. CardNumber має індекс. Є лише один запис з CardNumber=11119775847802, тому повинно прочитати лише 1 рядок для отримання цього запису. Але цього не відбувається. База даних SQL Server читає всі рядки, щоб знайти цей запис.
Ви питаєте чому? Дозвольте пояснити.

CardNumber має тип nvarchar, а ми шукаємо запис з CardNumber = 11119775847802, який має тип ціле число. Замість того, щоб перетворити 11119775847802 на nvarchar, SQL Server перетворює всі рядки CardNumber на ціле число, що призводить до сканування індексу. Ви розумієте проблему? Нам потрібен лише один запис, у якому CardNumber дорівнює 11119775847802, але для цього сканується вся таблиця.

Отже, використовуйте ‘11119775847802’ замість 11119775847802, що дозволяє уникнути неявного перетворення на стороні таблиці, як показано нижче:

SELECT  
 CreditCardID,  
 CardNumber  
FROM Sales.CreditCard  
WHERE CardNumber = '11119775847802';

Цей запит прочитає лише 1 рядок і ефективно використовуватиме індекс.

Примітка: Іноді SQL Server розумно обробляє неявне перетворення і уникає перетворення на стороні таблиці, як у цьому прикладі:

SELECT  
 soh.SalesOrderID,  
 soh.SalesOrderNumber,  
 soh.ModifiedDate  
FROM Sales.SalesOrderHeader soh  
WHERE soh.SalesOrderID= '43668';

Запит вище не виконує неявне перетворення на стороні таблиці. Замість того, щоб перетворювати всі SalesOrderId на nvarchar, він просто перетворює '43668' на ціле число.

Я протестував кілька запитів, де порівнював значення рядка (наприклад, ‘43668’) з цілими числами (наприклад, SalesOrderID). Що я знайшов, так це те, що SQL Server обробляє це дуже добре і перетворює лише порівнюване значення, а не всі рядки. Це призводить до пошуку індексу.

Отже, іноді оптимізатор SQL також оптимізує процеси.

4. Використання необов’язкового параметра (where column_name=@some_parameter or @some_parameter is NULL)

Я є жертвою цього. Я робив це все своє життя, коли писав сирі SQL запити (хоча більшість часу я використовував EF). І я навіть не усвідомлював, що це не найкращий запит.

Розглянемо приклад:

create or alter procedure GetBooks  
 @SearchTerm nvarchar(40)=null  
as  
begin  
 set nocount on;  

 select * from Book  
 where @SearchTerm is null or Title like @SearchTerm+'%';  
end  
go  

-- виконуємо процедуру  
exec GetBooks @SearchTerm='The Epic Of Gilgamesh'; -- повертає 1 рядок

Вищезгаданий запит не використовуватиме індекс на Title і в результаті призведе до index scan (сканування всіх рядків).

Ми можемо вирішити це двома способами:

(i). Використання динамічного SQL

create or alter procedure GetBooks  
 @SearchTerm nvarchar(40)=null  
as  
begin  

 set nocount on;  

 declare @SQL nvarchar(max);  

 set @SQL = N'SELECT * FROM Book WHERE 1 = 1';  

 if @SearchTerm is not null  
 begin  
 set @SearchTerm = @SearchTerm+'%';  
 set @SQL += N' AND Title LIKE @SearchTerm';  
 end  

 exec sp_executesql @SQL, N'@SearchTerm nvarchar(40)', @SearchTerm;  
end  
go  

-- виконуємо процедуру  

exec GetBooks @SearchTerm='The Epic Of Gilgamesh'; -- повертає 1 рядок

(ii). Використання OPTION (RECOMPILE)

create or alter procedure GetBooks  
 @SearchTerm nvarchar(40)=null  
as  
begin  
 set nocount on;  

 select * from Book  
 where @SearchTerm is null or Title like @SearchTerm+'%'  
 option (RECOMPILE);  
end  

-- виконуємо процедуру  
exec GetBooks @SearchTerm='The Epic Of Gilgamesh'; -- повертає 1 рядок

RECOMPILE може збільшити використання CPU. У більшості випадків це не буде великою проблемою. Якщо це так, то слід розглянути використання динамічного SQL. Але спочатку зверніть увагу на RECOMPILE. Якщо запит має довгий час компіляції і часто виконується, тоді можна використовувати динамічний SQL. Це детально пояснюється в статті Gail, рекомендую ознайомитись, вона має чудову глибину.
Використання OR в умовах WHERE

SELECT  
 SalesOrderID,  
 OrderDate,  
 SalesOrderNumber,  
 CustomerID  
FROM  
Sales.SalesOrderHeader  
WHERE  
 CustomerID= 29642  
 OR  
 OrderDate < '2011-10-31';

У таблиці Sales.SalesOrderHeader, стовпець CustomerId є первинним ключем. І я створив покритий індекс для OrderDate (SalesOrderID, CustomerID).

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OrderDate_Covering  
ON Sales.SalesOrderHeader (OrderDate)  
INCLUDE (SalesOrderID, CustomerID)

План виконання:

Цей запит шукає два різні умови і в кінцевому підсумку призводить до сканування індексу.

👉 Одне з рішень — використовувати підказку FORCESEEK, як згадано тут:

SELECT  
 SalesOrderID,  
 OrderDate,  
 SalesOrderNumber,  
 CustomerID  
FROM  
Sales.SalesOrderHeader  
WITH (FORCESEEK)  
WHERE  
 CustomerID= 29642  
 OR  
 OrderDate < '2011-10-31';

👉 Інше рішення — використовувати union:

SELECT  
 SalesOrderID,  
 OrderDate,  
 SalesOrderNumber,  
 CustomerID  
FROM  
Sales.SalesOrderHeader  
WHERE CustomerID= 29642  

UNION  

SELECT  
 SalesOrderID,  
 OrderDate,  
 SalesOrderNumber,  
 CustomerID  
FROM  
Sales.SalesOrderHeader  
WHERE OrderDate < '2011-10-31';

Ще одна річ

Це не зовсім стосується індексу, але має відношення до продуктивності. Не використовуйте OR разом з JOIN. Особисто я ніколи не стикався з цією ситуацією, але я знайшов її тут під час дослідження для цієї статті. Я змоделював ситуацію і представляю її вам.

SELECT DISTINCT c.name, o.order_id, o.amount, o.status  
FROM customers c  
LEFT JOIN orders o  
ON c.id = o.customer_id  
OR c.email = o.customer_email

План виконання:

Статистика:

Цей запит виконався дуже повільно, навіть при тому, що в кожній таблиці лише 10 тис. записів.

Ми можемо вирішити цю ситуацію за допомогою union.

SELECT c.name, o.order_id, o.amount, o.status  
FROM customers c  
LEFT JOIN orders o ON c.id = o.customer_id  

UNION  

SELECT c.name, o.order_id, o.amount, o.status  
FROM customers c  
left JOIN orders o ON c.email = o.customer_email

Цей запит виконався значно швидше і з меншим числом логічних зчитувань.

Підсумок

Жоден із цих пунктів не призводить до сканування таблиці завжди. Це трапляється лише тоді, коли у вас великий набір даних (я маю на увазі тисячі записів). Оптимізатор SQL Server досить розумний, щоб оптимізувати запити (ось чому його і найняли). Але завжди корисно слідувати цим рекомендаціям.

Підсумуємо, не робіть наступне:

• where somefunction(somecolumn)= some_value.
• where somecolumn like %searchstring%
• where somenvarcharcolumn= integer_value
• where columnname=@someparameter or @some_parameter is NULL
• where condition1 OR condition2;

Джерела

Ось список статей, які я використовував під час свого дослідження. Вони чудові і надають глибоке розуміння. Якщо ви хочете зануритися глибше, обов'язково ознайомтесь з ними.

• Non-SARGable Predicates (by Brent Ozar)
• SQL Server Implicit Conversions Performance Issues (by K.)
  Браян Келлі на mssqltips.com)](https://www.mssqltips.com/sqlservertip/6207/sql-server-implicit-conversions-performance-issues/)
• Коли продуктивність SQL Server погіршується: неявні перетворення (Філ Фактор на red-gate.com)
• SQL Server Index Scan при використанні оператора 'OR' (dba.stackexchange.com)
• Єдине, що гірше за необов'язкові параметри для продуктивності запитів SQL Server (Ерік Дарлінг на erikdarling.com)
• Перегляд всіх запитів (Гейл на sqlinthewild.co.za)
• Проблеми з продуктивністю при використанні OR в JOIN (Ерік Кобб на sqlnuggets.com)

Будь ласка, натисніть «аплодувати» та поділіться, якщо цей пост був корисним.

Зв'яжіться зі мною

👉 YouTube
👉 Twitter
👉 GitHub

Стати підтримувачем🍵

Перекладено з: What makes your query Non-SARGable