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"),



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


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