MySQL Stored Functions: Impact of DECLARE HANDLER on Performance

Hi again!

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 CONDITIONs and HANDLERs

For the uninitiated: in MySQL stored routines, you can declare HANDLERs which are pieces of code that are executed only in case a particular CONDITION occurs. This device serves the same purpose as a try ... catch block which is supported in many popular programming languages like Java, JavaScript, C++, PHP5 and C#.

Now, one of the long-standing problems with MySQL HANDLERs - the inability to explicitly raise a CONDITION - has recently been solved by implementing the SIGNAL syntax. 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 SQLSTATE

To cut a long story short, Peter's solution is based on writing a HANDLER for all known CONDITIONs in advance. Here's a fragment of his code to explain:

BEGIN
  ...
  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...

END;

As Peter points out, it's tedious, but it works. (That is to say, it works better than not having anything)

Performance?

Now, one particular paragraph in Peter's post caught my eye:

I added 38 DECLARE EXIT HANDLER statements 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.

I 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 HANDLER code at all.

Method

The 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 HANDLER statement. In my case, the BEGIN...END block of the handler does not contain the RESIGNAL statement, 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 HANDLER statements, 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.

Results

The 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 HANDLER statements 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 HANDLER statements and the time it takes to execute the function. This is in itself a surprise. I mean, I would expect a little overhead per DECLARE statement 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 HANDLER declarations 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 HANDLER statements respectively. The difference, 0.18 seconds is a little less than 50% of the function variant with 2 DECLARE HANDLER statements, and a little more than 30% of the function having 38 DECLARE HANDLER statements.

Conclusion

To 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 HANDLER for 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.

A fast, single pass method to calculate the median in MySQL

After stepping off of the GROUP_CONCAT() solution for calculating quantiles I figured it would be nice to find a better way to calculate the median too.

Solution

I previously wrote on how to calculate the median using GROUP_CONCAT(), but I think that this is a better way:

SELECT     AVG(length) AS median                 -- take the average of left and right median
,          MIN(length) AS left_median            -- 
,          MAX(length) AS right_median           --
,          @l          AS left_median_position   --
,          @r          AS right_median_position  --
FROM (
     SELECT     @n, length                       -- @n is just here to facilitate debug
     FROM       film
     CROSS JOIN (
                SELECT @n:=0
                ,      @r := COUNT(*) DIV 2 + 1  -- right median or true median
                ,      @l := COUNT(*) DIV 2      -- left median, or true medain
                           + IF(
                               COUNT(*) % 2      -- even or odd? 
                             , 1                 -- odd, need next value
                             , 0                 -- even, need true left median
                             )
                FROM   film
                ) ``
WHERE      (@n:=@n+1)                                   -- row number
BETWEEN    @l AND @r                                    -- select two middle ones
ORDER BY   length                                       -- need to sort to get middle values
) ``

Apart from not relying on setting the buffer size for group_concat_max_len, this solution is also faster than the GROUP_CONCAT() solution. For example, calculating the median amount from the payment table in the sakila database takes some 0.12 seconds using the GROUP_CONCAT() solution and about 0.03 seconds with this method.

Explanation

At the heart of the solution are the user-defined variables:

• @n: the row number


• @l: the position of the 'left median' that is the row number with the highest value in the lower half of all rows


• @r: the position of the 'right median', that is the row number with the lowest value in the higher half of all rows

These are initialized in the inmost subquery:

SELECT @n:=0
,      @r := COUNT(*) DIV 2 + 1  -- right median or true median
,      @l := COUNT(*) DIV 2      -- left median, or true medain
           + IF(
               COUNT(*) % 2      -- even or odd? 
             , 1                 -- odd, need next value
             , 0                 -- even, need true left median
             )
FROM   film

Note that this yields one row, initializing the row number @n to zero. We can calculate the position for the right median immediately by doing

COUNT(*) DIV 2 + 1

You see, first we divide the total number of rows by two. If there is an even number of rows, COUNT(*) DIV 2 will give us the left median, and adding 1 is then by definition the right median. If COUNT(*) is an odd number, it still holds: the integer division rounds down to the nearest integer value, and adding 1 then gives us the position of the true median in that case.

The calculation of the left median is along the same lines:

