Are Stored Procedures Faster than Ad-Hoc Queries?
Yet another question was posted in the public ADO.NET newsgroup and I decided to use this forum to answer this fairly common question: Are Stored Procedures (SPs) faster than ad-hoc queries? One misconception is that SPs are compiled into some binary-like code and simply loaded into memory on each invocation--kinda like a DLL. They aren't. In order to take advantage of the current data "statistics" (how the information is distributed in the tables being referenced), each query is recompiled (to some extent) at runtime.
No, not every statement is recompiled on each use. The mechanism works like this (somewhat simplified): When you execute a query (parameterized or not) it's syntax checked, compiled, placed into the common RAM cache and executed. The compiled query sits in the RAM cache until space is needed for something else. When you execute the same query (or one that's pretty close--perhaps with a different parameter value) there is no need to recompile the query--the cached query is simply rexecuted with the new values.
A stored procedure works the same way but the server can recognize it more quickly and knows if it has its query plan in cache--it does not have to be checked for correct syntax--that was done when it was first created. So, yes, there is a little performance benefit whenrunning a SP when compared to an ad-hoc query. While there is the additional (minor) benefit of the number of bytes sent to the system to execute the SP is far lower (just the string that has the name and parameter values)--this benefit is not that great.
What Are the Benefits of Stored Procedures?
A real benefit to SPs is in centralized management of the logic in the SP. SPs get the code OUT of the client applications so when the logic needs to be changed developers (and DBAs) can change it without redeploying the application (assuming the signature does not change). This is good. This means it's cheaper to deploy SP-based applications as it does not have to be done as often (hopefully only once). It also means that the client application need not have access to the base tables. Most corporate DBAs won't permit this. It's their job to protect the data so they limit rights to the base tables--granting rights to selected SPs and Views. When I ask a mob of people in one of my sessions how many are not using SPs, I get a few hands--the vast majority (over 95%) use them, insist on them and fully leverage the additional power they add to their applications. Consider that a SP is a program--it might be several hundred lines long. It means that a single invocation from a client application can handle a dozen (or more) operations and return information about each one to the client. Most serious application shops eventually find the need for SPs. They find that it's far too expensive to handle low-level table operations on the client and they never have to worry about SQL injection...
Consider that SPs are supported on all versions of SQL Server except SQL Everywhere--using the exact same syntax.