What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction?

by Kevin Cianfarini   Last Updated July 12, 2019 00:06 AM - source

I'm trying to understand the performance differences between something like

INSERT INTO Person (id, name) VALUES
(1, "Kevin"),
(2, "John"),
(3, "Jane"),
... 

and

BEGIN TRANSACTION;

INSERT INTO Person (id, name) VALUES (1, "Kevin");
INSERT INTO Person (id, name) VALUES (2, "John");
INSERT INTO Person (id, name) VALUES (3, "Jane");
...

END TRANSACTION;

I'm aware that during a transaction indexes are temporarily built, but I'm not quite sure otherwise. I also don't know the other performance differences between the two.



Answers 1


For three rows, there is no difference.

Beyond that, the VALUES construct has a 1000 row limit, which isn't likely to expose a performance issue except in edge cases with extreme local factors.

Erik Darling
Erik Darling
July 11, 2019 23:38 PM

Related Questions



Database transaction consistency on PostgreSQL

Updated November 20, 2018 18:06 PM


buld insert csv file to sqlServer error

Updated July 19, 2018 06:06 AM