Влияние порядка предложений where на скорость SQL-запроса

ProgramBox

Влияние порядка предложений where на скорость SQL-запроса

Post author:admin
Запись опубликована:9 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server #sqlperformance

#sql #sql-сервер #производительность SQL

Вопрос:

У меня проблема, когда сложный запрос выполняется недопустимо медленно (примерно 10 секунд на транзакцию) на SQL-сервере. Я не уверен в версии, но, по крайней мере, 2005. Запрос сопоставляет около 40 полей (столбцов) с соответствующими переменными и возвращает те строки, в которых либо совпадают все поля, либо и поле, и переменная равны нулю. По крайней мере, первые несколько полей проиндексированы. В нынешнем виде первое поле в запросе будет совпадать практически с каждой записью. Позволит ли изменение порядка запроса таким образом, чтобы поля с меньшей вероятностью совпадения помещались первыми, досрочно завершить запрос по данной строке при обнаружении несоответствия? Какие другие ускорения кто-нибудь может предложить?

Запрос с переменными, переименованными, следует:

 SELECT _pk FROM FinancialDetail 
WHERE   ( Field01 = @Field01 OR ( Field01 IS NULL AND @Field01 IS NULL ) )
    AND ( Field02 = @Field02 OR ( Field02 IS NULL AND @Field02 IS NULL ) )
    AND ( Field03 = @Field03 OR ( Field03 IS NULL AND @Field03 IS NULL ) )
    AND ( Field04 = @Field04 OR ( Field04 IS NULL AND @Field04 IS NULL ) )
    AND ( Field05 = @Field05 OR ( Field05 IS NULL AND @Field05 IS NULL ) )
    AND ( Field06 = @Field06 OR ( Field06 IS NULL AND @Field06 IS NULL ) )
    AND ( Field07 = @Field07 OR ( Field07 IS NULL AND @Field07 IS NULL ) )
    AND ( Field08 = @Field08 OR ( Field08 IS NULL AND @Field08 IS NULL ) )
    AND ( Field09 = @Field09 OR ( Field09 IS NULL AND @Field09 IS NULL ) )
    AND ( Field10 = @Field10 OR ( Field10 IS NULL AND @Field10 IS NULL ) )
    AND ( Field11 = @Field11 OR ( Field11 IS NULL AND @Field11 IS NULL ) )
    AND ( Field12 = @Field12 OR ( Field12 IS NULL AND @Field12 IS NULL ) )
    AND ( Field13 = @Field13 OR ( Field13 IS NULL AND @Field13 IS NULL ) )
    AND ( Field14 = @Field14 OR ( Field14 IS NULL AND @Field14 IS NULL ) )
    AND ( Field15 = @Field15 OR ( Field15 IS NULL AND @Field15 IS NULL ) )
    AND ( Field16 = @Field16 OR ( Field16 IS NULL AND @Field16 IS NULL ) )
    AND ( Field17 = @Field17 OR ( Field17 IS NULL AND @Field17 IS NULL ) )
    AND ( Field18 = @Field18 OR ( Field18 IS NULL AND @Field18 IS NULL ) )
    AND ( Field19 = @Field19 OR ( Field19 IS NULL AND @Field19 IS NULL ) )
    AND ( Field20 = @Field20 OR ( Field20 IS NULL AND @Field20 IS NULL ) )
    AND ( Field21 = @Field21 OR ( Field21 IS NULL AND @Field21 IS NULL ) )
    AND ( Field22 = @Field22 OR ( Field22 IS NULL AND @Field22 IS NULL ) )
    AND ( Field23 = @Field23 OR ( Field23 IS NULL AND @Field23 IS NULL ) )
    AND ( Field24 = @Field24 OR ( Field24 IS NULL AND @Field24 IS NULL ) )
    AND ( Field25 = @Field25 OR ( Field25 IS NULL AND @Field25 IS NULL ) )
    AND ( Field26 = @Field26 OR ( Field26 IS NULL AND @Field26 IS NULL ) )
    AND ( Field27 = @Field27 OR ( Field27 IS NULL AND @Field27 IS NULL ) )
    AND ( Field28 = @Field28 OR ( Field28 IS NULL AND @Field28 IS NULL ) )
    AND ( Field29 = @Field29 OR ( Field29 IS NULL AND @Field29 IS NULL ) )
    AND ( Field30 = @Field30 OR ( Field30 IS NULL AND @Field30 IS NULL ) )
    AND ( Field31 = @Field31 OR ( Field31 IS NULL AND @Field31 IS NULL ) )
    AND ( Field32 = @Field32 OR ( Field32 IS NULL AND @Field32 IS NULL ) )
    AND ( Field33 = @Field33 OR ( Field33 IS NULL AND @Field33 IS NULL ) )
    AND ( Field34 = @Field34 OR ( Field34 IS NULL AND @Field34 IS NULL ) )
    AND ( Field35 = @Field35 OR ( Field35 IS NULL AND @Field35 IS NULL ) )
    AND ( Field36 = @Field36 OR ( Field36 IS NULL AND @Field36 IS NULL ) )
    AND ( Field37 = @Field37 OR ( Field37 IS NULL AND @Field37 IS NULL ) )
    AND ( Field38 = @Field38 OR ( Field38 IS NULL AND @Field38 IS NULL ) )
    AND ( Field39 = @Field39 OR ( Field39 IS NULL AND @Field39 IS NULL ) )
    AND ( Field40 = @Field40 OR ( Field40 IS NULL AND @Field40 IS NULL ) )

