Details
-
Type:
Bug
-
Status: Confirmed
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.1, 10.0, 5.5
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
First, try benchmarking an increment + assignment:
SET @x := 0; DO BENCHMARK(1000000, @x := @x + 1);
On my local system, this takes ~ .11 seconds.
But try to do the same in a stored procedure:
DELIMITER ||
DROP PROCEDURE IF EXISTS test ||
CREATE PROCEDURE test()
BEGIN
SET @x := 0;
WHILE @x < 1000000 DO
SET @x := @x + 1;
END WHILE;
END ||
DELIMITER ;
CALL test();
More than 13.6 seconds on my system!
For some reason, the problem is notably less critical (but still annoying) if you use a local variable instead of a session variable:
DELIMITER ||
DROP PROCEDURE IF EXISTS test ||
CREATE PROCEDURE test()
BEGIN
DECLARE x INT UNSIGNED DEFAULT 0;
WHILE x < 1000000 DO
SET x := x + 1;
END WHILE;
END ||
DELIMITER ;
CALL test();
Takes about 5.75.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Same with MySQL 5.5, 5.7.
Also, same is true for MariaDB 10.1 compound statements, so it's not related to the SP call as such.
Here is some noticeable difference from P_S (although maybe it's not the difference that causes the performance problem, but it might be):
(these values appear in the table right after TRUNCATE, they are barely modified by the BENCHMARK)
I'll leave it to Sergei Golubchik to decide whether there is a bug in here.