Android Bulk insert or Update, not insertOrReplace
My problem :
I am working on Android application
(using SQLite
) where I need to get a lot of data from the server and store
before DB
. I am currently fetching list
from the server, iterating over it and inserting
it into DB
one by one. But before each entry insertion
, I check if it exists in DB
. If then I update
write it down. This comes through application
(my app has over 50 tables). Now a week ago I noticed that this process takes extra time. I mean we have a list from the server, we loop through it and run two queries
for each entry (one to check if it exists) and the other for insert
or update
).
Now I need optimise
it. I know about batch insertion and I am thinking of inserting all records at once. But there is a problem with that. I need to check if an entry exists in DB
or not. If yes, then update
else insert
. I know about insetOrReplace
, but it doesn't serve my purpose. I need to update some specific records and not replace everything (in the case of data already in the DB and populated by the user from a local application).
And what will happen to him solution
? Should I check which one records
already exists in DB
one query and then execute one query up to the update
records and from one to the insert
rest? But that's three more questions? Can only one query be resolved? Or any other solution?
source to share
After two days of work. I finally found a solution. Which increases the speed of the process by more than 35%. Also, this technique helps my case (insert if no other update exists). SQLite has a method insertOrIgnore
. As the name is referenced, it either inserts a record or ignores it (at the 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 the primary key _id already exists, will ignore the insert. Here's the other part. What 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 this (create one String that contains all of these) and run it using SQL Batch insert. 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 the same values) (a bit of overhead, but if ignored, it works great. Hope this helps.
source to share
NoSQL is one of the best performing solutions, but it might be too late for you. You already have over 50 tables ...
CursorLoader would be a good solution. This doesn't affect runtime, but at least you can show the download dialog to make it executable if you haven't implemented it yet.
I don't know much about your database architecture, but another solution would be getting a diff from your server to your db version that is stored on both platforms (server and you are).
Another assumption would be to override the hash and equal methods that represent the record id. This way, your method that checks if it exists will run faster if you use the .equals () method if you check for its existence yourself.
Your task is to avoid bulk operations.
Good luck there.
source to share