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

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

-- Создание UDF без привязки схемы
CREATE FUNCTION NonSchemaBinded(@INPUT INT)
RETURNS INT
BEGIN
    RETURN @INPUT * 2 + 50
END

-- Создание UDF с привязкой схемы
CREATE FUNCTION SchemaBinded(@INPUT INT)
RETURNS INT WITH SCHEMABINDING
BEGIN
    RETURN @INPUT * 2 + 50
END

-- Создание таблиц для UDF
CREATE TABLE forNonSB(col1 INT)
CREATE TABLE forSB(col1 INT)

Затем мы заполним обе таблицы по 100 000 строк каждая.

-- Заполнение таблицы для UDF без привязки схемы
DECLARE @COUNT INT
SET @COUNT = 1
WHILE (@COUNT < 100000)
BEGIN
    INSERT INTO forNonSB VALUES (@COUNT)
    SET @COUNT = @COUNT + 1
END

-- Заполнение таблицы для UDF с привязкой схемы
SET @COUNT = 1
WHILE (@COUNT < 100000)
BEGIN
    INSERT INTO forSB VALUES (@COUNT)
    SET @COUNT = @COUNT + 1
END

Теперь, когда у нас есть наши UDF и таблицы, давайте сравним производительность UDF с привязкой схемы и без нее.

-- Обновление с использованием UDF с привязкой схемы
UPDATE forSB
SET col1 = col1 + 5 
WHERE dbo.SchemaBinded(col1) > 10

-- Обновление с использованием UDF без привязки схемы
UPDATE forNonSB
SET col1 = col1 + 5 
WHERE dbo.NonSchemaBinded(col1) > 10

Анализируя планы выполнения и статистику запросов, мы видим, что UDF с привязкой схемы обеспечивает лучшую производительность по сравнению с UDF без привязки схемы. UDF с привязкой схемы устраняет необходимость в операторе Table Spool, что приводит к улучшению производительности запроса. Кроме того, UDF с привязкой схемы не обращается к системным каталогам или данным пользователей, что снижает накладные расходы.

Мы также можем отслеживать производительность обоих UDF, просматривая кэш SQL Server. Используя представление sys.dm_exec_query_stats, мы можем получить агрегированную статистику производительности для кэшированных планов запросов.

-- Получение агрегированной статистики производительности обоих UDF в кэше планов
SELECT total_logical_reads, total_logical_writes, total_physical_reads, total_worker_time, total_elapsed_time, sys.dm_exec_sql_text.TEXT 
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE total_logical_reads <> 0 AND total_logical_writes <> 0 
ORDER BY (total_logical_reads + total_logical_writes) DESC

Исходя из статистики кэша планов, мы видим, что UDF с привязкой схемы более эффективен, чем UDF без привязки схемы.

В заключение, использование привязки схемы с UDF в SQL Server может значительно улучшить производительность путем устранения ненужных операторов и снижения накладных расходов. Тщательно рассмотрев использование привязки схемы, разработчики могут оптимизировать свои приложения SQL Server и улучшить общую производительность системы.

Не забудьте очистить и удалить созданные UDF и таблицы, когда они больше не нужны.

-- Удаление таблиц
DROP TABLE forSB, forNonSB

-- Удаление UDF
DROP FUNCTION dbo.SchemaBinded, dbo.NonSchemaBinded