CODEWITHSUNDEEP
androidmysqlsqlperformancesqlite
GregM
GregM

Reputation: 3734

SQLITE bulk inserts to increase optimization with UPDATE and INSERT

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

Upvotes: 1

Views: 1806

Answers (2)

CommonsWare
CommonsWare

Reputation: 1007228

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

Upvotes: 1

Gak2
Gak2

Reputation: 2701

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.

Upvotes: 0

Related Questions