Android improve SQL processing speed

Question

I'm processing 31000 Insert SQL statements reading from a file and it takes more than 2 minutes to complete.. need some advice in improving performance..

Here is the code:

while((line=reader.readLine())!=null){

            dbs.execSQL(line);
            k=(int)(i*0.0032);
            if(k%10==0){fdb.publishProgress(k);}
            i++;
}

First Run inside SQLiteOpenHelper - onCreate(SQLiteDatabase db) — Alfa, Aug 24 '13 at 10:21
Then why not distribute the sqlite database file pre-populated? — Joachim Isaksson, Aug 24 '13 at 10:22

score 1 · Answer 1 · edited May 23 '17 at 11:59

m processing 31000 Insert SQL statements reading from a file and it takes more than 2 minutes to complete.. need some advice in improving performance..

All what you are able to do is to use BufferedReader if you actually not using it for reading from file.

And concerning to SQLite if you are processing hundreds and thousands of insert, update or delete statements a usage of transaction will rapidly increase speed performance and also it has other benefits like security and there is not danger of losing database integrity.

score 1 · Answer 2 · edited May 23 '17 at 11:59

1

Do a batch insert using technique mentioned here Is it possible to insert multiple rows at a time in an SQLite database?

Please note that you can only insert 500 rows at a time in a query using this technique. So insert 31000 records in queries containing 500 records per query. And you can publish progress after every query i.e. after every 500 inserts.

edited May 23 '17 at 11:59

Community

1
1

answered Aug 24 '13 at 10:32

M-Wajeeh

17,204
10
66
103

score 0 · Answer 3 · answered Aug 24 '13 at 10:26

0

DO this trick will work;

dbs.beginTransaction();
while((line=reader.readLine())!=null){

            dbs.execSQL(line);
            k=(int)(i*0.0032);
            if(k%10==0){fdb.publishProgress(k);}
            i++;
}
dbs.setTransactionSuccessful();
dbs.endTransaction();

answered Aug 24 '13 at 10:26

Biraj Zalavadia

28,348
10
61
77

You forgot `try`/`finally`. – CL. Aug 24 '13 at 10:45

Android improve SQL processing speed

3 Answers3