Fastest way for doing INSERTS using IBATIS

I need to insert 20,000 rows in a single table (SQL Server 2005) using iBatis. What's the fastest way to do it ? I'm already using batch mode, but it didn't help much:

try {
  // ... execute statements in between
} finally {

Asked by: Kelvin515 | Posted: 28-01-2022

Answer 1

Barring the bulk loaders others are referring to, let's consider how to best do it through SQL. (And the bulk loaders don't work well if you're sending intermixed data to different tables.)

First, you shouldn't be using whatever abstraction layer you're using, in this case iBatis, as it effectively will offer you little value, but that abstraction layer will have some (not necessarily much, but some) CPU cost. You should really simply use a raw database connection.

Next, you'll be sending in a mess of INSERT statements. The question is whether you should use a simple string for the statment, (i.e. INSERT INTO TABLE1 VALUES('x','y', 12)) vs a prepared statement (INSERT INTO TABLE1 VALUES(?, ?, ?)).

That will depend on your database and DB drivers.

The issue with using a simple string, is basically the conversion cost from an internal format (assuming you're inserting Java data) to the string. Converting a number or date to a String is actually a reasonably expensive CPU operation. Some databases and drivers will work with the binary data directly, rather than simply the string data. So, in that case a PreparedStatement could net some CPU savings in potentially not having to convert the data.

The downside is that this factor will vary by DB vendor, and potentially even the JDBC vendor. For example, Postgres (I believe) only works with SQL strings, rather than binary, so using a PreparedStatement is a waste over simply building the string yourself.

Next, once you have your statement type, you want to use the addBatch() method of the JDBC Statement class. What addBatch does is it groups up the SQL statements in to, well, a batch. The benefit is that instead of sending several requests to the DB, you send a single LARGE request. This cuts down on network traffic, and will give some noticeable gains in throughput.

The detail is that not all drivers/databases support addBatch (at least not well), but also the size of your batch is limited. You most likely can't addBatch for all 20,000 rows and expect it to work, though that would be the best bet. This limit, also, can vary by database.

For Oracle, in the past, I used a buffer of 64K. Basically I wrote a wrapper function that would take a literal INSERT statement, and accumulate them in 64K batches.

So, if you wanted to bulk insert data through SQL via JDBC, those are the ways to do it. The big improvement is the Batch mode, the Statement vs PreparedStatement is more to potentially conserve some CPU, and maybe network traffic if your driver supports a binary protocol.

Test, rinse, and repeat until you're happy enough.

Answered by: Joyce965 | Posted: 01-03-2022

Answer 2

Although this is not specific to your db server, I have previously had success writing the rows out to a local file in csv format and then having the database importing the file. This was considerably faster than insert statements or even a batch insert.

Answered by: Patrick152 | Posted: 01-03-2022

Answer 3

In SQL Server, the fasted way to insert records in batch is using BULK INSERT. However, this method loads the records from a text file rather than directly from your application.

It also doesn't take into the account the time spent creating the file. You may have to weigh if that offsets any speed gains from the actual insert. Keep in mind that even if this is a little slower overall, you'll end up tying up your database server for less time.

The only other thing you might try is inserting (staging) the batch into a completely different table (with no indexes or anything). Then move the record from that staging table to your target table and drop the staging table. This would move the data to server first, so that the final insert could all happen with sql server itself. But again: it's a two step process, so you'll have to count the time for both steps.

Answered by: Stuart907 | Posted: 01-03-2022

Answer 4

Bulk inserts are best done using the database's own bulk loader tools. For Oracle, it's SQL*Loader, for example. Often these are faster than anything you could ever write.

Answered by: John817 | Posted: 01-03-2022

Similar questions

Fastest way to share data objects between a Java server and a C# client

What is the fastest way to share data structures between Java and C#? I want something where I can literally send a "car" object or a "foo" object and have it serialized and deserialized on both server and client.

java - What is the fastest way to read a large number of small files into memory?

I need to read ~50 files on every server start and place each text file's representation into memory. Each text file will have its own string (which is the best type to use for the string holder?). What is the fastest way to read the files into memory, and what is the best data structure/type to hold the text in so that I can manipulate it in memory (search and replace mainly)? Thanks

java - Fastest way to iterate through large table using JDBC

I'm trying to create a java program to cleanup and merge rows in my table. The table is large, about 500k rows and my current solution is running very slowly. The first thing I want to do is simply get an in-memory array of objects representing all the rows of my table. Here is what I'm doing: pick an increment of say 1000 rows at a time use JDBC to fetch a resultset on the following SQL query SELECT...

linux - Fastest service wrapper for Java apps

I'm looking for most performing Java service wrapper, which could make Java application running as a service on Linux. Can anyone recommend such utility. Thanks.

What is the fastest way to find an array within another array in Java?

Is there any equivalent of String.indexOf() for arrays? If not, is there any faster way to find an array within another other than a linear search?

Java, IO - fastest way to remove file

My problem is that I have an app which is writing a lot of relatively (100-500kb) small CSV files (tens and hundreds of thousands ). Content of those files then get loaded in database via sql loader call (its oracle db) and this is what I have to live with. So, I need to remove those small files time to time to prevent them from eating up all space. I would like to attach that to the activity which writes those fi...

swing - Fastest Way to Draw a Static Image in Java

I'm in the process of writing a custom heatmap generator. I'm wondering what the fastest way is to draw boxes (up to around 1 million) in Java. Most questions I've found have concentrated on dynamic images (like in games), and I'm wondering if there's a better way to go for static images. I've tried using swing (via a GridLayout and adding a colored canvas to each box), drawing directly on the panel with Graphics2D, and al...

java - Fastest way to read long[] from file?

I have a file that contains about 200,000 long values that I want to read as fast as possible into a long[]. It's for an Android app; function calls are slow (so anything involving reading a long at a time with a "for" loop will be super slow) and I need loading to be fast. What can I use? Everything I look at seems to read only bytes fast. I've used ByteBuffer and FileChannel from the NIO...

c# - The fastest way to save large data to a file

I do some numerical calculation in Java, C# and C++. Some of them save a lot of data (to the text file). What is the fastest way to do it? C++. ofstream file;; for(int i=0;i<251;i++){ for(int j=0;j<81;j++) file<<(i-100)*0.01<<" "<<(j-40)*0.01<<" "<<U[i][j]<<endl; file<<endl; } Which I ...

Fastest Java-based Javascript Engine

I'm currently using the javax.script package for interpreting and executing Javascript code on the fly. Incidentally, this is the same engine used by Mozilla's Rhino JS interpreter. Question: is there any Java-based Javascript Engine that is faster than Rhino?

Still can't find your answer? Check out these amazing Java communities for help...

Java Reddit Community | Java Help Reddit Community | Java Community | Java Discord | Java Programmers (Facebook) | Java developers (Facebook)