How to insert many records to MySQL faster,Java?

Question

I am making a web service in which number of records are coming and they have to be inserted in database faster.

I use PreparedStatement. Are there any ways to insert records faster?

The data is coming dynamically so, how much data is coming to insert could not be hard-coded. I have a different class which is common for database queries and I am calling it through my 'Servlet'.

Here is the database query code:

public void insertUser_Details(String name,String address) throws Exception {
    ps=con.prepareStatement("insert into registration_table(name,address) values(?,?)");
    ps.setString(1,name);  
    ps.setString(2,address);        
    ps.executeUpdate();
}

It's *very* hard to help you without knowing more information - like the schema, what your performance target is, what your current code looks like, how it currently performs, how you're testing it etc... — Jon Skeet, Dec 01 '14 at 06:49
Well that's shown part of your code - although it suggests you're not closing your statement, which is worrying. You haven't given any of the rest of the information, however - and we don't know whether you have lots of rows to insert in one go, in which case batching might help, too. — Jon Skeet, Dec 01 '14 at 07:01
I have think about it @JonSkeet. But here my exact number of entries is varying. So how can i handle it? — Ronak Joshi, Dec 01 '14 at 07:08
While the exact number of entries may vary, you clearly have more information than we do - so put some of it in the question. Currently there just isn't enough information for us to to help you. — Jon Skeet, Dec 01 '14 at 07:11
@RonakJoshi , I guess batch operation is what you need, check my answer to see if it helps you and let me know. — JaskeyLam, Dec 01 '14 at 08:25

JaskeyLam · Accepted Answer · 2014-12-02T12:40:51.547

4

Suppose you have your name and address in an Employee, and now you need to insert many Employee records into db, you can use JDBC batch operation and optimize the performance.

  PreparedStatement ps = con.prepareStatement("INSERT INTO registration_table(name,address) VALUES (?, ?)");   

  for(Employee employee: employees){
      ps.setString(1,employee.getName());
      ps.setString(2,employee.getAddress());
      ps.addBatch();// add to batch
      ps.clearParameters();
  }

  int[] results = ps.executeBatch();// execute with batch rather than execute many SQL separately.

You can also easily change the code using array or collection to hold your data if you do not have a entity object(Employee) to hold them.

You may read batch insert in java for more information

edited Dec 02 '14 at 12:40

answered Dec 01 '14 at 08:13

JaskeyLam

15,405
21
114
149

What is employees here? I have to pass name and address from my servlet to this class where database query is applied. @Jaskey – Ronak Joshi Dec 02 '14 at 10:30
@RonakJoshi, it is an example, you can use a array to store your name and address and use for loop for name[i],address[i] to set the param and add them to the batch. – JaskeyLam Dec 02 '14 at 12:41

score 0 · Answer 2 · answered Dec 01 '14 at 06:53

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
VALUES ('1', 'userid_1', 'content_1', 1);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

score 0 · Answer 3 · answered Dec 01 '14 at 06:55

I'll assume you are inserting a single row into a single table.

If your inserts are unlikely to fail, and you do not need to read back information (e.g. you do not need the auto-inserted key) you can use the INSERT DELAYED which will queue the insert and return immediately.

However, even an immediate insert should be VERY FAST. You might want to look at the system constraints and determine if you need to add memory, CPU or IO capacity to your database server.

score 0 · Answer 4 · answered Dec 01 '14 at 07:34

Two common optimizations come to mind, which can be used individually or together:

1) If you your goal is to be able to increase the throughput of inserted records, then you can consider tuning the size of your MySQL datasource connection pool. How it is done depends on which web/application server and the connection pool implementation you use.

2) If the goal is to keep the server responsive, then you may consider performing asynchronous inserts, basically trading additional memory usage for improved response time of the web tier. If you use a J2EE 7 compliant application server, then this should be relatively easy using the Java EE concurrency API, otherwise you should be able to use the "traditional" java.util.concurrent APIs, even though the J2EE spec doesn't encourage it.

How to insert many records to MySQL faster,Java?

4 Answers4

Linked