How to avoid app failure against large database manipulation

Question

I am developing an android application in which I need to download an JSON string and save it in SQlite database in a specific format (In my perspective, I have no other option to choose any other data-storage). And this is my table-structure:

problem_table(pid INTEGER PRIMARY KEY, 
              num TEXT, title TEXT, 
              dacu INTEGER, 
              verdict_series TEXT)

And at launch I need almost 4200 rows to be entered into the database table. I am working on emulator and when I launch the app, it works perfectly. But the app seemed to be freeze for a while after database manipulation is begin. Eventually the app manages to insert all the row but take pretty much time. Even at a point it shows the following look:

enter image description here

So how can I reduce the time-memory complexity or how can I do this in more optimized way or avoid this temporary failure?

N.S. : I didn't check it in any real device yet for lack of my scope. My emulator is using 512 RAM and 48 heap size.

Emulator is way too slow, don't use emulator to check the time taken to insert these values, try it on a device. — Vedang Jadhav, Aug 02 '13 at 11:07

score 1 · Answer 1 · answered Aug 02 '13 at 10:08

1

Don't do your database manipulations in UI thread but in an AsyncTask, Thread, Service or whatever, but not in the UI Thread.

answered Aug 02 '13 at 10:08

Jivy

1,213
1
10
10

Maybe you have a problem with this async task bescause your error message is displayed only when a long task is made in main thread. Can you give us the code of your asynctask to try to resolve the problem ? Thanks! – Jivy Aug 04 '13 at 11:06

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

I solved it by @Jakobud answer given here

Answer: Normally, each time db.insert() is used, SQLite creates a transaction (and resulting journal file in the filesystem). If you use db.beginTransaction() and db.endTransaction() SQLite commits all the inserts at the same time, dramatically speeding things up.

Here is some pseudo code from: Batch insert to SQLite database on Android

try
{
  db.beginTransaction();
  for each record in the list
  {
    do_some_processing();
    if (line represent a valid  entry)
    {
      db.insert(SOME_TABLE, null, SOME_VALUE);
    }
    some_other_processing();
  }
  db.setTransactionSuccessful();
}
catch (SQLException e) {}
finally
{
  db.endTransaction();
}

How to avoid app failure against large database manipulation

2 Answers2