How to speed up JDBC inserts?

How to speed up JDBC inserts

Updated: July 2021

When trying to assess how knowledgeable a developer is in general and in JDBC in particular, here’s a question I like to ask: how would you speed up inserts when using JDBC?

Here are some options to consider if you ever need to insert data quickly into an SQL database.

1. Don’t use JDBC

The fastest of all. Most databases will have a tool to handle bulk inserts natively. The option isn’t always there, but if the data format is supported by the tool, you won’t find a faster option.

2. Use Prepared Statements

Prepared statements reduce insert time by compiling the SQL once, and also help prevent SQL injection attacks. Injection attacks are another good question to ask in interviews.

Once the prepared statement is parsed by the engine, the client only needs to send the data for each statement, instead of the entire SQL every time.

3. Use Batch Inserts

Prepared statements can help you when using JDBC, but batch statements are going to be your BFF when doing inserts and updates.

We have a demo in Data Pipeline that loads some hospital data into a MySQL database.  Using batch statements of 100 records,  insert time went from 3.5 minutes to under 1 second.

Here are two things you should know about the data: 1) The data isn’t perfect. It does have bad records and duplicate IDs that cause some records to fail.  2) Some work needs to be done to map the target and source formats.

Here’s the code we used.

If you’d like to learn more about Data Pipeline and the above code, please have a look at our Java ETL framework.

4. Use Multiple Connections

Depending on your configuration and database, speed could be improved by setting up more than one connection to do inserts.

In a test we run recently, we saw improvements with up to 5 connections with one of the major database providers.  Going above 5 connections saw no noticeable benefit.

5. Temporarily Disable Integrity Constraints

With this one, caution is required. I don’t recommend it unless you are completely familiar with what you are doing, and most importantly, have a rollback plan.

Disabling integrity checks means your database engine can insert data faster since it has less work to do for each record.  However, if your data isn’t perfectly clean, you may end up with corrupted tables and broken client apps.  Also, don’t forget to re-enable constraints. You don’t want to be the guy who forgot to re-enable constraints on a production database.

What’s your trick for speeding up inserts with JDBC?

Drop me a comment if you have an insert trick to share.

Happy coding.

 

About Dele Taylor

We make Data Pipeline — a lightweight ETL framework for Java. Use it to filter, transform, and aggregate data on-the-fly in your web, mobile, and desktop apps. Learn more about it at northconcepts.com.

5 thoughts on “How to speed up JDBC inserts?

  • Pingback: Java Weekly 22/16: JDBC insert, L2 caching, eager optimization

  • By Sanjeev Dhiman - Reply

    Pretty good, short, nice and concise.

    • By Dele Taylor - Reply

      Thanks, I appreciate your feedback. Cheers.

  • By Ramesh - Reply

    Hi Dele,

    Thanks for the good and very useful article .

    i have some small doubt on the below

    4. Use Multiple Connections

    can you please help me to understand , how to do this .

    • By Dele Taylor - Reply

      Hi Ramesh,

      The simplest way you can do this is to run your code that connects to the DB and inserts data in separate threads.

      Our setup is a little less simple. Each thread has its own LinkedBlockingDeque it pulls data from to write to its own connection. We have a main thread to read data from a source (like a CSV file) and put one record in each thread’s LinkedBlockingDeque in turn.

      Of course, we encapsulate all of this inside JdbcMultiWriter. You can see an example here: Write to a Database Using Multiple Connections.

      Hope this helps. Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *
You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">