Just a moment ago, I read this post by Peter Gulutzan. In this post, Peter explains a little trick that allows you to capture the SQL state in a variable whenever an error occurs in your MySQL stored routine code.
MySQL For the uninitiated: in MySQL stored routines, you can declare
HANDLERs which are pieces of code that are executed only in case a particular
CONDITIONoccurs. This device serves the same purpose as a
Now, one of the long-standing problems with MySQL
HANDLERs - the inability to explicitly raise a CONDITION - has recently been solved by implementing the
SIGNALsyntax. The most important issue that remains is the inability to discover exactly which error occurred. You need this especially when you are writing a generic 'catch all'
HANDLER(for example, on that is triggered in response to
SQLEXCEPTION) and you want to write information regarding the error to a log.
Peter's Trick to Capture SQLSTATETo cut a long story short, Peter's solution is based on writing a
HANDLERfor all known
CONDITIONs in advance. Here's a fragment of his code to explain:
BEGINAs Peter points out, it's tedious, but it works. (That is to say, it works better than not having anything)
DECLARE EXIT HANDLER FOR SQLSTATE '01000' BEGIN SET @e='01000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE09' BEGIN SET @e='XAE09'; RESIGNAL; END;
...remainder of code...
Performance?Now, one particular paragraph in Peter's post caught my eye:
I added 38I respect Peter a great deal - if he's got something to say you do well to listen and take his word for it. However, this time I was curious to find out if I could measure the effect of the
DECLARE EXIT HANDLERstatements at the start of my procedure, just after the variable declarations. These lines are always the same for any procedure. They’re not executed unless an error happens so I don’t worry about speed.
HANDLERcode at all.
MethodThe code I used is a simplification of Peter's code. I tested it on MySQL 5.1 because I was just interested in the impact of a
DECLARE HANDLERstatement. In my case, the
BEGIN...ENDblock of the handler does not contain the
RESIGNALstatement, and my function does not drop a table but simply returns 1. This is important, as none of the
HANDLERs is ever triggered by my code.
Last week I wrote how seemingly small changes in MySQL stored routine code can have a surprisingly large impact on performance. In that particular case, I already had a hunch about which things could be improved. In this case, I just didn't know so I created a series of functions with 2, 4, 8, 16, 32 and 38
DECLARE HANDLERstatements, and again I used the
BENCHMARK()function to measure the time it takes to execute it 100,000 times. I did warm-up runs, and repeated the measurement 5 times for each function variant.
ResultsThe graph below summarizes my observations:
The squares in the graph are the measurements - each one represents a version of the function. Along the horizontal axis you see the number of
DECLARE HANDLERstatements in that particular version of the function. The number of seconds it took to execute the function 100,000 times on my laptop using
BENCHMARK()is on the vertical axis.
As you can see, there seems to be a linear relationship between the number of
DECLARE HANDLERstatements and the time it takes to execute the function. This is in itself a surprise. I mean, I would expect a little overhead per
DECLAREstatement when the function is compiled initially. After that, it is cached at the session level, and beyond that point I would not expect statements that do not execute to have any impact.
So how badly do the
HANDLERdeclarations slow our function down? Well, I measured an average of 0.38 seconds for 2, and an average of 0.55 seconds for 38
DECLARE HANDLERstatements respectively. The difference, 0.18 seconds is a little less than 50% of the function variant with 2
DECLARE HANDLERstatements, and a little more than 30% of the function having 38
ConclusionTo be fair, the function I tested doesn't actually do anything, and if your function or stored procedure does some real processing, the overhead may be neglectable. However, you can clearly see that even just declaring a handler has a measurable negative impact on performance. The essence of Peter's trick is to actually always write a
DECLARE HANDLERfor each possible condition and to do this for each stored routine. You will certainly suffer a peformance hit for small functions, esp. if they get called a lot.