The Benefits of Stored Procedures

The ‘debate’ over Stored Procedures comes up a lot, mostly in the PHP forums I frequent and I have become tired of repeating myself. So this post summarises the benefits and reasons for using Stored Procedures and I can just point people to it in future.

Benefits of Stored Procedures

• Precompiled execution. The Database Server compiles each stored procedure once and then reutilizes the execution plan. Query strings on the other hand have to be syntax checked, then the execution plan is calculated by the query parser and only then can the query be executed. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced network traffic. Calling a stored procedure with an array of parameters takes less characters than the full query string. This is especially true when the target query is long and complex.
• Efficient reuse of code. Stored Procedures can be called again and again from different code modules, scripts and applications instead of each one having to build its own query string.
• Code seperation and abstraction. Using stored procedures means that the sql is all in one place – on the database where it should be. This centralisation makes it easier to develop and maintain the sql itself since it is not scattered around in other code and languages. All the client code will contain are the CALLs or EXECs.
• Enhanced security controls. You can grant users and clients access only through the stored procedures and deny access to the tables themselves. This makes it easier to control what users can and cannot modify, or even see.