SQLITE bulk inserts to increase optimization with UPDATE and INSERT

Question

I have seen a lot of posts on optimizing SQLITE on android with bulk inserts Currently its taking 90 seconds to do 900 inserts/updates. I added the Begin/End Transaction around them but only saw minor improvements. So I want to add I believe SQLiteStatement

Here is my code currently

static ArrayList<ContentSystem> csList = new ArrayList<ContentSystem>();

..fill out csList..

_dh.BeginTransaction(Table);
for(int i = 0; i < csList.size(); ++i)
{
    addItem(ma, csList.get(i), dh, Table, Key);
}
_dh.setTransactionSuccessful();
_dh.EndTransaction(Table);

public static void addItem(final MainActivity ma, ContentSystem cs,
final DatabaseHandler dh, String Table, String Key) 
{
         worked = dh.UpdateItem(cs.cv, Table, Key, cs.UUID);
         if (worked == 0) 
         {
            worked = dh.AddData(Table, cs.cv);
            if (worked <= 0) 
            {
               ErrorLogger.AddError("Error") 
            }
         }
}

My problem is that if my csList contains ~1000 items and some are already in my list, some are not so I am currently doing a update if the update returns 0 then I do an add

How could I get something like this to work in a bulk statement?

A bit more info

dh.Update

int wok = db.update(table, values, Key + " = ?", new String[] { Item });

dh.Add

int work = (int) db.insert(table, null, values);

ContentSystem is a list of ContentValues

Refer to this thread, it's incredible - http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite — bclymer, Oct 10 '13 at 19:06
@bclymer Yes - that is one of the ones I have been using - I don't understand how do do multiple types of queries in a bulk transaction - I know I need to do them - which is in a nutshell what that shows — GregM, Oct 10 '13 at 19:09
I'm surprised that thread was available to you guys since the question was closed. In any event, since my reputation is unjustifiably high, I voted to re-open it. Perhaps others will do so as well. — Dan Bracuk, Oct 10 '13 at 19:23
@DanBracuk Not sure what your talking about... The link that bclymer provided has been protected - in any event I am trying to get help on the matter at hand.. Thanks — GregM, Oct 10 '13 at 19:28
I just clicked on the link and the title was "How do I improve the performance of SQLite? [closed]". The closure reason was that it was not clear what was being asked. That's true, but I voted to re-open it anyway. — Dan Bracuk, Oct 10 '13 at 22:10

score 1 · Accepted Answer · answered Oct 10 '13 at 20:17

1

Try INSERT OR REPLACE, instead of either just an update or a failed update followed by an insert.

answered Oct 10 '13 at 20:17

CommonsWare

986,068
189
2,389
2,491

@morty what results did you find with this? – ataulm Dec 26 '13 at 18:41

score 0 · Answer 2 · answered Oct 10 '13 at 19:50

0

Are you sure the instantiated SQLiteDatabase object in your DatabaseHandler class is the same as _dh?

You might have started a transaction for _dh, but that might not even be the instantiated object that is actually doing any work.

answered Oct 10 '13 at 19:50

Gak2

2,661
1
16
28

This is a section for answers - The code works fine as is (albeit room for improvement)- this is an optimization question to change my code to use prepared statements and bulk transactions - also the database system is single threaded and yes _dh is the cored sqlitedatabase object – GregM Oct 10 '13 at 20:00

SQLITE bulk inserts to increase optimization with UPDATE and INSERT

2 Answers2