@l := COUNT(*) DIV 2      -- left median, or true medain
    + IF(
        COUNT(*) % 2      -- even or odd? 
      , 1                 -- odd, need next value
      , 0                 -- even, need true left median
      )

We just have to take care that in case we do have an odd number of rows, we need to pick the true median position here too.

To do that we first find out if there is an odd number of rows using COUNT(*) % 2. This calculates the remainder of dividing the total number of rows by two. In case of an odd number of rows, the remainder is 1 which is considered to be TRUE by the IF function. In this case, 1 is added, effectively making @l equal to @r (that is, both hold the position of the true median). In case of an even number of rows, we add 0 as COUNT(*) % 2 is already the desired position of the left median.

Now that we have these values, we can use @n to generate a rownumber, which we can compare against the calculated @l and @r values:

WHERE      (@n:=@n+1)                                   -- row number
BETWEEN    @l AND @r                                    -- select two middle ones
ORDER BY   length                                       -- need to sort to get middle values

And this will give us at most two rows in case of an even number of rows, and one row for an odd number of rows.

The final step is to average the values from the rows, which is required to calculate the median in case of an even number of rows:

SELECT     AVG(length) AS median                 -- take the average of left and right median
...
FROM (
...
) ``

Calculating Percentiles with MySQL, Round 2

My previous post on calculating percentiles with MySQL generated some comments and good discussion. In particular, I got some very interesting comments from Vladimir.

Basically, Vladimir was doubtful whether the GROUP_CONCAT() solution would be optimal in comparison to a JOIN. His proposal is to solve it like this:

SELECT   SUM(g1.r) sr
,        g2.length l
,        SUM(g1.r)/(SELECT COUNT(*) FROM film) p
FROM    (SELECT COUNT(*) r, length FROM film GROUP BY length) g1
JOIN    (SELECT COUNT(*) r, length FROM film GROUP BY length) g2
ON       g1.length < g2.length
GROUP BY g2.length
HAVING p > 0.9
ORDER BY p
LIMIT 1

First, this query sets up two identical subqueries in the FROM list using GROUP BY and COUNT() to calculate the number of occurrences of each distinct value. Then, these are joined and GROUP BY is again applied to calculate the total number of rows having a lower value. Finally, HAVING is used to find the groups in the upper percentiles, and LIMIT and ORDER BY are used to single out the one desired percentile value.

As it turns out, this solution is slower for moderately small data sets, but much faster for large data sets. He benchmarked it for a total of 999 distinct values on varying number of rows. Here are his slightly rounded numbers:

#rows:  group_concat:  groupby-join: 
4M      1 min 6    sec    5.3 sec
1M            5    sec    2.5 sec
100K          0.5  sec    1.6 sec

Although GROUP_CONCAT() seems to break down pretty soon, he also writes:

I must admit that when N of distinct rows reaches approx. 10K I get pretty the opposite results if the total number of rows is relatively small. Basically we get into the same situation as with joining the whole tables.

He concluded by saying:

But what I think is the right solution is having something like this on the server side:

SELECT COUNT(INC length)/(SELECT COUNT(*) FROM film) p, length 
FROM film 
GROUP BY length
HAVING p >= 0.9 ORDER BY p LIMIT 1 

Where "INC" is a flag that tells the server to not reset per-group counters in the aggregate functions. This would be quite a trivial change in the Item_sum class and would make sense not only for SUM, but maybe also for MIN, MAX, AVG, COUNT and maybe some other aggregate functions.

So, COUNT(INC length) would be the cumulative count, or a running total of counts. The fun thing is, you can already do exactly that using user-defined variables. Look:

-- allow statement batching
DELIMITER go                

-- initialize
SELECT      0, COUNT(*)     
INTO        @cum_cnt, @cnt
FROM        sakila.payment;

-- calculate percentiles
SELECT      @cum_cnt:=@cum_cnt + COUNT(*) / @cnt as p,  -- running fraction of #rows per distinct amount
            amount 
FROM        sakila.payment
GROUP BY    amount 
HAVING p >= 0.9 
LIMIT 1;

go

and this gets us the result:

Query OK, 1 row affected (0.01 sec)

