How to do bulk replace with android sqlite and contentvalue

Question

I have an android sqlite database that is currently very slow to execute.

per the advice of Improve INSERT-per-second performance of SQLite? I have changed from doing update if failed insert routine to just use replace (which is the same as REPLACE INTO also known as INSERT OR REPLACE)

I now want to change from doing one replace at a time to doing hundreds at a time

static ArrayList<ContentValues> cvs= new ArrayList<ContentSystem>();



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

into using the bulk system

SQLiteStatement stmt = _dh.db.compileStatement("Replace into tablename(..) value (?,?)");
    _dh.BeginTransaction(Table);
    for(int i = 0; i < cvs.size(); ++i)
    {
    stmt.bindString(cvs.get(i));
    }
    stmt.execute();
    _dh.EndTransaction(Table);

But I don't understand how the compile statement would look nor do I understand what I would put in the bind string function - i have the data stored in a contentvalue

Also from this http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html#execute()

Execute this SQL statement, if it is not a SELECT / INSERT / DELETE / UPDATE, for example CREATE / DROP table, view, trigger, index etc.

It appears the execute call will not work with replace?? Because it is doing insert/update is this correct?

Here is how my database is set up, and how I am using the replace call http://sqlfiddle.com/#!7/b8af8/1

Are `dh` and `_dh` the same? If yes, why are there two references to the same database? — CL., Oct 13 '13 at 08:18
How are `Begin`/`EndTransaction` implemented? Why do they need a table parameter? — CL., Oct 13 '13 at 08:20
dh, _dh same thing its semi pseudo coded for this question... begin and end really just call the actual functions on the database, some error checking and setting to successful - just wasn't necessary to show for the question at hand — GregM, Oct 13 '13 at 20:00

score 0 · Answer 1 · answered Oct 14 '13 at 06:53

Using a prepared statement will not make much of a difference to the performance (using a transaction correctly is more important), but if you want to use it, you have to handle each parameter manually, and you have to call execute once for each record:

db.beginTransaction();
try {
    SQLiteStatement stmt = db.compileStatement(
        "REPLACE INTO MyTable(x,y,z) VALUES(?,?,?)");
    for (int i = 0; i < cvs.size(); ++i) {
        ContentValues cv = cvs.get(i);
        stmt.bindString (1, cv.getAsString ("x"));
        stmt.bindString (2, cv.getAsString ("y"));
        stmt.bindInteger(3, cv.getAsInteger("z"));
        stmt.execute();
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

The difference between execute, executeInsert, and executeUpdateDelete is just in the return value; if you don't need one, you can use execute for any statement.

Thanks for the response! Curious as to why you say it would not make much of a difference? In this thread - http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite - he was able to take using just the begin/end transaction at 38 seconds to 16 seconds — GregM, Oct 14 '13 at 12:35

How to do bulk replace with android sqlite and contentvalue

1 Answers1