SQLite - Increase speed of insertion

Question

I have a method which reads data from file line by line and takes value between coma, then puts this value into INSERT query. Data in file saved in this way:

–,08:10,–,20:20,08:15,08:16,20:26,20:27,08:20,08:21,20:31,20:32,08:30,08:31,20:40,20:41,08:37,08:38,20:46
20:47,08:48,08:50,20:56,20:57,09:00,09:01,21:07,21:08
08:53,–,17:43,09:01,09:03,09:13,09:15,18:02,18:04,–,–,09:19,09:25

Here is actual my code:

    public void insertTime(SQLiteDatabase database, String table) throws FileNotFoundException {
        BufferedReader br = null;
        String line;

        try {
            int j = 0;
            br = new BufferedReader(new InputStreamReader(context.getAssets().open("time.txt")));
            database.beginTransaction();
            while ((line = br.readLine()) != null) {
                j++;

                String query = "INSERT INTO "+table+""+j+" (arrival, departure) VALUES (?,?)";
                SQLiteStatement statement = database.compileStatement(query);
                // use comma as separator
                String[] time = line.split(",");


                for(int i = 1; i < time.length; i+=2) {

                    statement.bindString(1,time[i-1]);//arrival
                    statement.bindString(2,time[i]);//departure
                    statement.executeInsert();
                    statement.clearBindings();
                }

            }
                database.setTransactionSuccessful();
                database.endTransaction();

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

The problem is that data insert very slow, despite I use SQLiteStatement and transactions. For example, when I insert 69000 rows it takes about 65,929 seconds.

What have I to change in my code to improve speed of insertion ?

UPDATE

OK, I have simplified my code, I got rid of BufferedReader and now it looks like this

public void insertTime(SQLiteDatabase database) throws FileNotFoundException {
        database.beginTransaction();
        int r = 0;
        while (r < 122) {
            r++;
            String query = "INSERT INTO table_1 (arrival, departure) VALUES (?,?)";
            SQLiteStatement statement = database.compileStatement(query);

            for(int i = 1; i < 1100; i++) {
                statement.bindString(1,i+"");//arrival
                statement.bindString(2,i+"");//departure
                statement.executeInsert();
                statement.clearBindings();
            }
        }

        database.setTransactionSuccessful();
        database.endTransaction();
}

But it still so long inserts data, more than 2 min. Do you have any ideas how to increase speed of my second example ?

Possible duplicate of [Improve INSERT-per-second performance of SQLite?](http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) — SoroushA, Jul 11 '16 at 17:00
there all code wrote on C how can it help me if I use java ? — gigs, Jul 11 '16 at 17:33
sqlite is sqlite everywhere. the only thing that is C in that post is how to execute an sqlite command. The sql statements are the same in any medium. — SoroushA, Jul 11 '16 at 18:57
Yes, but I did almost all like said there. I used transactions and prepared statement, but speed still slow. — gigs, Jul 11 '16 at 19:22

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

Here is a very very detailed post on every method of increasing SQL insertion speed.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 11 '16 at 17:00

SoroushA

2,043
1
13
29

score 0 · Answer 2 · answered Jul 11 '16 at 17:06

0

Move beginTransaction() and setTransactionSuccessful() outside of while loop and it will be way faster.

answered Jul 11 '16 at 17:06

sergej shafarenka

20,071
7
67
86

I did like you said but speed still the same - about 65 seconds – gigs Jul 11 '16 at 17:20
Can you share your changes? – sergej shafarenka Jul 11 '16 at 18:28
Hmm. Difficult to say then. Try to add some code collecting execution time of compileStatement() and executeInsert() methods to see what the bottleneck is. Once you know it, there will be a solution. – sergej shafarenka Jul 11 '16 at 19:48

score 0 · Answer 3 · answered Jul 11 '16 at 17:06

A new transaction is started for each item in the while() loop. It might go a bit faster if you only have 1 transaction to do all your insertions.

Also, when your data is corrupt and String.split doesn't give you at least 2 items, then your transaction will not be ended properly due to an Exception being thrown.

score 0 · Answer 4 · answered Jul 11 '16 at 21:33

0

Every time you insert a row in a table with indexes, the indexes have to be adjusted. That operation can be costly. Indexes are kept as b-trees and if you hit the rebalance point, you're bound to have a slowdown. One thing you can do to test this is to remove your indexes. You could also drop the indexes, insert, then re-create the indexes.

answered Jul 11 '16 at 21:33

MPelletier

16,256
15
86
137

I also think to make PRAGMA synchronous=OFF. But, where should I put this command in my code ? – gigs Jul 12 '16 at 10:25
@gigs In theory, anywhere after your connection. But I'd say just before your transaction and turn it ON again right after you end your transaction if you keep using that connection. – MPelletier Jul 12 '16 at 14:12
I added "database.execSQL("PRAGMA synchronous=OFF")" before transaction and "database.execSQL("PRAGMA synchronous=NORMAL")" after transaction. But it gives me an error: android.database.sqlite.SQLiteException: Safety level may not be changed inside a transaction (code 1): , while compiling: PRAGMA synchronous=OFF – gigs Jul 12 '16 at 15:08
@gigs I haven't worked with Android and SQLite together. Try to move your PRAGMA just after your connection as a test. – MPelletier Jul 12 '16 at 15:10

tm1701 · Answer 5 · 2021-04-30T17:10:44.903

For those using JDBC (Java): to be sure, do you first set the autoCommit to FALSE?

I guess so, because you work with explicit transactions.

The performace gain I got by explicitly setting the autocommit off was over 1000 times!

So:

Class.forName("org.sqlite.JDBC");
String urlInput = "jdbc:sqlite:" + databaseFile;
databaseConnection = DriverManager.getConnection(urlInput);
databaseConnection.setAutoCommit( false);

And:

String sql = "INSERT INTO " + TABLE_NAME + " ( type, bi, ci, fvi, tvi, content_type) VALUES ('V',?,?,?,?,'rtf')";
PreparedStatement psi = databaseConnection.prepareStatement(sql);
for( Item item :  items) {
    psi.setInt(1, item.property1);
    // ....
    count = psi.executeUpdate();
}
databaseConnection.commit();
databaseConnection.setAutoCommit( true);

So, when somebody forgets this, this may have a huge effect.

SQLite - Increase speed of insertion

5 Answers5