В большинстве случаев лучше избегать скалярных функций, которые ссылаются на таблицы, потому что (как говорили другие) они в основном являются черными ящиками, которые необходимо запускать один раз для каждой строки и не могут быть оптимизированы механизмом плана запроса. Следовательно, они склонны к линейному масштабированию, даже если связанные таблицы имеют индексы.

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

Как побочный эффект встраивания, они не могут содержать никакого процедурного кода (не объявлять @variable, устанавливать @variable = .., возвращать). Однако они могут возвращать несколько строк и столбцов.

Вы можете переписать свои функции примерно так:

create function usf_GIS_GET_LAT(
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 lat
  from GIS_Location with (nolock) 
  where [State] = @State
    and [City] = @City
);

GO

create function usf_GIS_GET_LON (
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 LON
  from GIS_Location with (nolock)
  where [State] = @State
    and [City] = @City
);

Синтаксис их использования также немного отличается:

select
    Lat.Lat,
    Lon.Lon
from
    Address_Location with (nolock)
    cross apply dbo.usf_GIS_GET_LAT(City,[State]) AS Lat
    cross apply dbo.usf_GIS_GET_LON(City,[State]) AS Lon
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)

Нет.

В скалярных функциях нет ошибок, из-за которых их производительность падает экспоненциально в зависимости от количества строк, с которыми выполняется скалярная функция. Повторите свои тесты и взгляните на профилировщик SQL, посмотрев на столбцы CPU и READS и DURATION. Увеличьте размер теста, чтобы включить тесты, которые занимают больше секунды, двух секунд, пяти секунд.

CREATE FUNCTION dbo.slow
(
    @ignore int
)
RETURNS INT 
AS
BEGIN
    DECLARE @slow INT
    SET @slow = (select count(*) from sysobjects a 
        cross join sysobjects b 
        cross join sysobjects c 
        cross join sysobjects d 
        cross join sysobjects e 
        cross join sysobjects f
    where a.id = @ignore) 

    RETURN @slow
END
go
SET STATISTICS TIME ON

select top 1 dbo.slow(id)
from sysobjects
go
select top 5 dbo.slow(id)
from sysobjects
go
select top 10 dbo.slow(id)
from sysobjects
go
select top 20 dbo.slow(id)
from sysobjects
go
select top 40 dbo.slow(id)
from sysobjects

SET STATISTICS TIME OFF

Выход

SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 202 ms.


SQL Server Execution Times:
   CPU time = 889 ms,  elapsed time = 939 ms.

SQL Server Execution Times:
   CPU time = 1748 ms,  elapsed time = 1855 ms.

SQL Server Execution Times:
   CPU time = 3541 ms,  elapsed time = 3696 ms.


SQL Server Execution Times:
   CPU time = 7207 ms,  elapsed time = 7392 ms.

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

Вы можете обернуть свою функциональность во встроенный TVF, это будет намного быстрее:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx

вы вызываете функцию два раза (два выбора попадания в БД) для каждой строки в наборе результатов.

чтобы сделать ваш запрос быстрее, подключитесь прямо к GIS_Location и пропустите функции:

SELECT
    g.Lat,
    g.Lon
FROM
    Address_Location        l WITH(NOLOCK)
    INNER JOIN GIS_Location g WITH(NOLOCK) WHERE l.State = g.State AND l.City = g.City
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)

Я не уверен, почему NOLOCK или безумное предложение where я просто скопировал из вопроса...

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

Совет KMike хорош. ГДЕ .. В (ВЫБЕРИТЕ что-то) вряд ли будет эффективным шаблоном, и в этом случае его можно легко заменить на СОЕДИНЕНИЕ.

Посмотрите, работает ли это лучше... Или, может быть, отдельное внутреннее соединение?

select a.*,
(select top 1 g.Lat from GIS_Location g where g.City = a.City and g.State = a.State) as Lat,
(select top 1 g.Lon from GIS_Location g where g.City = a.City and g.State = a.State) as Lon
from Address_Location a
where a.ID in (select top 100 ID from Address_Location order by ID desc)

Что касается производительности скалярной функции, я не уверен.

Обычно скалярные функции намного медленнее встроенных аналогов TVF. К счастью, для многих сценариев это изменится.

В SQL Server 2019 будут представлены Скалярное встраивание UDF:

