Does declaring variables inside an exec improve SQL query plans?-CodePudding

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
')

CodePudding user response：

I suggest executing a parameterized query instead of injecting variables. This will promote execution plan reuse and have other benefits as well.

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;

CodePudding user response：

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.