Stored Function Overhead in MySQL 5.0 not that bad after all?

In my blog post on how to implement ADD_TO_SET() and REMOVE_FROM_SET() I came to the conclusion that the use of stored functions generates tremendous overhead. In a reaction MySQL developer Per-Erik Martin pointed out to me that this might not be true but just a consequence of a very strange behaviour of MySQL's built-in BENCHMARK() function.

Per-Erik Martin didn't trust the results from my BENCHMARK() tests at first as my overhead factor of about 10 to 50 (which he could in fact reproduce with similar results) for simple stored functions "seemed a bit absurd" to him. So he implemented his own benchmark tests as stored procedures, ended up with an overhead factor of only about 2 to 3 and came to the conclusion that "there's something fishy with the DO BENCHMARK thingy..."

There could be something wrong in how BENCHMARK() handles calls to stored functions, which actually renders it useless for its purpose to do performance checks and lets MySQL look much worse than it actually is when it comes to stored functions.

Of course I needed to reproduce this myself, using the ADD_TO_SET() function from my article Add the missing ADD_TO_SET() AND REMOVE_FROM_SET() for MySQL's SET type.

Let's first benchmark the stored function versus its inline code again using BENCHMARK(). I used global variables as input to all of the functions to rule out possible optimization on the inline versions if fed with static strings (the variable @member was set to 'd' and @set was set to 'a,b,c' for all of the following tests).

mysql> DO BENCHMARK(100000, ADD_TO_SET(@member, @set));
Query OK, 0 rows affected (0.75 sec)

mysql> DO BENCHMARK(100000, CONCAT_WS(',', IF(@set = '', NULL, @set), @member));
Query OK, 0 rows affected (0.04 sec)

This shows an overhead factor of nearly 20 for the call to the stored function.

We'll now build our own benchmark procedures to compare times of inline versus stored function code (and an empty procedure to determine the overhead of the benchmark procedure itself):

CREATE PROCEDURE benchmark_stored_function(i INT)
    DETERMINISTIC NO SQL
BEGIN
    DECLARE s VARCHAR(4096);
    WHILE i > 0 DO
        SET s = ADD_TO_SET(@member, @set);
        SET i = i - 1;
    END WHILE;
END //

CREATE PROCEDURE benchmark_inline_code(i INT)
    DETERMINISTIC NO SQL
BEGIN
    DECLARE s VARCHAR(4096);
    WHILE i > 0 DO
        SET s = CONCAT_WS(',', IF(@set = '', NULL, @set), @member);
        SET i = i - 1;
    END WHILE;
END //

CREATE PROCEDURE benchmark_empty(i INT)
    DETERMINISTIC NO SQL
BEGIN
    DECLARE s VARCHAR(4096);
    WHILE i > 0 DO
        SET s = @set;
        SET i = i - 1;
    END WHILE;
END //

And do the tests again using these procedures:

mysql> CALL benchmark_stored_function(100000);
Query OK, 0 rows affected (2.27 sec)

mysql> CALL benchmark_inline_code(100000);
Query OK, 0 rows affected (1.25 sec)

mysql> CALL benchmark_empty(100000);
Query OK, 0 rows affected (0.75 sec)

Subtracting the overhead for the procedure itself (benchmark_empty()) we get:

stored function:
2.27 - 0.75 = 1.52
inline code:
1.25 - 0.75 = 0.5

And thus we find an overhead factor of about 3 instead of the 20 measured with BENCHMARK().

This is definitely a BENCHMARK() issue, you might think? Possibly, but possibly not. Just after filing a bug report on this topic, I've seen an additional post to the forums from Felix Geerinckx. He experienced a similar difference in execution times of stored functions versus inline code between SET and UPDATE statements. This could be related and probably mean that this is not a BENCHMARK()-only problem but something more fundamental.

Then of course my original suggestion to improve the query optimizer with an automatic inlining feature for simple functions would still be valid. One-line functions using NO SQL could be treated as macros instead of real functions and their whole code body could be inserted into the query calling them, using similar functionality as with the MERGE algorithm for VIEWs. At least for these simple cases this would for most of the part only involve some string operations, as I understand it. For the general case I think Per-Erik Martin is right when he says that "it's not an easy thing to do".