Stored Procedures: Good or bad?

A project team rewriting one of our web products has made the decision to jump ship from SQL Server to the open-source MySQL database and, at the same time, to place a greater emphasis on the use of stored procedures. Since that decision, there have been various other projects where the choice of moving some business logic to the database server has had to be contemplated.

My initial reaction to the use of stored procedures was hesitant.  I work in a small company with no dedicated database programmers and a relatively small team of PHP experts. Due to this balance alone, I consider a greater reliance of stored procedures to be a risk.  But there is one very good reason to think the move may not be so risky after all: it takes load off the network – which might also result in a net performance gain.

Wary

But my initial wariness still stands – and continues for all the projects for which I’ve considered stored procedures recently.  The following disadvantages overwhelm that performance gain:

  • Language: the extended SQL language used in stored procedures is relatively immature and doesn’t lend itself to certain complex logic.
  • Tools: development tools for MySQL stored procedures (including debugging aids) are very limited.
  • Server load: increased processing on the database server is more difficult to accommodate (database servers are more difficult to scale than Apache/PHP web servers, for example.)
  • Deployment headaches: it can be hard enough to keep database and application code in sync without causing even a blip of downtime.  Introducing stored procedures introduces another element which needs careful management.  It’s not rocket science, but it’s just more to think about – and more to go wrong.
  • Portability is limited (DB2 has a similar language to that used by MySQL, but otherwise, you’re pretty much stuck if you want to move to another database platform.)  That said, it’s not exactly an everyday occurrence, is it?
  • Performance(!): the performance gain you may expect from stored procedures might often be a myth anyway. Stored procedure code executes slowly when compared with many application programming languages – so the benefits of reduced network traffic might soon be eaten into.

So, for the time being, while we have the skills to focus development of business logic in PHP, that’s just what we’ll do.