Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8254

Performance problems with a simple loop

    XMLWordPrintable

    Details

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              f_razzoli Federico Razzoli
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: