To be honest I’m not a big fan of Stored Procedures, at least not in the form they are currently implemented in MySQL 5.0
Only SQL as a Language Which is ancient ugly for algorithmic programming and slow. It is also forces you to use a lot of foreign constructs to “original” MySQL style – to process data via cursors, handle error via Handlers etc. If you spent last 10 years writing Stored Procedures for Oracle or DB2 it may be cool and convenient for you, but not for me 🙂
Lack of Debugging I like to be able to debug software, if not full blown debugger I’d like to have things like echo and var_dump. Due to the context of execution these are not easy though. Of course you can code a little helper Debug Storage Procedure which will log some information in MySQL table but it is not convenient enough.
Bad Parser Error Messages MySQL Parser is in general far from perfect when it comes to error handling. “You have an error in your SQL syntax … near ” at line 1″ is not very helpful even when dealing with large queries but for Stored Procedures that is the real issue. So you have to overcome few road blocks even before you start fighting with debugging.
No Profiling tools If you’re interested in Performance you need a way to profile what inside stored procedures is taking the time, which is not something readily available. Even if you look at most simple and typically most time consuming part of Stored Procedure execution – running of SQL queries – you do not get these logged, instead slow query log will contain full stored procedure calls.
On the other hand Stored Procedures Indeed can help to Improve MySQL Performance. For DBT2 benchmarks we’ve tried a while back MySQL 5.0 was about 10% slower than 4.1 without stored procedures but was 20% faster if Stored Procedures are used. And this is of course not the limit. You may also have other reasons to use Stored Procedures besides performance and these can be valid.
What I tend to do if Stored Procedures are helpful for Performance reasons is to have two code versions, one using stored procedures and other doing same thing using direct statements. This allows to debug and profile most of the things comfortably and works pretty well especially if you keep your stored procedures simple so you do not add much bugs converting code from your language of choice to SQL.
There is one more thing you should beware with Stored Procedures is to put a lot of computational load in them. Not only the language is slow at it but you’re also loading CPU on your Database Server, which is typically more expensive to scale than Application/Web Servers. It is OK however to do some simple math if it can help you to avoid sending large result set back to the client as in this case server may need to do more work to send it back than Stored Procedure to process it.
Stored Procedures also have number of performance gotchas which I’ve been running into production but have not taken a time to research into details. So just beware.
I also hope other time MySQL and third parties will develop tools and extensions to target many of the problems mentioned.
Resources
RELATED POSTS
