4

I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200")

The stored procedure includes REPEAT loop, in which character string is constructed and inserted into table.

When all the looping is inside transaction, the procedure runs very fast, but when I insert data without transaction, running the procedure lasts 400 times longer.

Can anyone explain, why is that so?

Alternatively, if anyone has better idea how to generate sequences like this, i will be happy to hear them.

Here is the code:

-- create table
CREATE TABLE seq
(
    Code CHAR(12)
) ENGINE = INNODB;

-- fast procedure with transactions
DELIMITER $$
CREATE PROCEDURE sp_sequence(IN val_prefix CHAR(2), IN val_from INT, IN val_to INT )
BEGIN
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        INSERT INTO seq (Code) VALUES (CONCAT(val_prefix, @val));
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    COMMIT;
END$$
DELIMITER ;

-- slow procedure without transactions
DROP PROCEDURE IF EXISTS sp_sequence_slow;
DELIMITER $$
CREATE PROCEDURE sp_sequence_slow(IN val_prefix CHAR(2), IN val_from INT, IN val_to INT )
BEGIN
    SET @val = val_from;
    REPEAT
        INSERT INTO seq (Code) VALUES (CONCAT(val_prefix, @val));
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
END$$
DELIMITER ;

And here are results:

mysql> CALL sp_sequence('TX', 11000, 12000);
Query OK, 0 rows affected (0.09 sec)

mysql> CALL sp_sequence_slow('TX', 11000, 12000);
Query OK, 0 rows affected (40.07 sec)
6

Without explicit transaction, all statements are in auto-commit mode - so each insert is separate transaction. And that comes with some overhead for syncing etc.

If you insert all rows without commiting in between, mysql can do lot of it in memory/cache and sync it to disk only once (simplified, caches will probably be flushed multiple times by filesystem and OS).

In case of no explicit transaction each insert has to wait for disk to confirm writing all changes before returning and letting the loop continue. This can be sometimes relaxed a bit by flushing periodically instead after each transaction, but you might lose some data on failure IIRC.

On the other side - if you insert/update too many rows in one transaction, log with information for possible rollback can grow a lot and slow everything down. So transaction size should be reasonable (not millions of rows).

Some tips: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

  • Thanks, the number of rows inserted in one transaction should not exceed 10 000, so this should be OK – Zbynek May 27 '15 at 11:56
  • I recommend 100-1000. Further, I recommend putting all the rows in a single INSERT statement to avoid the overhead of receiving, parsing, etc the statement. Beyond 1000 and you are into "diminishing returns", plus as the answer says, other nasties happen in the logs. – Rick James Jun 7 '15 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.