Динамический SQL, EXEC или sp_executesql

Tsql теория > Динамический SQL, EXEC или sp_executesql
12.02.2013 10:59:58



Статья:

Динамический SQL, EXEC или sp_executesql

Для исполнения динамического SQL кода в SQL Server есть два подхода: с помощью инструкции EXECUTE и с помошью хранимой процедуры sp_executesql. Какой же из них лучше? Ответ очевиден – sp_executesql. Почему этот подход лучше, и как его правильно использовать, чтобы получить все преимущества, я постараюсь разобрать в этом посте.

        Наиболее очевидное преимущество перед EXEC, то что хранимая процедура sp_executesql использует параметры, и таким образом снижает вероятность SQL Injection. Кроме того план выполнения процедуры может кэшироваться независимо от параметров, в то время как, используя EXEC, при разных значения параметров, будет получаться новый план, что приведёт к заполнению кэша планов выполнения. В этом случае будет расходоваться много памяти и планы действительно сложных запросов могут быть вытеснены из кэша большим количеством мелких. По мере роста нагрузки ситуация будет только усугубляться, и может привести к неприятному сюрпризу. Чтобы убедиться создаём тестовую таблицу:

CREATE TABLE dbo.Employees(name varchar(50), age smallint)
GO

Принудительно очищаем кэш планов выполнения:

DBCC freeproccache
GO

Выполняем 2 запроса при помощи хранимой процедуры sp_executesql (1):

DECLARE @sql nvarchar(1000) = 'SELECT * FROM dbo.Employees WHERE age = @age';
DECLARE @paramDef nvarchar(500) = '@age smallint';
DECLARE @ageParam smallint = 24;
EXEC sp_executesql @sql, @paramDef, @age = @ageParam;
SET @ageParam = 25;
EXEC sp_executesql @sql, @paramDef, @age = @ageParam;
GO

Смотрим, что находится в кэше планов выполнения, вот таким запросом:

SELECT q.TEXT,cp.usecounts,cp.objtype,p.*, q.*, cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE
cp.cacheobjtype = 'Compiled Plan' AND q.TEXT  LIKE '%dbo.Employees%'
AND q.TEXT  NOT LIKE '%sys.dm_exec_cached_plans %'
GO

Отсюда видно, что план запроса кэшируется, и при запуске запроса с другим параметром, используется тот же план. Теперь запускаем те же два запроса, но уже с помощью EXEC, предварительно очистив кэш (2):

DECLARE @ageParam smallint = 24;
DECLARE @sql nvarchar(1000) = 'SELECT * FROM dbo.Employees WHERE age = ' + CONVERT(varchar(10), @ageParam);
EXEC(@sql);
SET @ageParam = 25;
SET @sql = 'SELECT * FROM dbo.Employees WHERE age = ' + CONVERT(varchar(10), @ageParam);
EXEC(@sql);
GO

Посмотрев кэш, после этого запроса, видно, что кэшируются планы обоих запросов. Тот же результат можно получить и при выполнении кода с помощью sp_executesql, если использовать её неправильно (3):

DECLARE @ageParam smallint = 24;
DECLARE @sql nvarchar(1000) = 'SELECT * FROM dbo.Employees WHERE age = ' +  CONVERT(varchar(10), @ageParam);
EXEC sp_executesql @sql;
SET @ageParam = 25;
SET @sql = 'SELECT * FROM dbo.Employees WHERE age = ' +  CONVERT(varchar(10), @ageParam);
EXEC sp_executesql @sql;
GO

Кроме того, при исполнении кода (примеры 2 и 3) происходит неявное приведение типов, т.к. параметры передаются в виде строки. И напротив, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql. На рисунках ниже, слева направо приведены фрагменты планов выполнения динамического SQL кода при помощи EXEC и sp_executesql соответственно:

И последнее преимущество sp_executesql, которое я хочу выделить, это возможность возвращать какое-то значение через OUT параметр.

PS: LINQ to SQL, Entity Framework и параметризованные команды ADO.NET используют для выполнения T-SQL кода именно sp_executesql.

Ссылки по теме:

- Разбор выполнения динамического SQL кода при помощи EXEC и sp_executesql

- Разбор неявной конвертации параметров при использовании динамического SQL

- Инструкция EXECUTE

- Хранимая процедура sp_executesql

- Использование хранимой процедуры sp_executesql

- SQL Injection

- Защита от SQL Injection