Optimizing batch inserts, SQLite

Question

I am playing with different buffer sizes to be inserted into the local SQLite DB and have found that it takes nearly 8 minutes to inserts 10,000,000 rows of data, when buffer size is 10,000. In other words, it takes 1,000 writes to store everything.

8 minutes to store 10,000,000 seems a bit too long (or is it?)

Can any of the below be optimized to increase the speed? Please note that data being inserted is a random collection of characters.

public int flush() throws SQLException {
    String sql = "insert into datastore values(?,?,?,?);";

    PreparedStatement prep = con.prepareStatement(sql);

    for (DatastoreElement e : content) { // content is 10,000 elements long
        _KVPair kvp = e.getKvp();

        prep.setInt(1, e.getMetaHash());
        prep.setInt(2, kvp.hashCode());
        prep.setString(3, kvp.getKey());
        prep.setString(4, kvp.getValue());

        prep.addBatch();
    }

    int[] updateCounts = prep.executeBatch();

    con.commit();

    return errorsWhileInserting(updateCounts);
}

When table is created it is done via

    statement.executeUpdate("create table datastore 
               (meta_hash INTEGER," + 
               "kv_hash   INTEGER," + 
               "key TEXT," +
               "value TEXT);");

Can any of the above be further optimized please?

I would try smaller batch sizes e.g. 2500 or 1000, to see if it makes any difference, it could be slower but it might be faster. — Peter Lawrey
– Peter Lawrey, Commented Aug 23, 2012 at 15:36
I agree with Peter. I have found that when using SqlServer, using anymore than 1000 for a bacth size simply slowed down the process. I was able to get my program to insert a couple million records in under a minute when using 1000 but 10000 made it takes a few minutes. — Jesse Webb
– Jesse Webb, Commented Aug 23, 2012 at 15:42
Is there a way to find a magic number of a batch size? In my tests, 10,000 performs better than 1000 (slightly) — James Raitsev
– James Raitsev, Commented Aug 23, 2012 at 15:52
No magic, all performance optimisation is about testing your hunches. BTW you are getting 20,833 rows per second. I get around the same when bulk loading data into MS SQL Server via bcp.exe, which is that fastest way to load data. So your numbers are not too bad. But in my case I'm inserting into a view which runs a trigger which manipulates the data before final insert to tables, so ... could be faster. — David Roussel
– David Roussel, Commented Aug 23, 2012 at 16:10

Joe · Accepted Answer · 2012-08-23 16:09:40Z

16

I'm a bit hazy on the Java API but I think you should start a transaction first, otherwise calling commit() is pointless. Do it with conn.setAutoCommit(false). Otherwise SQLite will be journaling for each individual insert / update. Which requires syncing the file, which will contribute towards slowness.

EDIT: The questioner updated to say that this is already set true. In that case:

That is a lot of data. That length of time doesn't sound out of this world. The best you can do is to do tests with different buffer sizes. There is a balance between buffer jitter from them being too small and virtual memory kicking in for large sizes. For this reason, you shouldn't try to put it all into one buffer at once. Split up the inserts into your own batches.

edited Aug 23, 2012 at 16:09

answered Aug 23, 2012 at 15:36

Joe

48k37 gold badges166 silver badges261 bronze badges

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

4 Comments

James Raitsev Over a year ago

Where should begin() be called? On what object please?

Joe Over a year ago

On the connection. It looks like the method is called con.setAutoCommit(false); in JDBC. Call it before you start inserting items.

James Raitsev Over a year ago

Right. I forgot to mention this .. it is of course set. Without it takes forever

James Raitsev Over a year ago

So other then that, does 8 minutes look reasonable to you? We're talking about 6G worth of data here.

keiki · Accepted Answer · 2012-08-23 17:08:36Z

3

You are only executing executeBatchonce, which means that all 10 million statements are send to the database in the executeBatch call. This is way too much to handle for a database. You should additionally execute int[] updateCounts = prep.executeBatch(); in your loop let's say all 1000 rows. Just make an if statement which tests on counter % 1000 == 0. Then the database can asynchronously already work on the data you sent.

edited Aug 23, 2012 at 17:08

answered Aug 23, 2012 at 16:05

keiki

3,4893 gold badges32 silver badges41 bronze badges

1 Comment

FarshiD Naqizadeh Over a year ago

I use 2yers ago this concept in my IOT project that have milion of rows and see this today , I upvote this for help people understanding the right way!

Collectives™ on Stack Overflow

Optimizing batch inserts, SQLite

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related