SQL: Once again, for the record...

Performance is not a compelling reason to choose stored procedures over dynamic SQL.

At work today I was surprised to overhear a suggestion to dynamically create stored procedures in SQL, just "to take advantage of the added performance of stored procedures" over dynamic/ad-hoc/inline SQL.

So here we go again, for the record:

Performance of Stored Proc vs. Dynamic/Ad-hoc SQL

Actual, simplistic test by BlackWasp:

http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx

SQL Books On Line:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.
http://msdn2.microsoft.com/en-us/library/aa174792(SQL.80).aspx

DeKlarit dev take:

People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise. Fortunately, when they go and ask the LinQ for SQL/Entities team they get the same answer than they get from me. They are not.
http://weblogs.asp.net/aaguiar/archive/2006/06/22/Stored-Procs-vs-Dynamic-SQL.aspx

There are lots of good reasons to use Stored Procedures, and lots of good reasons to use Dynamic SQL.  Performance is rarely a good reason to chooswe one way or the other, and if performance is  the decision factor often dynamic sql comes out on top, e.g. if you can use dynamic sql to eliminate an OR for example (as when using optional query parameters in an advanced search form).