Ответ 1

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

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

В качестве побочного эффекта встраивания они не могут содержать какой-либо процедурный код (no declare @variable; set @variable =..; return). Однако они могут возвращать несколько строк и столбцов.

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

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)

Ответ 2

Они не делают.

Нет ошибок в скалярных функциях, которые приводят к ухудшению его производительности экспоненциально в зависимости от количества строк в скалярной функции. Попробуйте свои тесты снова и посмотрите на профилировщик 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.

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

Ответ 3

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

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

Ответ 4

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

чтобы ваш запрос быстрее присоединился к 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, или сумасшедший, где предложение, я просто скопировал из вопроса...

Ответ 5

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

Совет KMike хорош. WHERE.. IN (SELECT something) вряд ли будет эффективным образцом, и в этом случае его можно легко заменить JOIN.

Ответ 6

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

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)

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

Ответ 7

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

SQL Server 2019 представит скалярную вставку UDF:

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

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

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

Скалярные UDF обычно плохо работают по следующим причинам.

• Итеративный вызов
• Отсутствие затрат
• Интерпретируемое исполнение
• Серийное исполнение

---

Автоматическое наложение скалярных UDF

Цель функции встраивания Scalar UDF - повысить производительность запросов, которые вызывают скалярные UDF T-SQL, где выполнение UDF является основным узким местом.

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

---

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

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

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

  1. DECLARE, SET: объявление и назначение переменных.
  2. SELECT: SQL-запрос с присвоением одной/нескольких переменных1.
  3. IF/ELSE: ветвление с произвольными уровнями вложенности.
  4. ВОЗВРАТ: одно или несколько операторов возврата.
  5. UDF: вложенные/рекурсивные вызовы функций2.
  6. Другие: реляционные операции, такие как EXISTS, ISNULL.

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

• UDF использует предложение EXECUTE AS CALLER (поведение по умолчанию, если не указано предложение EXECUTE AS).
• UDF не ссылается на табличные переменные или табличные параметры.
• Запрос, вызывающий скалярную UDF, не ссылается на скалярный вызов UDF в своем предложении GROUP BY.
• UDF изначально не скомпилирован (взаимодействие поддерживается).
• 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;

Добавление

Microsoft Research - проект Froid

Ответ 8

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

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

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

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