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

Performance problems with a simple loop

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

          relates to

          Task - A task that needs to be done. MDEV-10280 store procedures - JIT

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            f_razzoli Federico Razzoli created issue -
            f_razzoli Federico Razzoli made changes -
            Field Original Value New Value
            Description First, try benchmarking an increment + assignment:

            {code:sql}
            SET @x := 0;
            DO BENCHMARK(1000000, @x := @x + 1);
            {code}

            On my local system, this takes ~ .11 seconds.

            But try to do the same in a stored procedure:

            {code:sql}
            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();
            {code}

            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:

            {code:sql}
            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();
            {code}

            Takes arount 5.75.             		First, try benchmarking an increment + assignment:

            {code:sql}
            SET @x := 0;
            DO BENCHMARK(1000000, @x := @x + 1);
            {code}

            On my local system, this takes ~ .11 seconds.

            But try to do the same in a stored procedure:

            {code:sql}
            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();
            {code}

            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:

            {code:sql}
            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();
            {code}

            Takes about 5.75.
            elenst Elena Stepanova made changes -
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0.19 [ 19200 ]
            Labels performance stored_procedures performance stored_procedures upstream
            elenst Elena Stepanova made changes -
            Assignee Sergei Golubchik [ serg ]
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            danblack Daniel Black made changes -
            serg Sergei Golubchik made changes -
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 69809 ] MariaDB v4 [ 131789 ]

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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