Performance difference between EXEC and sp_executesql

Introduction

I presumed that you already know about the execution plan. Sometimes we may come to situation to use Dynamic SQL instead of direct T-SQL.If we are using Direct T-SQL (not dynamic) in stored procedure, SQL Server reused execution plan from the cache. i.e. SQL Server will not compile the Stored Procedure again.

If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.So, we have to think about the performance while using dynamic sql.

To execute the dynamic SQL in stored procedure, we have to use the following way.

1. EXEC (Non- parameterized)
2. sp_executesql (Parameterized)

There will be performance difference between above two.

Execution plan will not be created until you execute the dynamic sql. If you execute the dynamic sql using EXEC, execution plan will be created for every execution even values only changing. If you use sp_executesql, SQL Server Optimizer will try to use same execution plan. Because dynamic sql string will be the same, values only going to change. So it will be treated as Stored Procedure having input parameters.

Use the following query to test,
 

CREATE TABLE [dbo].[Item]
(
ID INT
)

GO

INSERT INTO [dbo].[Item](ID) VALUES (1)
INSERT INTO [dbo].[Item](ID) VALUES (2)

GO


DBCC FREEPROCCACHE

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID

SET @ItemID = 2
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID

To view the execution plan, use the following query.

SELECT usecounts, sql FROM sys.syscacheobjects;

Results

UseCounts SQL

1 SELECT * FROM [dbo].[Item] WHERE ID = 1
2 (@ID INT)SELECT * FROM [dbo].[Item] WHERE ID = @ID
1 SELECT usecounts, sql FROM sys.syscacheobjects
1 SELECT * FROM [dbo].[Item] WHERE ID = 2

From the results, executed the dynamic sql using sp_executesql uses same execution plan. EXEC create the execution plan every time.

Conclusion

Always try to use sp_executesql to execute the dynamic sql to improve the performance.