MySQL Bugs: #15014: Performance problems with functions

Bug #15014	Performance problems with functions
Submitted:	17 Nov 2005 13:51	Modified:	2 Feb 2006 14:58
Reporter:	Per-Erik Martin
Status:	Closed
Category:	Server: SP	Severity:	S2 (Serious)
Version:	5.0	OS:	Any (Any)
Assigned to:	Bugs System	Target Version:

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.