CODEWITHSUNDEEP
mysqldatabasestored-procedures
guest86
guest86

Reputation: 2956

Mysql inserting multiple rows with a single insert

I have a client Java 7 app that has to insert large number of small data (few columns) into the database. Database (Mysql 5.6) is located on my server. Client side Java app calls stored procedure in a loop for inserting single row. Row is simple, looks like this:

int | int | varchar | varchar | int | datetime

and the current way i'm doing that is like this (pseudo code):

try(DbConnection conn = new DbConnection()) {
for(RowModel model : rowModel) {
//inserting row here
}
}
catch(SqlException e) {
//process exception...
}

As you can see, i'm opening connection once and i insert row by row. "rowModel" may have thousands of rows and to me it looks like it would be more reasonable to send 1 "LongText" to mysql stored procedure which will somehow delimit that longtext to be suitable for one single "insert" call.

Is it possible and how? What do you propose?

So, basically, to sum things: 1) is there any benefit in calling one "insert" with a lot of values instead of having "for-each" on client's side? 2) How could i write my stored procedure so it takes 1 big text and inserts it using single "INSERT" command?

Upvotes: 0

Views: 96

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

There are relevant performance benefit calling on insert with a lot of values .. you execute just a command one time onle instead of many commands like how many rows you have to insert ..

Typically the batch insert, so is also called this tecnique, reduce the duration of the procedure of many time ..

eg: in some empiric test inserting 1000 in insert loop the execution time was of 1 minutes .. with batch insert the time result is 2 secs

Upvotes: 1

Related Questions