[MDEV-8254] Performance problems with a simple loop - Jira

XML

Word

Printable

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.

relates to

MDEV-10280 store procedures - JIT

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.