The next argument often raised by SP advocates is that SPs offer better performance. Rob says:
When stored procedures are used SQL Server can cache the ‘execution plan' that it uses to execute the SQL vs. having to recalculate the execution plan on each request – would you recompile your business logic class on each request? …probably not.
Note that Rob initially used the word “pre-compile or cache” when describing SPs, which he has edited out of the post, as noted by Rob at the bottom of the entry.
The counter to this is provided by Frans, who quotes SQL Server Books On-Line, as I do here:
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.
The important thing to note about this is that there is no “compiling” of SPs in the traditional sense. In some environments (Oracle, I am told, and in a number of other databases as well), SPs really are converted to a different programming language (such as C or C++) and compiled to native code. This is not the case in SQL Server, for SPs or ad-hoc SQL. SPs have a slight advantage in that the execution plan can be cached and found a bit more efficiently.
Are there any other performance advantages to SPs? In a number of reasonably rigorous tests, I initially did not see any significant differences in performance. I took a SELECT statement that was pulling from a table using a random ID (generated using the RAND() function in SQL Server) and for a single SELECT, there was no difference between an ad-hoc SELECT statement and an SP. I expected this, so I duplicated the statement a number of times (100 times, in fact) and compared the execution times, and again I saw no difference. Even when connecting to a remote server over the Internet, I saw no difference.
After more thought, I realized that in my stored procedures I am often doing complex work, building up a temporary table and SELECTing the result of the stored procedure. Rather than doing the individual SELECT statements, I next tried a test in which I created a temporary table and ran 100 INSERT statements in which I used a SELECT for the values to insert. Finally, I ran a SELECT against the temporary table, and then dropped the temporary table. In this example, I did see a reasonable performance advantage in using an SP to perform the task, on the order of 10:1 when operating on a SQL Server both across the LAN and over the Internet.
So what are the performance benefits of using SPs? If you have complex work to do with the data, it makes good sense to work as close to the data as possible. If you are doing complicated operations on data that cannot be expressed easily using standard SQL set-based operations, it is possible that SPs will be quicker than looping through results on the client side and doing the calculations. Note that the issue of when to use or not use SPs will change when SQL Server 2005 emerges out of beta, since you will be able to use VB.NET and C# to create stored procedures that are genuinely compiled.
Performance: Some SP advantage when complex operations are involved.
Comments