What is the most efficient way to insert many rows into a table?
You put all the rows into one statement, and you break a very large load into batches.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
sku VARCHAR(40) NOT NULL UNIQUE,
name VARCHAR(120) NOT NULL
);
-- Many rows in one statement. This is the fast way.
INSERT INTO products (id, sku, name) VALUES
(1, 'NB-001', 'Notebook'),
(2, 'PEN-002', 'Pen'),
(3, 'LAMP-003', 'Desk lamp');
So why is that so much faster than one statement per row? Three reasons.
The first one is the round trip. One statement for a thousand rows is one trip to the database, and a thousand statements for a thousand rows is a thousand trips, and on a network connection each one of those has a delay that dwarfs the actual work.
The second one is the parsing. Every statement has to be read and worked out, and the engine knows the shape of the table after the first one, so the later ones are cheaper. A thousand statements pay that cost a thousand times.
The third one is the commit. A statement that runs on its own is usually a transaction of its own, and every commit means the database writes its record of the change to the disk so that it survives a crash. That is the biggest cost of all, and one statement for the whole batch pays it once.
So when the load is very large, you break it into batches of a few hundred or a few thousand rows, and you put the whole load inside one transaction.
BEGIN;
-- the first batch
INSERT INTO products (id, sku, name) VALUES (1, 'NB-001', 'Notebook'), (2, 'PEN-002', 'Pen');
-- the second batch
INSERT INTO products (id, sku, name) VALUES (3, 'LAMP-003', 'Desk lamp');
COMMIT;
The next question in this topic looks at the slow way in more detail.