Android Sqlite Database: Slow Insertion

Android SQLite database: slow insertion

You should do batch inserts.

Pseudocode:

db.beginTransaction();
for (entry : listOfEntries) {
    db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();

That increased the speed of inserts in my apps extremely.

Update:

@Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database

Android Sqlite Performance

Use SQLite transaction for speed up

Use BEGIN TRANSACTION & END TRANSACTION for SQLite Optimization

Each SQL statement is enclosed in a new transaction block by SQLite runtime, by default. Sowhen you perform a basic DB operation such as INSERT, a transaction block will be created and wrapped around it.

Letting SQLite runtime manage the transaction for you is advisable only if your routine performs only one DB operation on a data set. However, if you are doing numerous DB operations (say INSERT inside for loop), this becomes very expensive, since it requires reopening, writing to, and closing the journal file for each statement.
You may refer

1. Android SQLite database: slow insertion

2. http://www.androidcode.ninja/android-sqlite-transaction-tutorial/

3. http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/

4. http://www.android-app-market.com/sqlite-optimization-in-android-programming-sqlite-optimization-in-android-apps.html

Android, SQLite: Inserting data in while clause is very slow

I would suggest to do batch inserts as explained in the following post:

Android SQLite database: slow insertion

It gives you multiple examples. Personally I have noticed that SQLite inserts are pretty slow, and for 3000 inserts its normal to take a long time unless you do batch inserts.

So idea is the same as the other poster posted, i.e. do it all in one transaction:

db.beginTransaction();
  for (entry : listOfEntries) {
  db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();

Sqlite batch INSERT is slow

There isn't much you can to speed up this code; handling 200,000 rows on a mobile device always is slow. (Just doing the flash I/O for that amount of data will take lots of time.)

If possible, ship the complete database with your app.

If you need to get the data from elsewhere, try to replace the JSON file with a database file, which can be ATTACHed to your main database and just copied over:

INSERT INTO main.MyTable SELECT * FROM downloadDB.MyTable;

executing INSERT commands in a row very slow

You need to put the list of INSERT statements into a transaction:

BEGIN TRANSACTION;
CREATE TABLE ...;
INSERT INTO ...;
INSERT INTO ...;
...
COMMIT;

This will dramatically reduce the execution time.

---

Related Topics