MySQL Bugs: #15014: Performance problems with functions

Bug #15014	Performance problems with functions
Submitted:	17 Nov 2005 12:51	Modified:	2 Feb 2006 13:58
Reporter:	Per-Erik Martin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0	OS:	Any (Any)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
When using statements which will repeatedly invoke a stored function, it's several magnitudes slower than doing the same thing as the function directly in-line.
Two known statements when this happens are "do benchmark" and "update".

When doing a similar thing in a looping procedure, the difference between the inline code and using the stored function is much smaller. (The difference is a magnitude of 2-3, due to the function call overhead.)

Examining the SP cache behaviour shows that in the former case the hit and insert counts are 1, indicating that the cache is cleared or recreated between each invocation,
while in the latter case the cache is used, and the hit count is proportional to the number of invokations. (This cannot be seen directly, some coding or debugging is required.)

Using the declarations below:

mysql> update t1 set s = concat('zip','zap');
Query OK, 1000 rows affected (0.01 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> update t1 set s = f('foo','bar');
Query OK, 1000 rows affected (0.78 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> do benchmark(1000, concat('foo','bar'));
Query OK, 0 rows affected (0.00 sec)

mysql> do benchmark(1000, f('foo','bar'));
Query OK, 0 rows affected (0.76 sec)

mysql> call b1(1000);
Query OK, 0 rows affected (0.17 sec)

mysql> call b2(1000);
Query OK, 0 rows affected, 1000 warnings (0.40 sec)

mysql> 

How to repeat:
create table t1 ( s varchar(20));

delimiter //
create procedure p(i int)
begin
  while i > 0 do
    insert into t1 values ('kaka');
    set i = i - 1;
  end while;
end//

create function f(s1 varchar(10), s2 varchar(10))
    returns varchar(20)
  return concat(s1, s2)//

create procedure b1(i int)
begin
  declare s varchar(20);

  while i > 0 do
    set s = concat('foo', 'bar');
    set i = i - 1;
  end while;
end//

create procedure b2(i int)
begin
  declare s varchar(20);

  while i > 0 do
    set s = f('foo', 'bar');
    set i = i - 1;
  end while;
end//
delimiter ;

-- Fill table
call p(1000);
-- Update directly
update t1 set s = concat('zip','zap');
-- Update with function
update t1 set s = f('foo','bar');
-- Benchmark directly
do benchmark(1000, concat('foo','bar'));
-- Benchmark function
do benchmark(1000, f('foo','bar'));
-- Looping procedure directly
call b1(1000);
-- Looping procedure with function
call b2(1000);

The initial diagnosis turned out to be wrong. The cache may or may not be involved, but the performance problem remains, of course.

I missed that there already is a bug report that's partially covers this: BUG#14946.

So this one maybe supersedes that one (adding the UPDATE issue).

This is no longer repeatable (in bk 5.0.19). Some change(s) between the end of December and now fixed the problem. (Hard to tell what, a lot of possibilities, and possibly a combination of things.)

Now we don't see the extremely high overhead for functions with UPDATE and DO BENCHMARK we had before, only the normal function call overhead. (Admittedly a little high too, but accounted for.)

mysql> -- Fill table
mysql> call p(1000);
Query OK, 1 row affected (0.06 sec)

mysql> -- Update directly
mysql> update t1 set s = concat('zip','zap');
Query OK, 1000 rows affected (0.01 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> -- Update with function
mysql> update t1 set s = f('foo','bar');
Query OK, 1000 rows affected (0.04 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> -- Benchmark directly
mysql> do benchmark(1000, concat('foo','bar'));
Query OK, 0 rows affected (0.00 sec)

mysql> -- Benchmark function
mysql> do benchmark(1000, f('foo','bar'));
Query OK, 0 rows affected (0.02 sec)

mysql> -- Looping procedure directly
mysql> call b1(1000);
Query OK, 0 rows affected (0.02 sec)

mysql> -- Looping procedure with function
mysql> call b2(1000);
Query OK, 0 rows affected (0.05 sec)

Since it's not practical to have performance tests in the test suite, no test case was added.
The current behaviour was tested in bk 5.0.19 though (optimized build).

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.19 changelog. Closed.