I spoke to a developer today about the differences between a stored procedure and ad-hoc parameterized sql statement. The conversation focused on – “Is a stored procedure faster than an ad-hoc parameterised query?” The short answer : “it depends”

 Firstly some definitions:

According to BOL : “A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.”

A parameterized query is defined when using sp_executesql or the forced parameterization is enabled.

 An example of sp_executesql

EXECUTE sp_executesql

          N'SELECT col1 FROM Companies 

          WHERE CompanyID = @companyID',

          N'@companyID INT',

          @companyID = 3;

Forced parameterization in BOL “When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement, submitted in any form, is converted to a parameter during query compilation”

 Ad-hoc query create execution plans. Stored procedures create execution plans. What are the differences and similarities?

 1)Stored procedures don’t have syntax checked at run-time. Already done at compilation.

2)Ad-hoc queries upon query execution  – syntax is checked , compiled , placed into memory and executed.

3)Plans remain in memory until space is required.Depending on activity it can eventually get flushed

4)Stored procedures , once compiled , don’t need syntax checked. Check SQL Server query plans in cache

 I ask developers what is the purpose of the application? Is it a distributed point-of-sale application? Is it a Decision Support System? Will multiple developers need access to sql code? Are the query requirements suitable to ad-hoc queries or stored procedures?

Map out the performance stack of the application and create benchmarks. Use the benchmarks to gauge performance.

Author: Jack Vamvas (http://www.sqlserver-dba.com)