В последнее время я прочитал много вещей, указывающих на скалярные функции, как если бы они были дьяволом. В этом блоге я собираюсь исследовать, так ли это. Давайте посмотрим.
Это правда, что во многих ситуациях скалярная функция часто является узким местом производительности; но есть ли ситуация, когда они могут быть ответственно использованы?
Что делать, если у вас есть таблица поиска, которая почти никогда не меняется? Стоит ли делать соединение при поиске, чтобы получить нужные вам данные?
Давайте рассмотрим простое соединение между адресом клиента и таблицей поиска состояния.
Чтобы соединить их, вы обычно используете очень простое внутреннее соединение, чтобы получить код состояния, который также может быть именем состояния, которое длиннее, чем 4 байта для int. Вы также можете включить другие показатели в таблицу State, такие как географические данные или Кремль. А пока давайте притворимся, что Abbr намного больше, чем State (int), поэтому имеет смысл сбросить его в таблицу подстановки.
Вот как может выглядеть запрос соединения
Чтобы проверить значение скалярных функций, мы создали следующий код (обратите внимание, что он отсекает, но вы получаете точку)
Теперь, когда у нас есть функция для тестирования, мы хотим создать запрос, который возвращает те же результаты, что и наш запрос join. Давайте посмотрим.
Как они соотносятся с реальным планом?
Ух ты! Запрос с этой функцией имеет гораздо меньшую стоимость! Невероятные результаты, правда? Не так быстро! Иногда стоимость может ввести в заблуждение. Чтобы провести настоящий тест, нам нужно будет сделать немного больше. Давайте выключим план и включим статистику времени.
Общее время, затраченное на запрос соединения, составляет 206 МС.
Только 92 мс для запроса funtion. Это хорошо!
Давайте рассмотрим статистику запросов, чтобы увидеть, как эти два сравниваются.
Здесь мы находим несколько неожиданных результатов. Время выполнения было меньше для этого одиночного запуска, однако процессор был больше на значительную величину.
Мы должны копать глубже. Возможно, вы не заметили в планах запросов, что функция вообще не представлена в плане. Обратите внимание, что это SQL Server 2017, и база данных работает на последнем уровне совместимости.
Чтобы узнать больше, мы рассмотрим статистику функций DMV.
Выберите db_name(database_id) в качестве DB,
object_name(object_id) в качестве fn,
type_desc,
last_execution_time,
execution_count,
total_elapsed_time,
total_worker_time,
total_logical_reads,
total_physical_reads,
total_logical_writes
Из sys. dm_exec_function_stats
То, что мы находим, это то, что функция была выполнена 192 раза. Это происходит потому, что функция должна выполняться один раз для каждой строки в результате, и именно по этой причине скалярные функции обычно не являются отличным выбором.
Один из последних тестов состоит в том, чтобы выполнить запросы 100 раз. Для этого мы будем использовать GO 100.
ему мы научились? Хотя функция может отлично смотреться в плане запросов, она не складывается в реальные метрики времени выполнения. Если есть одна вещь, которую DBA всегда должен сказать, это “это зависит“, но в этом случае это не так, и вы должны вместо этого добавить эту цитату в свой набор инструментов:”вы никогда не должны полностью доверять стоимости в планах запросов".
