Geek asks and answers

Optimization of batch inserts, SQLite

I play with different buffer sizes that need to be inserted into the local SQLite database, and found that it takes about 8 minutes to enter 10,000,000 rows of data, when the buffer size is 10,000. In other words, it takes 1000 records to save everything.

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

Can any of the following functions be optimized to increase speed? Please note that the inserted data is a random character set.

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 a table is created, it runs through

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

Is it possible to optimize any of the above questions?

+5
source share
2 answers

Java API, , , commit() . conn.setAutoCommit(false). SQLite /. , .

EDIT: , , . :

. . , , . , , . . .

+11

executeBatch , , 10 executeBatch. . int[] updateCounts = prep.executeBatch(); , 1000 . if, counter % 1000 == 0. , .

0

More articles:


All Articles