I have a bunch of stored procedures that I’m looking to optimize and want to know if changing from having the variables injected into the WHERE condition to having them inject into a variable declaration will help
The current statements look like this where the ids are passed into the stored procedure and then injected into the EXEC
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
SELECT * FROM Employees WHERE Id = ' + @EmployeeId + '
SELECT * FROM Customers WHERE Id = ' + @CustomerId + '
')
Would there be any benefit of changing to something like this where the injected values are done into a variable?
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
DECLARE @EmployeeId int = ' + @EmployeeId + '
DECLARE @CustomerId int = ' + @CustomerId + '
SELECT * FROM Employees WHERE Id = @EmployeeId
SELECT * FROM Customers WHERE Id = @CustomerId
')
Advertisement
Answer
Doing it the first way
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
SELECT * FROM Employees WHERE Id = ' + @EmployeeId + '
SELECT * FROM Customers WHERE Id = ' + @CustomerId + '
')
means that the a new query plan could be generated for every run. While it is true that auto-parameterization may work here, it may not, and therefore you are undergoing the cost of recompilation every time.
Meanwhile the second version,
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
DECLARE @EmployeeId int = ' + @EmployeeId + '
DECLARE @CustomerId int = ' + @CustomerId + '
SELECT * FROM Employees WHERE Id = @EmployeeId
SELECT * FROM Customers WHERE Id = @CustomerId
')
doesn’t really help things, as the whole batch is changing each time, so you still get a recompilation. It will also force auto-parameterization off, and the fact you are using local variables means parameter sniffing is disabled also.
As mentioned by @DanGuzman, you could use sp_executesql
DECLARE @EmployeeId int = 123;
DECLARE @CustomerId int = 456;
EXEC sp_executesql N'
SELECT * FROM Employees WHERE Id = @EmployeeId;
SELECT * FROM Customers WHERE Id = @CustomerId
'
,N'@EmployeeId int, @CustomerId int'
,@EmployeeId = @EmployeeId
,@CustomerId = @CustomerId;
This will get you a cached plan, with parameter sniffing.