Есть ли какая-либо польза для СХЕМЫ ЗАГРУЗКИ функции за пределами защиты Хэллоуина?

Хорошо известно, что SCHEMABINDING функция может избегать ненужной катушки в планах обновления:

  

Если вы используете простые UDF T-SQL, которые не касаются каких-либо таблиц (т. е. не получают доступа к данным), убедитесь, что вы указываете параметр SCHEMABINDING во время создания UDF. Это сделает привязку схемы UDF и гарантирует, что оптимизатор запросов не генерирует ненужных операторов очереди для планов запросов, связанных с этими UDF.

Есть ли другие преимущества функции SCHEMABINDING , даже если она не имеет доступа к данным?

41 голос | спросил Paul White 4 J0000006Europe/Moscow 2016, 09:35:05

1 ответ


61

<сильный> Да.

Неспособность указать WITH SCHEMABINDING означает, что SQL Server пропускает подробные проверки, которые он обычно выполняет в теле функции. Он просто отмечает функцию как доступ к данным (как указано в ссылке, приведенной в вопросе).

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

Существуют важные свойства функции пять :

  • детерминизм
  • Точность
  • Доступ к данным
  • Доступ к системным данным
  • Проверка системы

Например, возьмите следующую несвязанную скалярную функцию:

  CREATE FUNCTION dbo.F
(
    @i integer
)
ВОЗВРАТЫ datetime
В ВИДЕ
НАЧАТЬ
    RETURN '19000101';
КОНЕЦ;
 

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

  SELECT
    IsDeterministic = OBJECTPROPERTYEX (Func.ID, 'IsDeterministic'),
    IsPrecise = OBJECTPROPERTYEX (Func.ID, 'IsPrecise'),
    IsSystemVerified = OBJECTPROPERTYEX (Func.ID, 'IsSystemVerified'),
    UserDataAccess = OBJECTPROPERTYEX (Func.ID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX (Func.ID, 'SystemDataAccess')
FROM (VALUES (OBJECT_ID (N'dbo.F ', N'FN'))) AS Func (ID);
 

 Результат

Два свойства доступа к данным были установлены как истинные, а остальные три имеют значение false .

Это имеет последствия, выходящие за рамки ожидаемых (например, в индексированных представлениях или индексированных вычисленных столбцах).

Эффекты на оптимизаторе запросов

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

Например, рассмотрим следующие две таблицы:

  CREATE TABLE dbo.T1
(
    SomeInteger integer ПЕРВИЧНЫЙ КЛЮЧ
);
ИДТИ
CREATE TABLE dbo.T2
(
    SomeDate datetime ПЕРВИЧНЫЙ КЛЮЧ
);
 

... и запрос, который использует функцию (как определено ранее):

  SELECT *
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = dbo.F (T1.SomeInteger);
 

План запроса выглядит так, как ожидалось, с поиском в таблице T2:

 Искать план

Однако, если один и тот же логический запрос записывается с использованием производной таблицы или общего выражения таблицы:

  С CTE AS
(
    SELECT *, dt = dbo.F (T1.SomeInteger)
    FROM dbo.T1 AS T1
)
ВЫБРАТЬ *
ОТ CTE
JOIN dbo.T2 AS T2
    ON T2.SomeDate = CTE.dt;

- Производная таблица
ВЫБРАТЬ
    *
ИЗ
(
    SELECT *, dt = dbo.F (T1.SomeInteger)
    FROM dbo.T1 AS T1
) AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = T1.dt;
 

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

 План сканирования

Это также происходит, если производная таблица или общее выражение таблицы заменяется на представление или встроенную функцию. Подсказка FORCESEEK (и другие подобные попытки) не будет выполнена:

 Сообщение об ошибке

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

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

Fix

Исправление для этого примера включает в себя два шага:

  1. Добавить С СХЕМЫ>
  2. Сделать функцию детерминированной

Первый шаг тривиален. Второе включает удаление неиндексируемого имплицированного перевода из строки в datetime ; заменив его детерминированным CONVERT . Ни один из них не достаточен сам по себе .

  ALTER FUNCTION dbo.F
(
    @i integer
)
ВОЗВРАТЫ datetime
С СХЕМЫ
В ВИДЕ
НАЧАТЬ
    - Преобразование с детерминированным стилем
    RETURN CONVERT (дата и время, 19000101, 112);
КОНЕЦ;
 

Теперь свойства функции:

 Новые свойства

При освобождении оптимизатора все примеры теперь вызывают желаемый план поиска .


Обратите внимание, что с помощью CAST в datetime вфункция не будет работать, поскольку в этом синтаксисе невозможно указать стиль преобразования:

  ALTER FUNCTION dbo.F
(
    @i integer
)
ВОЗВРАТЫ datetime
С СХЕМЫ
В ВИДЕ
НАЧАТЬ
    - Преобразование с детерминированным стилем
    RETURN CAST ('19000101' AS datetime);
КОНЕЦ;
 

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

 Свойства функции CAST

ответил Paul White 4 J0000006Europe/Moscow 2016, 09:35:05

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132