In some companies including leading ones stored procedures are labeled as bad practice and banned forever, in others they are praised for better performance. Let’s take a closer look into it.
Caching the execution plan
Let’s start from the performance perspective. Whenever you submit a query to the database system, it will need to create a query execution plan. This is an ordered set of step, which tells the database how to get your data. Every query requires a query plan before it is actually executed.
Since there are different way how to get there, the DB will evaluate different plans and finally choose the one it considers the best to proceed. It is not guaranteed, that this one is optimal for the query, so sometime DB users/admins have to manually optimize it.
Each and every time a query is submitted, it has to run through the procedure of finding the execulation plan. Stored procedure on the other hand should be faster because the execution plan can be created and cached the moment the procedure is added or run for the first time is the assumption. Let’s see what the MSSQL server has to say about it:
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.http://msdn.microsoft.com/en-us/library/aa174792.aspx
I assume that this statement is also valid for other database systems such as Oracle, MySQL and so on. Based on this fact, the advantage of using stored procedure lies not in performance because database systems does cache also plain and parameterized queries.
But it is still faster!
It depends how your stored procedures are written. If you just put a single query inside it, it will not be (significant) faster. Just do the benchmark. If you put complex statements and/or some logic that filters the result according to your needs, then the amount of data, that needs to be transferred is lower. Hence you save the traffic and it arrives faster.
Don’t put business logic in stored procedures!
Usually when you start merging tables and putting filtering logic, you tends to end up putting business logic into stored procedures. And this causes your business logic to be splitted into multiple places/layers. And this is something you should avoid. So be very careful on this road and only put “simple” logic to consolidate, aggregate and filter data. Or in some cases you may even need to iterate. But you if do it too often, it means your schema is not optimal.
“Stored procedure are more secure”
Indeed, using stored procedures instead of granting direct access to the tables provide an additional layer of protection and also a much more granular control over who can do what with the data because you can grant/revoke permission for each stored procedure. But think about the following scenario, you have 200 tables. Now you need to create 4 stored procedures to cover the CRUD operations for each table. Which means that you end up writing 800 boilerplate stored procedures. Have fun ;). I was there on a project, where we did that and believe me, it’s not funny.
As a general statement, I have to say, that I don’t think that a concept is in general bad or good and should be avoided by any means. It is a tool for a certain purpose and if we use it for that purpose it generally leads to better result. But if we don’t understand it and use it for everything or nothing, then it is rather the problem with the user not the tools. e.g. If you buy a tank and go into war, the tank will turn out to be very useful. Take the tank to go shopping, it will be hard to find a parking slot and… also huge public attention, the police, national guard, legal stuffs and so on.
The same applies to stored procedures. Make a clear analysis, for which purposes you want to use stored procedures and decide on a case-by-case basis. Stored procedures can offer more granular access control, it can increase the performance of your application and also add an abstract level to the tables itself.
On the other hands, stored procedures are not meant to be a replacement of your code, so keep the logic small and simple. As such, it is harder to debug, to test and to refactor your stored procedure. But if you decide to use them, you should put them also in the source version control of your project.