Функция в составе набора функций интеллектуальной обработки запросов. Эта функция повышает производительность запросов, которые вызывают скалярные пользовательские функции в SQL Server (начиная с предварительной версии SQL Server 2019)

Скалярные определяемые пользователем функции T-SQL

Пользовательские функции, реализованные в Transact-SQL и возвращающие одно значение данных, называются скалярными пользовательскими функциями T-SQL. Пользовательские функции T-SQL — это элегантный способ добиться повторного использования кода и модульности запросов SQL. Некоторые вычисления (например, сложные бизнес-правила) проще выразить в императивной форме UDF. Пользовательские функции помогают создавать сложную логику, не требуя навыков написания сложных SQL-запросов.

Скалярные пользовательские функции обычно плохо работают по следующим причинам.

• Итеративный вызов
• Отсутствие калькуляции
• Интерпретированное исполнение
• Серийное исполнение

---

Автоматическое встраивание скалярных пользовательских функций

Функция встраивания скалярных определяемых пользователем функций предназначена для повышения производительности запросов, вызывающих скалярные определяемые пользователем функции T-SQL, где выполнение определяемых пользователем функций является основным узким местом.

С помощью этой новой функции скалярные пользовательские функции автоматически преобразуются в скалярные выражения или скалярные подзапросы, которые подставляются в вызывающем запросе вместо оператора пользовательских функций. Затем эти выражения и подзапросы оптимизируются. В результате в плане запроса больше не будет определяемого пользователем оператора функции, но его эффекты будут наблюдаться в плане, например, представления или встроенные TVF.

---

Требования к встроенным скалярным пользовательским функциям

Скалярная пользовательская функция T-SQL может быть встроенной, если выполняются все следующие условия:

• UDF записывается с использованием следующих конструкций:

  1. DECLARE, SET: Variable declaration and assignments.
  2. SELECT: SQL-запрос с присвоением одной или нескольких переменных1.
  3. IF/ELSE: ветвление с произвольными уровнями вложенности.
  4. RETURN: один или несколько операторов возврата.
  5. UDF: вложенные/рекурсивные вызовы функций2.
  6. Другие: реляционные операции, такие как EXISTS, ISNULL.

• Пользовательская функция не вызывает никаких встроенных функций, зависящих от времени (например, GETDATE()) или имеющих побочные эффекты3 (например, NEWSEQUENTIALID()).

• UDF использует предложение EXECUTE AS CALLER (поведение по умолчанию, если предложение EXECUTE AS не указано).
• UDF не ссылается на табличные переменные или табличные параметры.
• Запрос, вызывающий скалярную определяемую пользователем функцию, не ссылается на вызов скалярной пользовательской функции в своем предложении GROUP BY.
• UDF изначально не компилируется (поддерживается взаимодействие).
• Пользовательская функция не используется в вычисляемом столбце или в определении проверочного ограничения.
• UDF не ссылается на определяемые пользователем типы.
• В UDF не добавляются подписи.
• UDF не является функцией разделения.

Проверка, является ли функция встроенной:

SELECT OBJECT_NAME([object_id]) AS name, is_inlineable
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('schema.function_name')

Включение/отключение функции на уровне базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON/OFF;

Приложение

Исследования Майкрософт — проект Froid

Извините, что поздно присоединился к этой вечеринке, но я хочу поделиться своим ответом с будущими жертвами Profiler. Несколько дней назад все скалярные функции на одном производственном сервере (sql server 2012 sp4 enterprise) стали медленнее, некоторые хранимые процедуры, выполнение которых обычно занимает секунды, они начали выполняться за минуты, в одном случае часы.

Наконец, основной причиной этого была трассировка, созданная с помощью профилировщика. Трассировка была запущена, но затем ноутбук, на котором выполнялась эта трассировка, был выключен без предварительной остановки трассировки. Как чудо, трассировка была автоматически остановлена ​​пользователем sa (для записи учетная запись sa была отключена и переименована) -- "Трассировка SQL остановлена. Идентификатор трассировки = '3'. Имя для входа = 'sa'". это автоматически решает проблему с производительностью.

Итак, проверьте трассировку профилировщика или расширенные события, работающие на медленном сервере.

Надеюсь, это поможет кому-то в будущем.