Batch Insert: Using Single vs. Multiple Statements

Question

In a single transaction, is there any significant performance difference between doing batch insert with a single statement like:

-- BEGIN TRANSACTION

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- COMMIT

or multiple statements like below:

-- BEGIN TRANSACTION

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy');

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- COMMIT

Answer 1

Any difference between these two approaches is effectively eliminated if you do the multiple INSERT operations (your second approach) within a single BEGIN / COMMIT transaction.

Why? The lion's share of the cpu and I/O work for data manipulation happens upon commit. If you're in autocommit mode, each INSERT gets an implicit commit, and the overhead associated with it, unless you're in a transaction. But multiple data manipulation statements in a single transaction incur the overhead just once.

If you didn't set the the mode, you're probably in autocommit mode (unless you're using the python-language connector).