CODEWITHSUNDEEP
androidperformancesqlite
gigs
gigs

Reputation: 1531

SQLite - Increase speed of insertion

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 ?

Upvotes: 0

Views: 3154

Answers (5)

tm1701
tm1701

Reputation: 7581

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.

Upvotes: 0

MPelletier
MPelletier

Reputation: 16657

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.

Upvotes: 0

ByteWelder
ByteWelder

Reputation: 5604

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.

Upvotes: 0

sergej shafarenka
sergej shafarenka

Reputation: 20406

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

Upvotes: 0

SoroushA
SoroushA

Reputation: 2121

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

Upvotes: 1

Related Questions