+-------------+--------+
| p           | amount |
+-------------+--------+
| 0.904542334 |   6.99 |
+-------------+--------+
1 row in set (0.03 sec)

Here is the equivalent GROUP_CONCAT solution:

SELECT  SUBSTRING_INDEX(
             SUBSTRING_INDEX(
                 GROUP_CONCAT(
                     p.amount
                     ORDER BY p.amount
                     SEPARATOR ','
                 )
             ,   ','
             ,   90/100 * COUNT(*) + 1
             )
         ,   ','
         ,   -1
         )   AS `90th Percentile`
FROM    sakila.payment AS p;

...and it is considerably slower:

+-----------------+
| 90th Percentile |
+-----------------+
| 6.99            |
+-----------------+
1 row in set (0.08 sec)

(sakila.payment has 16049 rows and 19 distinct values for amount)

So, the sane thing to do would be to forget about that GROUP_CONCAT idea, and use this method. It does not have the nasty drawbacks of having to mess with group_concat_max_len and I am pretty sure Vladimir's method will be faster across the board anyway.

The one thing you could object about is the extra query to initialize the user-defined variables. You can get around that by initializing those in a single row subquery in the FROM clause, a technique described by Baron Schwartz (see for example this post)

SELECT      @cum_cnt:=@cum_cnt + COUNT(*) / @cnt as p, 
            amount 
FROM        sakila.payment
CROSS JOIN (SELECT @cum_cnt:=0
            ,      @cnt:=COUNT(*) 
            FROM   sakila.payment) p
GROUP BY    amount 
HAVING p >= 0.9 
LIMIT 1;

This has the advantage that the variables are initialized in one go with the entire query, ensuring you are not accidentally working with uninitialized or garbage variables.

(BTW: If you want to do more with these user-defined variables, I can highly recommend more from Baron's site, see for example his article on advanced user defined variable techniques.)

UDFs at the MySQL User's conference

The MySQL User's conference will be held in less than a month from now!!!

This year there is quite a good number of sessions on adding your own functions and procedures, such as:

• Advanced Stored Procedures


• A Tour of External Language Stored Procedures for MySQL


• Code Generators for MySQL Plugins and User Defined Functions (UDFs)


• Extending MySQL


• Using User-defined Functions and Aggregates to Speed Up Your Data Warehouse Processing


• Writing MySQL user-defined functions

I will be doing the 3 hour tutorial on writing user-defined functions, and I am currently adding the last few final touches to my slides.

My tutorial will be very much a hands-on experience. The ambition is to allow people with some programming skills in either C/C++, PHP or Java to leave the room with a bunch of UDFs they created themselves during one of labs. With that and the supporting materials (slides and a handout with detailed instructions) the attendees will be able to write UDFs themselves, and have the knowledge that allows them to make a sensible decision when they have to choose between stored SQL functions, UDFs or raw expressions built of built-in functions.

Allow me to tell you a bit about UDFs - you might decide you want to take my tutorial ;)

User-defined functions

User-defined functions or UDFs are often confused with Stored SQL functions but unrightly so. Other than the fact that stored functions and user-defined functions can be created by the user (as opposed to hard-wired, built-in functions), they have little in common. If you want to know exactly what the difference is and what the strengths and weaknesses are of either feature, my tutorial is for you.

Features

For now I don't want unveil too much but I think that it might be good to point out a number of key advantages of using UDF's over stored SQL functions:

• Flexible argumentlists - UDFs allow a flexible number of dynamically typed arguments, and it is possible to identify arguments by name (rather than position).


• The ability to leverage the functionality from external libraries - UDFs can be linked to existing libraries in order to expose their functionality to SQL statements


• Aggregate functions - UDFs can compute a value for a collection of rows just like the built-in functions COUNT and GROUP_CONCAT

Neither of these features is available to stored SQL functions, and a generic workaround is simply impossible or far from trivial. So - UDFs might be for you if you need one of these things.

Performance

Another reason to use UDFs might be performance. UDFs are much, much better than stored SQL functions when it comes to computation. To prove this fact, I'd like to share a number of benchmarks I did.

For the benchmarks I use a stored procedure like this:

