Single INSERT command of multiple rows vs. multiple INSERT commands of single rows

Question

I would like to know which solution is faster than the other. So, imagine we'd like to INSERT 10000 rows into a table. We have the following 2 solutions:

Solution 1: Run the INSERT query 10000 times:

INSERT INTO myTable (a,b,c) VALUES ("a","b","c"); x 10000

Solution 2: Run one INSERT query with 10000 rows at once:

INSERT INTO myTable (a,b,c) VALUES 
("a","b","c"),
("a","b","c"),
("a","b","c"),
 ...,
("a","b","c");

Please check the following link: stackoverflow.com/questions/1793169/… — Nedim Hadžiosmanović
– Nedim Hadžiosmanović, Commented Feb 25, 2015 at 10:23

Zakaria.dem · Accepted Answer · 2015-02-25 10:18:42Z

1

Solution 2 : it need just one connection to the database, the solution 1 need 1000 connections and it's so mush.

answered Feb 25, 2015 at 10:18

Zakaria.dem

3235 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

disasterkid Over a year ago

What if we run all 10000 INSERTs with one connection? 1. open connection; 2. perform 10000 INSERTs ; 3. close connection

Zakaria.dem Over a year ago

you can do for exemple a loop and insert 1000 by 1000

Gleiemeister 2000 · Accepted Answer · 2015-02-25 10:24:46Z

1

Solution 1 is around 10 times slower than solution 2.

From the guy with the mysql database server with 100gb data and 5000 request per second.

answered Feb 25, 2015 at 10:24

Gleiemeister 2000

7074 gold badges9 silver badges23 bronze badges

Collectives™ on Stack Overflow

Single INSERT command of multiple rows vs. multiple INSERT commands of single rows

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related