MySQL Transactions – Why Is Inserting Data with Transactions Faster?

MySQLstored-procedurestransaction

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)

Best Answer

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

Related Question