Использование функции скалярного значения в предложении where вызывает проблемы с производительностью

ProgramBox

Использование функции скалярного значения в предложении where вызывает проблемы с производительностью

Post author:admin
Запись опубликована:5 августа, 2022
Post category:Вопросы по программированию

#sql #sql-server #user-defined-functions #sqlperformance

#sql #sql-сервер #определяемые пользователем функции #производительность sql

Вопрос:

У меня есть sql-запрос, который использует функцию скалярного значения внутри предложения where.

 SELECT TOP 1 *
FROM TableName T
WHERE T.SomeID = fn_ScalarValueFunction(100)
ORDER BY T.SomeDate

Выполнение запроса занимает около 1 минуты. Однако, если я выполняю следующие запросы отдельно, они выполняются в течение секунды.

 SELECT fn_ScalarValueFunction(100) = 123

SELECT TOP 1 *
FROM TableName T
WHERE T.SomeID = 123
ORDER BY T.SomeDate

Я проверил план выполнения, и он выполняет полное сканирование таблицы для обоих запросов.

Как я могу повысить производительность медленного запроса?

1. Если вы не используете SQL Server 2019 ( ), то функция не сможет извлечь выгоду из встраивания. Поэтому вам может быть лучше использовать встроенную функцию табличного значения.

2. Многострочная скалярная функция может быть медленной (у нас здесь нет определения, поэтому невозможно узнать). Преобразование во встроенную функцию табличного значения может (вероятно) повысить производительность.

3. Проблема использования скалярных функций в предложении where обсуждалась много раз. Поиск немного — но это хорошо известная проблема с производительностью. Возможно, первый вопрос, который вы должны задать себе, заключается в том, действительно ли ваша функция полезна и действительно нужна. Многим неопытным программистам слишком нравится модулировать tsql, что приводит к подобным проблемам.

4. По правде говоря, я бы не удивился, если SQL Server запускает функцию для каждой строки в таблице, а затем упорядочивает; следовательно, почему это так медленно.

5. Является ли этот экземпляр 2019 годом?

Ответ №1:

Переместите вызов функции в FROM предложение:

 SELECT TOP 1 T.*
FROM TableName T CROSS JOIN
     (VALUES (fn_ScalarValueFunction(100)) v(val)
WHERE T.SomeID = v.val
ORDER BY T.SomeDate;

В вашей версии скалярная функция должна вызываться для всех строк в таблице — и это добавляет накладные расходы. Эта версия может фактически использовать индекс SomeId , если он доступен.

1. Спасибо за ваш быстрый ответ. Я пробовал это, но для выполнения все еще требуется 1 минута. На SomeId нет индекса, может ли это быть проблемой?

2. Я создал некластеризованный индекс для SomeId, но безуспешно.

3. @разработчик . . . Это очень странно. Насколько велика таблица и сколько строк соответствует where условию?

4. @developer Вы отбрасываете все столбцы, поэтому, вероятно, решено не использовать индекс. select * обычно это плохо. У вас есть include столбцы в индексе?

5. @Gordon Linoff — таблица содержит 10 000 строк, а 11 строк соответствуют условию where.

Ответ №2:

Вы также можете объявить переменную для хранения значения:

 DECLARE @ID int = fn_ScalarValueFunction(100)

SELECT TOP 1 * 
FROM TableName T 
WHERE T.SomeID = @ID 
ORDER BY T.SomeDate

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

Ответ №3:

Можете ли вы попробовать использовать внутреннее соединение с вложенным запросом, чтобы получить результат скалярной функции только один раз, как показано ниже

 SELECT TOP 1 *
FROM TableName T
inner join
(select fn_ScalarValueFunction(100) as 'scalarID')t1
on t.someID = t1.scalarID
ORDER BY T.SomeDate