Stored Procedures: Good or Bad?
December 15, 2008 – 22:06A 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.
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.
3 Responses to “Stored Procedures: Good or Bad?”
Hi Pete,
Just seen your blog on SPs. I would also add
versioning – SPs are quite to hard to put in the same repository as the source code – finding who changed what and when can be tricky.
Performance problems – SPs give a performance boost because they store a pre-compiled query plan – They need to be recompiled periodically to become aware of new indexes or data distribution changes
By Tom Walder on Feb 27, 2009
I’d say stored procedures are great and can be used to ensure that application code is relieved of a great bulk of SQL.
Of course it helps to have a DBA around as there is a whole other tier of code management needed around procedures. Not least publicising that they are available to use!!
Speed may be less of an issue than the convenience of a solid set of calls that can be made for data. That said the nastiness of doing a SQL transform of a table using cursors falls far behind the relative ease of doing the same thing in PHP.
I’m a bigger fan of views though and don’t think enough use is made of them. Especially when moving one database’s schema to another or in developing new applications. One of the great things is that they can prevent duplication of data whilst giving new structures – brilliant for prototyping as well.
On balance though keeping away from mySQL stored procedures seems like a good idea. The support is nowhere near as good as that for SQL Server, but they are very different products.
By Ian Lewis on Jun 4, 2009