3

My Problem:

I m working on an Android application (using SQLite) where I have to fetch a lot of data from a server and store it to DB. Currently, I m getting a list from the server, Loop over it and inserting it into DB one by one. But before every record insertion, I m checking if it already exists in DB. If does then I update that record. This is happing across the application ( My app have over 50 tables ). Now a week ago I noticed this process taking extra time. I mean we got a list from the server, we loop through it and run two queries for each record ( One to check whether it already exist or not and one is for insert or update).

Now I need to optimise it. I know about batch insertion and I'm thinking about inserting all records at once. But there's a problem in it. I need to check if the record already exists in DB or not. If yes then update else insert. I know about insetOrReplace but it does not serve my purpose. I need to update some specific records rather than replacing everything (in the case of data already in DB and filled by the user from the app locally).

So what would be the solution of it? Do I have to check which records already exists in DB by one query then run one query to update the records and one to insert remaining? But it still three queries? Can it be solved by one query only? Or any other solution?

Nouman Ghaffar
  • 3,780
  • 1
  • 29
  • 37
  • Do you know about `SQLTransaction` ? [Check this](http://stackoverflow.com/questions/28188164/android-sqlite-performance/28188228#28188228) – M D May 15 '17 at 06:11
  • u can use #realm sdk https://news.realm.io/news/realm-for-android/ .. its more efficient than sqlite but it increased your app size much more.. your need surely ll b fulfilled with realm. – altu May 15 '17 at 06:14
  • To use realm I have to change the structure a lot . And also with my tons of tables it will increase app size.. – Nouman Ghaffar May 15 '17 at 08:42

4 Answers4

1

After two days of work. I finally found a solution. Which increase process speed more than 35%. Also, this technique helps my case (insert If not exist else update). SQLite has method insertOrIgnore. As name refer, it inserts the record or ignores it (on primary Key). So for the first part. 

 insert or ignore into contact ( _id  , cntct_id ) )  
 values ( COALESCE ( ( Select _id from contact where _id = '10' or cntct_id = '46'  ) ,null ), '46') ;

Now, this insert check, if primary key _id already exists it will ignore the insertion. Here is the other part. Which will update the record?

Update contact set cntct_id = '46' ,_id='10'  , where _id = 10 or cntct_id = '46';

So I generate 30 queries like these (create a single String which holds all of these) and run it using SQL Batch insertion. Like this

    SQLiteStatement statementAdd = database.compileStatement(addQuery);
statementAdd.execute();

One thing to consider here while doing this. Once a record is inserted it will update ( with same values ) as well ( A little overhead but in the case of ignore it works perfectly. Hope it helps.

Nouman Ghaffar
  • 3,780
  • 1
  • 29
  • 37
0

If you want simple solution then delete data on the basis of your primary and insert using batch/bulk insertion

KKSINGLA
  • 1,284
  • 2
  • 10
  • 22
  • https://github.com/redwarp/Android-LazyDatabase?utm_source=android-arsenal.com&utm_medium=referral&utm_campaign=1887 – KKSINGLA May 15 '17 at 06:19
0

SQLite is an embedded database without client/server communication overhead, so it does not really matter whether you execute lots of simple commands or do some batch operation (the actual C API does not even have batch operations).

To execute lots of commands quickly, put all of them into a single transaction.

CL.
  • 173,858
  • 17
  • 217
  • 259
-1

NoSQL is one of the best solution for performances but it might be too late for you. You have already over 50 tables...

CursorLoader would be nice solution. It does not effect execute time but at least you can show loading dialog to make it performatic if you didnt implement yet.

I dont know much about your database architecture but another solution would be getting diff set from your server up to your db version that hold in both platform (server and you are).

Another suggestion would be that override hash&equal methods that represent record id. So your method which checks if it is exist would work faster when you use .equals() method if you are checking its existance by yourself.

Your point should be avoid bulk operations.

Good luck there.

Emre Aktürk
  • 3,306
  • 2
  • 18
  • 30