How to optimize this?

Question

java sqlite insert very slow... my code is this

        for(int i = 1 ; i <= list.size(); i++ ) {
        query="INSERT OR REPLACE INTO highErrorProbabillityDB VALUES (?,?);";
        prep = localError.prepareStatement(query);
        prep.setString(1, list.get(i-1).get("ki"));
        prep.setInt(2, Integer.parseInt(list.get(i-1).get("ccolor")));
        prep.executeUpdate();
        }

The first column is primary key and On average, it takes time of about one to 150Milliseconds.

how to optimize this code?

http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database — Konstantin V. Salikhov, Jul 18 '14 at 13:46

score 2 · Answer 1 · answered Jul 18 '14 at 13:46

First of all, you could prepare statement only once:

query="INSERT OR REPLACE INTO highErrorProbabillityDB VALUES (?,?);";
prep = localError.prepareStatement(query);
for(int i = 1 ; i <= list.size(); i++ ) {
    prep.setString(1, list.get(i-1).get("ki"));
    prep.setInt(2, Integer.parseInt(list.get(i-1).get("ccolor")));
    prep.executeUpdate();
}

score 0 · Answer 2 · answered Jul 18 '14 at 13:46

0

If you have SQLite version 3.7.11 or greater then you can do multiple inserts at once like this

INSERT OR REPLACE INTO highErrorProbabillityDB 
VALUES (1,4),
       (2,5),
       (3,6),
       ...

That way you only have to execute a single query.

answered Jul 18 '14 at 13:46

juergen d

186,950
30
261
325

How to optimize this?

2 Answers2