create procedure sp_<SOME-NAME>_benchmark(p_num int unsigned)
begin
    declare v_num int unsigned default 0;
    declare v_return <SOME-DATA-TYPE>;
    declare v_begin int unsigned default unix_timestamp();    
    while v_num < p_num do
        set v_return := <SOME-EXPRESSION>;
        set v_num := v_num + 1;
    end while;
    call sp_store_function_benchmark(
        '<SOME-NAME>'
    ,   p_num
    ,   unix_timestamp() - v_begin
    );
end;

As you can see, the procedure repeats the evaluation of <SOME-EXPRESSION>, depending on the specified number of iterations passed to the parameter p_num. Depending on the benchmark, an expression is plugged in place of <SOME-EXPRESSION>. The variable v_return is used to capture the evaluation result, and a suitable data type is used in place of <SOME-DATA-TYPE>. Before running the loop, the time is recorded. After the loop, the elapsed time, the number of repetitions and the benchmark name are stored in a table for later analysis. This allows me to test a series of increasing repetitions of the same expression.

I also use a sp_noop_benchmark() procedure that is identical to the one described above, except that it omits the assignment of the expresion. So, it lacks this line:

        set v_return := <SOME-EXPRESSION>;

This allows us to isolate the time spent on evaluating the expression and doing the assignment. I also use another control measurement that uses an assignment like this:

        set v_return := <SOME-LITERAL-VALUE>;

This allows us to estimate the time spent on the value assignment, which can be used to get a bit closer to the actual time spent only on expression evaluation alone. (Of course, this assumes that the time spent on the assignment alone is comparable for expressions, function calls and literals).

All these measurements were made by comparing single, complete calls many times. All tests were done on Ubuntu Gutsy Gibbon using MySQL 5.1.23. Compilation of UDFs was performed using gcc version 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2). No optimizations were employed.

Stored functions and UDFs

To compare UDFs and Stored functions, I first used simple "Hello World" expressions that all evaluate to a VARCHAR(14). This was measured:

• String literal sp_string_benchmark. The expression was:

  v_return := 'Hello, String!';



• Stored SQL function sp_ssf_benchmark. The expresion was:

  v_return := ssf_hello_world();

  The ssf_hello_world() itself was:

  
  create function ssf_hello_world()
  returns varchar(14)
  return 'Hello, SSFs!!!';



• User defined function sp_udf_benchmark. The expression was:

  v_return := udf_hello_world();

  The UDF itself was:

  
  my_bool udf_hello_world_init(
    UDF_INIT *initid, UDF_ARGS *args,
    char *message
  ){
    return 0;
  }
  char *udf_hello_world(
    UDF_INIT *initid, UDF_ARGS *args
  , char* result, unsigned long* length
  , char *is_null, char *error 
  ){
    *length = 14;
    return "Hello, UDFs!!!";
  }

Here are the raw results:

+----------+------+--------+-----+-----+
| repeated | noop | string | UDF | SSF |
+----------+------+--------+-----+-----+
|        0 |    0 |      0 |   0 |   0 |
|   500000 |    3 |      4 |   6 |   8 |
|  1000000 |    7 |      9 |  10 |  16 |
|  1500000 |   10 |     13 |  16 |  25 |
|  2000000 |   13 |     17 |  21 |  33 |
|  2500000 |   16 |     22 |  25 |  42 |
|  3000000 |   19 |     27 |  31 |  50 |
|  3500000 |   23 |     31 |  36 |  58 |
|  4000000 |   26 |     36 |  41 |  67 |
|  4500000 |   30 |     40 |  45 |  75 |
|  5000000 |   33 |     44 |  51 |  83 |
|  5500000 |   36 |     49 |  55 |  90 |
|  6000000 |   39 |     53 |  61 |  99 |
|  6500000 |   42 |     57 |  66 | 107 |
|  7000000 |   45 |     62 |  71 | 115 |
|  7500000 |   49 |     66 |  76 | 123 |
|  8000000 |   52 |     71 |  81 | 131 |
|  8500000 |   56 |     75 |  86 | 140 |
|  9000000 |   58 |     80 |  90 | 147 |
|  9500000 |   62 |     83 |  96 | 156 |
| 10000000 |   64 |     88 | 101 | 164 |
+----------+------+--------+-----+-----+

And here is a graph:

