| Bug #54485 | function execution time increases after each execution | ||
|---|---|---|---|
| Submitted: | 14 Jun 2010 14:11 | Modified: | 24 Dec 2012 9:02 |
| Reporter: | Günter Nowak | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.5.8, 5.0.19 | OS: | Any (verified in Linux, Solaris) |
| Assigned to: | CPU Architecture: | Any | |
[14 Jun 2010 14:17]
Valeriy Kravchuk
Please, check with a newer version, 5.0.91. Nobody will fix bugs in 4 years old code, and there is good chance that problem (if any) is already fixed:
valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop function if exists testfunc_a;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> delimiter //
mysql> CREATE FUNCTION `testfunc_a`(
-> param1 NVARCHAR(20)
-> ) RETURNS varchar(20)
-> DETERMINISTIC
-> BEGIN
-> if param1 = 'AT' then
-> return '0024000203';
-> else
-> return '0024000204';
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql>
mysql> select benchmark(10000,testfunc_a('hallo'));
+--------------------------------------+
| benchmark(10000,testfunc_a('hallo')) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (2.01 sec)
mysql> select benchmark(10000,testfunc_a('hallo'));
+--------------------------------------+
| benchmark(10000,testfunc_a('hallo')) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (1.88 sec)
mysql> select benchmark(10000,testfunc_a('hallo'));
+--------------------------------------+
| benchmark(10000,testfunc_a('hallo')) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (2.01 sec)
[14 Jun 2010 15:15]
Günter Nowak
it seems that the bug is repaired in higher version, e.g. 5.0.41
[14 Jun 2010 15:27]
Valeriy Kravchuk
5.0.41 is also old and not fixable. I've demonstrated that current code, 5.0.92, does not allow to repeat this bug. The only way to force some fix or check what exactly fixed the problem is to prove that it is still repeatable with recent 5.0.x (or, even better, 5.1.x, as 5.0 is on extended support now) version released officially. So, I need to know if the problem is repeatable for you with 5.0.91 and/or 5.1.47.
[14 Jun 2010 15:35]
Günter Nowak
if the bug is repaired in 5.0.92 and cannot repaired in 5.0.19 because this is an old version this is ok for me and you can close this thanks guenter
[14 Jun 2010 15:48]
Valeriy Kravchuk
Not repeatable with current version.
[9 Mar 2011 9:21]
Valeriy Kravchuk
Actually this is repeatable with 5.5 in the following way:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 --default
-character-set=auto test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop function if exists testfunc_a;
Query OK, 0 rows affected (0.16 sec)
mysql> delimiter //
mysql> CREATE FUNCTION `testfunc_a`(
-> param1 NVARCHAR(20)
-> ) RETURNS varchar(20)
-> DETERMINISTIC
-> BEGIN
-> if param1 = 'AT' then
-> return '0024000203';
-> else
-> return '0024000204';
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> select benchmark(10000,testfunc_a('hallo'));
+--------------------------------------+
| benchmark(10000,testfunc_a('hallo')) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.58 sec)
mysql> select benchmark(10000,testfunc_a('hallo'));
+--------------------------------------+
| benchmark(10000,testfunc_a('hallo')) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (3.84 sec)
mysql> select benchmark(10000,testfunc_a('hallo'));
+--------------------------------------+
| benchmark(10000,testfunc_a('hallo')) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (25.42 sec)
[24 Dec 2012 9:02]
Erlend Dahl
Fixed as a duplicate of bug#60025: SUBSTRING INSIDE A STORED FUNCTION WORKS TOO SLOW
Description: the function execution time of functions increases after each execution of the function in a session. if one reconnects the same behaviour starts again i reproduced this behaviour on version | 5.0.19-standard-log Red Hat Enterprise Linux AS release 3 (Taroon Update 7) and on version | 5.0.19-max-log SunOS 5.10 Generic_118822-25 sun4u sparc SUNW,Sun-Fire-V490 mysql> select benchmark(10000,testfunc_a('hallo')); +--------------------------------------+ | benchmark(10000,testfunc_a('hallo')) | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (0.91 sec) mysql> select benchmark(10000,testfunc_a('hallo')); +--------------------------------------+ | benchmark(10000,testfunc_a('hallo')) | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (3.28 sec) mysql> select benchmark(10000,testfunc_a('hallo')); +--------------------------------------+ | benchmark(10000,testfunc_a('hallo')) | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (5.58 sec) How to repeat: using the mysql-client create fhe following function and execute it. drop function if exists testfunc_a; delimiter // CREATE FUNCTION `testfunc_a`( param1 NVARCHAR(20) ) RETURNS varchar(20) DETERMINISTIC BEGIN if param1 = 'AT' then return '0024000203'; else return '0024000204'; end if; END; // delimiter ; select benchmark(10000,testfunc_a('hallo')); select benchmark(10000,testfunc_a('hallo')); select benchmark(10000,testfunc_a('hallo'));