CODEWITHSUNDEEP
iphoneobjective-csqlite
John Smith
John Smith

Reputation: 12797

Optimize SQLite3 on iPhone

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;
}

Upvotes: 1

Views: 1654

Answers (3)

Codz
Codz

Reputation: 1

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);

Upvotes: 0

Dave DeLong
Dave DeLong

Reputation: 243146

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).

Upvotes: 0

Ana Betts
Ana Betts

Reputation: 74654

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.

Upvotes: 8

Related Questions