Are stored procedures faster?
This debate has been going on for years.
"Back then" when I was learning the basics of database management, one of the advocating points for the usage of stored procedures (SP) was performance. Fast forward some years and all I have read is that they perform exactly the same as a parametrized query. Is this correct?
Probably depends on your RDBMS, the SQL statement itself and your very specific scenario, but most likely the answer is no. In Microsoft SQL Server and Oracle, Stored Procedures are compiled and then stored in a global stored procedure cache, in MySQL the process is somewhat different. This is usually the reasoning behind why they perform better.
In SQL Server, when you execute a stored procedure what happens is the following:
- The syntax is checked.
- A plan is determined in order to execute the SQL statement (use a clustered or non-clustered index, etc.).
- The SQL is executed.
- The SP is cached.
The thing is, from SQL Sever 2005 onwards, all SQL statements are compiled and cached, regardless if they are a SP or inline code.
Network traffic is another argument of why stored procedures perform better, since they are executed in batches rather than sending multiple requests. This point however, is very debatable.
If there are many applications requesting the SQL server it can lead to increased network traffic, depending on the number of transactions. If for example, a heavy SQL query is sent to the server in-line, it would need several network packets to transmit the code to the server, rather than executing something that is already stored in the server.
With modern broadband connections, or cases in which the SQL and application code are in the same data center, this point may be negligible. Again, it depends on the situation. Whatever your case may be, always try to reduce the number of round trips.
So, why still use a stored procedure?
Security
- Permission granting: You can grant or revoke permissions to individual SP's.
- SQL injection prevention: Although not specific to SP's, since parametrized SQL statements can achieve the same, it is still a valid reason to choose SP's. Try not to execute them as a string!
- Securing just the data and scripts can be easier than applying security to your whole application's code.
Maintenance
- Because scripts are located in one place, modifications can be easier. It is generally easier to modify a stored procedure than a hard-coded SQL statement in your application.
- However, using stored procedures adds yet another layer in your application design, so it may actually be harder to maintain, it all depends.
Testing
They can be tested independent of the application.
Isolated Business Rules
By applying business rules in your Stored Procedures means that there should be no confusion regarding where your application's logic is; effectively reducing disparate code files.
Alternatives to Stored Procedures
- An object-relational mapping
- Parametrized queries
- A NoSQL database
tl;dr: No, but there are still valid reasons to use Stored Procedures.
Bibliography
Low, G. (2009, August 1). Plan Caching in SQL Server 2008. Retrieved July 3, 2015, from https://msdn.microsoft.com/en-us/library/ee343986.aspx
Carpentiere, C. (2004, March 1). An Evaluation of Stored Procedures for the .NET Developer. Retrieved July 3, 2015, from https://msdn.microsoft.com/en-us/library/ms973918.aspx
Grant Permissions on a Stored Procedure. (n.d.). Retrieved July 3, 2015, from https://msdn.microsoft.com/en-us/library/ms345484.aspx
How the Query Cache Operates. (n.d.). Retrieved July 3, 2015, from http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html