At a glance we see that doing nothing is the fastest, followed by the assignment of a literal string. After that, comes the UDF and the stored SQL function is by far the slowest. Another thing that is immediately apparent is that the execution time increases proportionally as the number of repetitions is increased. This means that we can essentially forget the entire series and focus on the measurements with the highest number of repetitions (which is presumably more accurate than the earlier measurements).

But exactly how much faster is the UDF as compared to the SQL function? Well, it depends on how you look at it. If we just divide the raw execution time of the stored function by that of the UDF, we see that the ratio is:

164/101 = 1.60

This would tempt us into thinking that UDF's are about 60% faster.

However, when we calculate it like this, we are also counting the overhead of the benchmark procedures themselves. We get a better result if we focus on only the expression assignments. We can express the performance in terms of the time required to execute the no-operation benchmark. So:

noop / udf = 64 / 101 = .63

and

noop / ssf = 64 / 164 = .39

This correction indicates that the UDF performs at 63% of the no-operation benchmark, whereas the stored SQL function performs at 39% of the no-operation benchmark. We could say that relative to the no-operation benchmark, UDFs are 24% faster as compared to SQL functions

Now we could stretch it a bit more and try to isolate only the time spent on evaluating the functions. To do that, we'd have to assume that "assignment" costs the same, no matter if we are assigning from a stored SQL function, user-defined function or string literal. If we do that, we get this figure:

literal / udf = 88 / 101 = .87

and

literal / ssf = 88 / 164 = .54

This correction indicates that the UDF performs at 87% of the string-literal benchmark, whereas the stored SQL function performs at 54% of the string-literal benchmark. This would indicate that relative to the string literal assignment, UDFs are 33% faster than SQL functions.

Now, I can imagine that a lot of people will have some reservations against this method of correcting the measurements. Personally I think the method is valid, although the result itself is not that useful. I mean, in real life, the fact is that we will be assigning the value of the function, and from that perspective it doesn't help us much to know how fast it would have been if we didn't perform an assignment. Another reservation that we may have is that this benchmark still doesn't tell us much more than the relative differences in overhead. Basically, both the UDF and the stored function are empty - so this benchmark can tell us nothing about performance in a more realistic case where the function is actually doing some work.

Addition

In an attempt to measure at least some basic processing I decided to benchmark addition too. I took a INTEGER as data type and did the following benchmarks:

• Integer literal: sp_num_benchmark with the expression:

  v_return := 3;



• Addition operator: sp_opr_benchmark with the expression:

  v_return := 1+2;



• UDF: sp_udf_benchmark with the expression:

  v_return := UDF_ADD(1,2);

  The code for the UDF is

  my_bool udf_add_init(
    UDF_INIT *initid
  , UDF_ARGS *args
  , char *message
  ){
    if(args->arg_count!=2){
      strcpy(message, "Require two arguments");
      return 1;
    } else {
      args->arg_type[0]= INT_RESULT;
      args->arg_type[1]= INT_RESULT;
    }
    initid->maybe_null= 1;
    return 0;
  }
  
  long long udf_add(
    UDF_INIT *initid
  , UDF_ARGS *args
  , char *is_null
  , char *error 
  ){
    if((args->args[0]==NULL)||(args->args[1]==NULL)){
      *is_null= 1;
      return 0;
    } else {
      return (*((long long*)args->args[0])) + (*((long long*)args->args[1]));
    }
  }
  



• Stored SQL function: sp_ssf_benchmark with the expression:

  v_return := SSF_ADD(1,2);

  The code for the function is

  
  create function ssf_add(l int, r int)
  returns int
  return l+r;
  

Here is the raw data:

