Performance Difference between Parameterized and Non-Parameterized call to Stored Procedure

 Introduction

  You can call the sql stored procedures either with parameter ( using SqlParameter) or normal sql string ( using EXEC dbo.spName). There will be significant performance difference between two.

Non – Parameterized Stored Procedure Call:

If you call the stored procedure using EXEC ( like normal sql query) with command type as TEXT, execution plan is not reused. SQL Server caches the execution plan for SP to reuse. If you used EXEC for calling SP, new execution plan will create for every different string of EXEC statement. It will not reuse the execution plan from the SQL Server cache. SQL Server normally doing the parsing, optimizing, compiling process while creating the new execution plan.

Parameterized Stored Procedure Call:

If you call Stored procedure with Parameters ( example: SqlParameter) with Command Type as Stored Procedure, execution plan of the SP is reused again instead of creating the new one with different parameters.

Conclusion:

Use Parameterized method of calling when you call the stored procedure instead of EXEC method. You can’t feel the performance when few call to stored procedure. You can feel this performance difference when more calls to stored procedure (10000 calls frequently).