How to update a lot of rows very fast in SWIFT and SQLITE 3?

Question

I have a table of 10000 rows, from which I have to update 1000 rows. This takes me 10-20 secs. Is there a way to do this faster ?

My 1000 sql statements are as follow: update table set value=x where rowid=y (x and y are integers, rowid is the internal rowid of sqlite3)

My sql statement I provide to my function:

func executeSQL(sSQL:String) -> Bool {
    var bReturn:Bool=false;
    var statement:COpaquePointer = nil
    if sqlite3_prepare_v2(db, sSQL, -1, &statement, nil) != SQLITE_OK {
        println("Failed to prepare statement")
        //exit(1)
    }else{
        if sqlite3_step(statement) == SQLITE_DONE {
            println(sSQL);
            bReturn=true;
        }
        sqlite3_finalize(statement);
    }
    return bReturn;
}

Are you calling this `executeSQL` function 1000 times? One option would be to prepare the statement just once instead of 1000 times. That's kind of the point of a prepared statement. Then reset it each time instead of finalizing it. — rmaddy, Jun 18 '15 at 14:34
Try a replace into ? :D The update written by you takes long also if you write in ObjectiveC:) — ares777, Jun 18 '15 at 14:34
@rmaddy. Yes I do . That sounds interresting. Do you mean I should concatenate all 1000 updates in one big SQL Statement (stmt1;stmt2;) and then execute once ? — mcfly soft, Jun 18 '15 at 14:36
No, not at all. Prepare the statement just once as `update table set value=? where rowid=?`. Then in `executeSQL` you simply bind in the two values using `sqlite3_bind_xxx` (where `xxx` is for the appropriate type), call `sqlite3_step`, and then `sqlite3_reset` (instead of finalize). You still call the function 1000 times but pass in the values instead of a prebuilt query. — rmaddy, Jun 18 '15 at 14:39
Have a look at SQLite "Transactions". Doing the batch update within a transaction should be much more effective, as the changes are only saved once to disk, when the transaction is committed. - See http://stackoverflow.com/a/2893146/1187415 for C code, which is easily converted to Swift. — Martin R, Jun 18 '15 at 14:39
possible duplicate of [Improve INSERT-per-second performance of SQLite?](http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) — Colonel Thirty Two, Jun 18 '15 at 18:02

How to update a lot of rows very fast in SWIFT and SQLITE 3?

0 Answers0