+--------------+------+-----+-----+-----+-----+
| repeat_count | noop | num | opr | udf | ssf |
+--------------+----- +-----+-----+-----+-----+
|            0 |    0 |   0 |   0 |   0 |   0 |
|       500000 |    3 |   5 |   5 |   6 |   9 |
|      1000000 |    7 |   9 |   9 |  11 |  18 |
|      1500000 |   10 |  14 |  13 |  16 |  27 |
|      2000000 |   13 |  19 |  18 |  21 |  35 |
|      2500000 |   17 |  23 |  22 |  26 |  47 |
|      3000000 |   20 |  27 |  27 |  33 |  56 |
|      3500000 |   23 |  33 |  33 |  38 |  62 |
|      4000000 |   27 |  38 |  36 |  43 |  74 |
|      4500000 |   30 |  42 |  42 |  50 |  83 |
|      5000000 |   34 |  46 |  47 |  54 |  91 |
|      5500000 |   36 |  51 |  60 |  68 | 108 |
|      6000000 |   42 |  56 |  59 |  67 | 115 |
|      6500000 |   45 |  60 |  62 |  71 | 123 |
|      7000000 |   47 |  65 |  69 |  78 | 132 |
|      7500000 |   50 |  67 |  71 |  84 | 143 |
|      8000000 |   54 |  72 |  78 |  89 | 149 |
|      8000000 |   54 |  73 |  78 |  89 | 149 |
|      8500000 |   59 |  78 |  79 |  92 | 157 |
|      9000000 |   60 |  85 |  85 |  99 | 168 |
|      9500000 |   65 |  89 |  92 | 104 | 178 |
|     10000000 |   67 |  91 |  95 | 111 | 189 |
+--------------+------+-----+-----+-----+-----+ 

And here is the graph of the results:

We can immediately see that the result is quite similar to what we saw in the previous measurements. Let's look at the performance relative to assigning a literal integer:

    int / opr = 91 / 95 = 0.96

and

    int / udf = 91 / 111 = 0.82

and

    int / ssf = 91 / 189 = 0.48

So, A direct addition operator is less than 5% slower than a literal assignment. The UDF 18% slower than the literal assignment and 14% slower than the addition operator. The stored SQL function is quite a good deal slower: 48% slower than the direct addition operator and 34% slower than the UDF. If we want to compare only UDFs vs stored SQL functions, we should compare relative to the addition operator, which gives a minutely different result:

    opr / udf = 95 / 111 = 0.83

and

    opr / ssf = 95 / 189 = 0.5

Built-in functions and UDFs

While I was busy doing benchmarks, I also figured that it'd be nice to figure out how UDFs and built-in functions compare performance-wise. To make things slightly more realistic than simply returning a value, I chose to implement my own version of CONCAT().

Because CONCAT can take on a number of arguments I decided to measure the effect of a varying number of arguments too. I ended up choosing VARCHAR(10) for the data type, and tested these different benchmarks:

