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

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


Updated May 29, 2019 05:06 AM

Updated November 20, 2018 18:06 PM

Updated June 19, 2018 09:06 AM

Updated July 19, 2018 06:06 AM