Link Details

"The results show that the difference in speed between dynamically generated SQL and stored procedures is negligible in most cases."

Seems implausible.

It would be interesting to have a look at the SQL queries and stored procedures. If the procedures contain dynamic queries, it's not strange that S.P. match closely the dynamic SQL: their advantage over pure SQL is that they are already compiled when they are invoked, but they can't be if their content is dynamic, so it would be exactly the same process - only that it is started in the DB instead of in the client.

Otherwise, I can't explain these results. In a project with an Oracle DB we migrated some complex queries from SQL to PL/SQL, and we were able to reduce the query time quite a lot, although we didn't take exact measures.

The stored procedures used in the article contain the same statements as in the .NET code. They are in the zip file you can download from the top of the page. The results do seem to bear out the author's statement when run on my servers. I found this surprising but when I started digging into the detail I did find this quote on 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."


I guess that this is the same for SQL 20055. With LINQ and other technologies using dynamic SQL I guess Micro$oft wanted to make sure that it was as quick to use dynamic SQL as using SPs. Embarassed I didn't know about this as far back as in SQL Server 7.0 though.

Oracle is a completely different beast though. This article is clearly for SQL Server only.

I have added the SQL statements to the article too now.