1. Я бы выбрал построение динамического запроса внутри процедуры на основе предоставленных параметров. Это определенно ускорило бы выполнение.

2. Я предлагаю вам ознакомиться sommarskog.se/dyn-search.html . Версия SQL Server имеет значение и может быть определена с SELECT @@VERSION; помощью .

3. Я поддерживаю клиента (3-го уровня) и не имею прямого доступа к клиентской системе, поэтому я (пока) не знаю версию DB. В таблице около 5-8 миллионов строк.

4. Итак, подводя итог, вам нужно показать ТОЧНО совпадающие строки с параметрами, верно? Вы можете попробовать преобразовать параметры в строку, а затем использовать join. Может быть немного быстрее, учитывая условия и структуру таблицы. Хотя я не эксперт.

5. @DanGuzman — Это не динамический поиск. Речь идет о необязательных параметрах, где передача null означает «любое значение». Здесь null означает «должно быть null». Это не то же самое, что запрос необязательного параметра Field01 = @Field01 OR @Field01 IS NULL

Ответ №1:

Это не динамический поиск. Сравниваются все столбцы. Невозможно удалить что-либо без изменения семантики.

 Field01 = @Field01 OR ( Field01 IS NULL AND @Field01 IS NULL )

Просто означает, что оба равны, когда два нуля сравниваются как равные. Обычно этот шаблон не вызывает у SQL Server никаких проблем. Сокращенный пример

 CREATE TABLE #FinancialDetail
(
_pk INT PRIMARY KEY,
Field01 INT,
Field02 INT,
Field03 INT
)
CREATE INDEX IX ON #FinancialDetail(Field01, Field02, Field03)

declare @Field01 int, @Field02 int, @Field03 int;

SELECT _pk FROM #FinancialDetail 
WHERE   ( Field01 = @Field01 OR ( Field01 IS NULL AND @Field01 IS NULL ) )
    AND ( Field02 = @Field02 OR ( Field02 IS NULL AND @Field02 IS NULL ) )
    AND ( Field03 = @Field03 OR ( Field03 IS NULL AND @Field03 IS NULL ) )

Который показывает поиск по индексу во всех трех столбцах

План для этого фактически неотличим от плана для

 SELECT _pk FROM #FinancialDetail 
WHERE   ( Field01 = @Field01 )
    AND ( Field02 = @Field02 )
    AND ( Field03 = @Field03 )

Тот факт, что оператор равенства в поиске индекса использует другую семантику NULL , для нас не раскрывается, как обсуждалось в комментариях здесь .

Вам нужен составной индекс для некоторой довольно выборочной комбинации столбцов (SQL Server допускает до 16 ключевых столбцов), и вы должны иметь возможность получить запрос по этому.

Если вам нужно работать с существующими индексами с одним столбцом, и оптимальный может отличаться, вы можете добавить OPTION (RECOMPILE) .

Вы также можете переписать, как показано ниже, что короче и с той же семантикой, но я сомневаюсь, что это будет иметь какое-либо значение для плана.

 SELECT _pk FROM #FinancialDetail 
WHERE EXISTS (SELECT @Field01, @Field02, @Field03 /*.... , @Field40*/
              INTERSECT
              SELECT Field01, Field02, Field03 /*.... , Field40*/ )

1. Использование подсказки Option(Перекомпиляция) оказалось тем решением, которое мне было нужно. Вполне возможно, что использование динамического sql также решило бы проблему, но структура приложения затрудняла ее реализацию.

Ответ №2:

Это немного длинновато для комментария. Я предполагаю, что OR условия сбивают оптимизатора с толку. У вас сложное (хотя и повторяющееся) WHERE условие.

Одна большая проблема заключается в том, что SQL Server кэширует планы выполнения, поэтому при первом запуске запроса план устанавливается. Если более поздние значения могут лучше использовать индексы, они не обязательно будут использоваться.

Я бы рекомендовал динамический SQL, чтобы устранить OR условия. Результирующие условия будут зависеть от значений переменных. Если @Field02 бы и @Field03 было NULL , то условия выглядели бы так:

 WHERE ( Field01 = @Field01 ) AND
      ( Field02 IS NULL ) AND
      ( Field03 IS NULL )  AND
      ( Field04 = @Field04 ) AND
      . . .

Это строгие AND условия с равенством, с которыми оптимизатору должно быть намного проще справиться.