Accelerate the insertion into Sqlite datatbase Android?

Question

I use this function to insert data into the SQLite Android data base:

 public long insertAccount(String code,String name,int s3,int s4,String s5,String   s6,int s7,
        int s8,int s9,int s10,int s11,String s12,String s13,int s14,int s15,int s16) {

    //container and place in it the information you want inserted, updated, etc.
    ContentValues initialValues = new ContentValues();
    initialValues.put(Code, code);
    initialValues.put(Name,name);
    initialValues.put(Type, s3);
    initialValues.put(Level1, s4);
    initialValues.put(Father, s5);
    initialValues.put(ACCCurr,s6);
    initialValues.put(AccNat, s7);
    initialValues.put(LowLevel, s8);
    initialValues.put(DefNum, s9);
    initialValues.put(AccClass, s10);
    initialValues.put(SubClass, s11);
    initialValues.put(SSClass1, s12);
    initialValues.put(SSClass2, s13);
    initialValues.put(Stype1, s14);
    initialValues.put(Stype2, s15);
    initialValues.put(Stype3, s16);
    return db.insert(DATABASE_TABLE, null, initialValues);
}

But this takes much time when inserting about 70,000+ rows! How can I accelerate the process of insertion into the data base, and after the insert is done, how can I apply Update on it?

why dont you insert records from a tool instead of code? http://sqlitestudio.pl/ — Ahmad Dwaik 'Warlock', Nov 10 '13 at 07:10
I need this code into my application, which read data from MySql!! mmm But I don't know how can I append like these tools into code, or even how can I use it !! anyway Thanks I will try ,,, — , Nov 10 '13 at 10:10

score 1 · Accepted Answer · edited May 23 '17 at 10:31

1

Some options:

Prepopulate your database. See "Ship an application with a database"
Use transactions to reduce the time waiting for I/O. See e.g. "Android SQLite database: slow insertion". Likely you cannot wrap all 70k rows in a single transaction but something like 100..1000 inserts per transaction should be doable, cutting the cumulative I/O wait time by orders of magnitude.

edited May 23 '17 at 10:31

Community

1
1

answered Nov 10 '13 at 07:09

laalto

150,114
66
286
303

score 0 · Answer 2 · answered Nov 10 '13 at 06:49

Inserting into SQLlite android using PHP? how is it possible using php in android phone, I am sorry I didn't got this.

Anyways I believe you have written the java code up here and you have like 7k+ records that you want to insert in your db. The style of inserting a bulk of records in any db is called "Bulk Inserts", the idea is to create as less number of transactions as possible and rather do all the inserts in one shot; In case of relational db's like sql server and oracle its done by specific api's as well, but in sqllite the plain old idea is to make a single transaction with a bunch of data

check out this article which uses the same technique http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/ and also explains it quite well.

Many Thanks, I mean in PHP code that I using it when I read from MySQL data base to Sqlite DB android, just the transfer process by PHP code!! .. But I receive data by jason object .. then insert into Sqlite data base .. — , Nov 10 '13 at 08:13

Majid Daeinejad · Answer 3 · 2013-11-10T08:35:36.823

0

You have to use transaction to done insertion in 1 time. you can use this:

//before insertion
db.beginTransaction();
//====do insertion
//after insertion
db.setTransactionSuccessful()
db.endTransaction();

edited Nov 10 '13 at 08:35

answered Nov 10 '13 at 06:51

Majid Daeinejad

1,037
8
19

This will not insert anything because the transaction is rolled back at the end. – CL. Nov 10 '13 at 08:32
you are right @CL. oh my god. I forgot to call `db.setTransactionSuccessful()`. I edited my answer – Majid Daeinejad Nov 10 '13 at 08:37

Accelerate the insertion into Sqlite datatbase Android?

3 Answers3