Optimize SQLite3 on iPhone

Question

I have an app using SQLite3. It's running pretty well, but I would like suggestions on speeding up SQLite (write) access. Most of the database operations are writing triples (int, int, double) of numbers, about 20-50 triples per second. There is an occasional read, but is about as rare as hen's teeth (mostly occurs on loading only).

Does anyone have suggestions on optimizing (or at lease de-pessimizing) the code? Any SQLite3 shortcuts I missed? (Using CoreData is right out because of other stuff in the program.)

I prepare the statement st1 during initialization. It is prepared from

const char *sql = "insert into historic values (?, ?, ?)";
if (sqlite3_prepare_v2(database, sql, -1, &st1, NULL) == SQLITE_OK) ....

This preparation is one-time. The snippet I am using for saving is below (i've removed error checking here for clarity).

-(BOOL) saveHvalue:(int) fid time:(int) t value:(double) v type:(int) ftype
{
    {
     sqlite3_bind_int(st1, 1, fid);
     sqlite3_bind_int(st1, 2, t);
     sqlite3_bind_int(st1, 3, ftype);

     sqlite3_step(st1);
     sqlite3_reset(st1);
     sqlite3_clear_bindings(st1);
    }

    return YES;
}

Answer 1

Are you batching the writes in a transaction? This alone will save you a lot of time; you can't batch them all in a single transaction or the journal will be gigantic, but 1 transaction per 500-1000 inserts will speed up your code tremendously.

Answer 2

Do any of your columns in historic have indices? If they do, it's generally faster to drop the index, insert your stuff, and then re-add the index (if you're adding a LOT all at once).

Answer 3

I never usually reply to threads usually but this worked wonders for me (after days trying to optimize in other areas)!

Basically I wrapped my for loop of inserts with:

sqlite3_exec(db, "BEGIN", 0, 0, 0);

for () {
-- inserts --
}

sqlite3_exec(db, "COMMIT", 0, 0, 0);