Stored Procs performance are better then Dynamic SQL. Is it A myth?

People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise.

Andres Aguiar's wrote is his blog that: "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.
"

To add more evidence, a couple of weeks ago he was in Redmond in a meeting with the ADO.NET team, and a Tech Lead from that team wrote the code below and said 'this is the fastest way to execute this SQL sentence with the .NET framework today'.

SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = @"
SELECT Sales PersonId, FirstName, HireDate
FROM SalesPerson as sp
INNER JOIN Employee AS e ON sp.SalesPersonID = e.EmployeeID
INNER JOIN Contact AS c ON e.EmployeeID = c.ContactID
WHERE e.HireDate < @date";
cmd.Parameters.AddWithValue("@date", date");

DbDataReader r = cmd.ExecuteReader();

Why is this _faster_ than a stored procedure? Because the SQL Server engine could select a better execution plan depending on the value of the @date parameter.

Even in that room there was people that were surprised.

So here are some points why not using stored procedures:
1. Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.

2. Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.

3. Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?

4. Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.

5. Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

So why use Stored Procedures at all? Conventional wisdom says we do it because:
1. Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.

2. Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.

3. Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.

4. Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.

5. Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

one more interesting fact on Stored Procs:
Everyone who thinks stored procedures are pre-compiled, say "Aye!". Whoa, what a noise! For all of you who said "Aye!" a few seconds ago: open SqlServer's Books Online (v7 or v2000, doesn't matter), search for "cache execution plan". You'll find fine articles like "Execution Plan Caching and Reuse" and "SQL Stored Procedures". Let me just quote some lines from the "SQL Stored Procedures" article:

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.

So what do you think? Is it a myth? :-)