If we are using Direct T-SQL (not dynamic) in stored procedure, SQL Server reuses 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.
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.
No comments:
Post a Comment