• Built-in function sp_bif<1..10>_benchmark - where <1..10> is a number from 1 to 10, and where the expression was:

  v_return := CONCAT(0[,1[,...,9]);

  That is, 10 different CONCAT measurements, from CONCAT(0) to CONCAT(0,1,2,3,4,5,6,7,8,9)


• UDF sp_udf<1..10>_benchmark - where <1..10> is a number from 1 to 10, and where the expression was:

  v_return := UDF_CONCAT(0[,1[,...,9]);

  That is, 10 different measurements of my UDF implementation of concat, from UDF_CONCAT(0) to UDF_CONCAT(0,1,2,3,4,5,6,7,8,9).The code for my UDF_CONCAT is:

  
  my_bool udf_concat_init(
    UDF_INIT *initid, UDF_ARGS *args
  , char *message
  ){
    int i;  
    size_t bytes = 0;
    for(i=0;iarg_count; i++){
      args->arg_type[i] = STRING_RESULT;
      bytes += args->lengths[i];
    }
    if(!(initid->ptr= malloc(bytes))){
      strcpy(message, "Error allocating memory.");
      return 1;
    } else {
      return 0;
    }
  }
  
  char *udf_concat(
    UDF_INIT *initid, UDF_ARGS *args
  , char* result, unsigned long* length
  , char *is_null, char *error 
  ){
    int i;  
    char *buff = initid->ptr;
    *length = 0;
    for(i=0;iarg_count; i++){
      if(args->args[i]==NULL){
        *is_null = 1;
        break;
      } else {
        memcpy(buff + *length, args->args[i], args->lengths[i]);
        *length += args->lengths[i];
      }
    }
    return buff;
  }
  
  void udf_concat_deinit(
    UDF_INIT *initid
  ){
    if(initid->ptr){
      free(initid->ptr);
    }
  }
  




I also measured the same string literal assignment and the noop benchmarks as control measurements. I won't give the raw results here - send me an email or post a comment if you'd like to have them. I do have a graph of the result here:
Note that for this graph I already subtracted the no-operation benchmark. This was done to make it easier to examine the data sets of the UDFs and Built-in functions. The graph does include the literal string benchmark, which is the orange line nearest to the X axis.

Now, what do we see here apart from the control benchmark? Basically, we see two bundles of series. The bundle nearest to the X axis corresponds with the built-in CONCAT() benchmarks. The bundle above that corresponds to the UDF benchmarks. So, this tells us that on every occasion, the built-in CONCAT() was faster than the UDF.

We can also see that there is a relatively small but measurable effect for passing more arguments. The calls with fewer arguments are consistently faster than the calls with more arguments within both the group of built-in and UDF calls.

Frankly, I didn't expect to see this. I had imagined that the effect of passing more arguments would be larger, so I expected to see pairs of UDF/Built-in function calls having the same number of parameters. Clearly, I was wrong.

So how much faster are UDFs? Well, if we look at the raw ratios of execution times for the largest number of repetitions, we see:

UDF_CONCAT(0) / CONCAT(0) = 91 / 107 = .85

Interestingly, if we compare that for the calls with 10 arguments we see the same ratio:

UDF_CONCAT(0,1,2,3,4,5,6,7,8,9) / CONCAT(0,1,2,3,4,5,6,7,8,9) = 91 / 107 = .85

So, UDF_CONCAT() would seem to be 15% slower than the built-in CONCAT(). Interestingly, there seems to be no difference between the call with the larger number of arguments, indicating that the performance impact of passing another argument is about the same for UDFs as it is for built-in functions.

What happens if we express the execution time relative to the no-operation benchmark? Well, we get:

noop / UDF_CONCAT(0)                     = 64 / 107 = .60
noop / CONCAT(0)                         = 64 /  91 = .70

and

noop / UDF_CONCAT(0,1,2,3,4,5,6,7,8,9)   = 64 / 122 = .52
noop / CONCAT(0,1,2,3,4,5,6,7,8,9)       = 64 / 104 = .62

Here we see that relative to the no-operation benchmark, the UDF is 10% slower than the built-in function. We could say that 10% is quite a lot, but it is more than half as large as the difference between UDFs and stored SQL functions.

We see something interesting if we look at the execution time relative to the execution time spent on the string literal assignment:

string / UDF_CONCAT(0)                   = 88 / 107 = .82
string / CONCAT(0)                       = 88 /  91 = .87

and

string / UDF_CONCAT(0,1,2,3,4,5,6,7,8,9) = 88 / 122 = .72 
string / CONCAT(0,1,2,3,4,5,6,7,8,9)     = 88 / 104 = .85

Here, the difference between the single argument calls is only 5%, wereas the difference becomes more 12% for the calls with 10 arguments. This is interesting because in the comparison with the no-operation benchmark we did not detect a difference caused by the the amount of arguments.

My current suspicion is that we are actually witnessing the effect of the length of the left hand expression in the assignment operation. Our string literal is a VARCHAR(14), and it should probably be a CHAR(1) for the comparison to the single argument calls and a CHAR(10) for the comparison with the 10 argument calls.

Conclusion

The UDFs I tested are

• somewhere around and between 25% to 33% faster than stored SQL functions


• somewhere around and between 5% to 20% slower than built-in functions

Another interesting finding is that argument passing seems to have about the same impact on UDFs as it has on built-in functions. A single argument seems to cost about 1% of the function performance in both cases. For stored SQL functions no such data is currently available.

Discussion

Currently, all functions call are single complete function calls. That means that for UDFs, the initialization function and the row-level function are both called exactly once per UDF instance. When using UDFs in multi-row SQL statements, the initialization function will be called only once per occurence of the UDF, and the row-level function will be called for each row. For functions where the initialization function is relatively expensive, this means that the performance observed in these benchmarks is probably poorer that it can be. A new set of benchmarks should be done to measure the performance of functions in multi-row SQL statements.

Finally

So, are you interested? If you are, go and register for the conference. You can contact me and get a 20% discount! Of course I'd love to see you attend my tutorial too!

See you at the conference!