Android Bulk insert Or Update rather then insertOrReplace

Question

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?

Answer 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.