MySQL Stored Procedures problems and use practices
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.
del.icio.us
digg13 Comments
Hey Peter,
“Only SQL as a Language” - it’s all right, “foreign” constructs came from SQL 2003 standard, I hope all database vendors would follow it, including Oracle
And I so agree with a lack of debugging (breakpoints, step by step execution, variable content, etc), and proper messaging.
Dmitri
Comment :: June 12, 2007 @ 8:15 am
I completely agree, trying to develop stored procedures on MySQL is a nightmare. No debugging, you have a syntax error often gives you the wrong line.
You also missed the strange situation you are not allowed to use variable in certain constructs - e.g. in the LIMIT clause
Comment :: June 13, 2007 @ 10:47 am
I also completely agree. We are developing some complex game logics using stored procedures - in order to avoid non-useful data roundtrips between server application and database - but it seems that there is no way to trace down MySQL logfile every SQL statement executed in a stored procedure/function… Therefore we can’t use any analysis tool and we must check every index and every SQL statement by ourselves, especially when game logic design changes (which is quite often…).
Comment :: June 13, 2007 @ 7:10 pm
vogon5,
Right I mentioned I’m not trying to look at limitations and gotchas of stored procedures themselves.
Comment :: June 14, 2007 @ 6:37 am
[...] features without risking your data. Peter Zaitsev’s article explaining the problems about using stored procedures in MySQL is very noteworthy. MySQL guys should think twice before using them. MySQL Cluster SQL Tips by [...]
Pingback :: June 15, 2007 @ 5:47 am
how to run in mysql browser
Comment :: May 22, 2008 @ 11:27 pm
I completely agree. MySQL is clearly sub-par in many aspects, specifically the development tools. DECLARE statements that work in some contexts and not others, variable initialization inconsistencies, lack of debugging ability in the provided tools, IF/CONDITIONAL confusion, operator confusion ( :=/= ), and the list goes on an on. Maybe they should hire Anders Hejlsberg to fix this lemon. My guess is Sun will make this better but then charge for tools that target real devs.
They’re very fortunate it’s a free tool. Otherwise I’m quite sure it would have been abandon years ago.
Andrew
Comment :: July 23, 2008 @ 9:19 pm
hi im harpreet. my problem concern to stored procedure.i m unable to create stored procdure………so plz help me
Comment :: September 16, 2008 @ 5:08 am
hi im harpreet. my problem concern to stored procedure.i m unable to create stored procdure in java………and after wards i wanna to access stored procdure…………
Comment :: September 16, 2008 @ 5:10 am
harpreet,
You have problem concern with stored procedure as do I too very much. Java stored procedure create does work not. Plz me help too.
Comment :: September 22, 2008 @ 9:13 am
hi im yukipupu. i problem have java procedure stored. two day is my first time. plz me help give.
Comment :: September 24, 2008 @ 12:53 pm
Inside DECLARE EXIT HANDLER ON SQLEXCEPTION, is there any way to find the error code and/or message that MySQL would have returned to the caller if the handler wasn’t there?
Or is it necessary to write an individual handler for each and every possible error code in each and every stored procedure…
Comment :: September 25, 2008 @ 5:03 pm
oops, meant
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
— in here, how do I see what the error was, and what message MySQL would have returned?
END;
Comment :: September 25, 2008 @ 5:06 pm