Bulk Insert to Sqlite3 causing Db Error: Library routine called out of sequence

Question

I need to load some configuration data after app updates for my iphone app. I bundle in a file with a bunch of SQL statements (800+) to run on first launch. It looks like I may be painted in a corner now. If I run it on the main thread at startup, it take so long to run that the app crashes due to the startup taking too long. If I run it on a separate thread, I am getting database contention errors (Library routine called out of sequence). I think this is because the app is continuing to load and read the DB on the main thread.

Here is the method that receives the data form the CSV file and then loops through and writes to the DB.

Any ideas about how to either make this run faster on startup or run successfully without contention on a low priority background thread?

+ (void) updateDB:(NSString *)data {
    NSArray  *lineArray = [data componentsSeparatedByString:@"\n"];
    if (sqlite3_open([[BIUtility getDBPath] UTF8String], &database) != SQLITE_OK) {
        sqlite3_close(database);
        NSLog(@"Failed to opendatabase in updateDB");
    }
    char *errorMsg;

    for(int k = 0; k < [lineArray count]; k++){
        NSString *loadSQLi = [lineArray objectAtIndex:k];

        if (sqlite3_exec(database, [loadSQLi UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
            NSLog(@"DB Error. '%s'", sqlite3_errmsg(database));
        }

    }

    if(database) sqlite3_close(database);

}

Answer 1

You can make this faster by doing all inserts in a single transaction instead of doing the inserts in separate transactions as it happens by default in that code.

Answer 2

+ (void) updateDB:(NSString *)data {
    NSArray  *lineArray = [data componentsSeparatedByString:@"\n"];
    if (sqlite3_open([[BIUtility getDBPath] UTF8String], &database) != SQLITE_OK) {
        sqlite3_close(database);
        NSLog(@"Failed to opendatabase in updateDB");
    }
    char *errorMsg;

    execQuery(@"Begin Transaction");

    for(int k = 0; k < [lineArray count]; k++){
        NSString *loadSQLi = [lineArray objectAtIndex:k];
        execQuery(loadSQLi);
    }

    execQuery(@"Commit");

    if(database) sqlite3_close(database);

}

+ (void) execQuery:(NSString *)query {
    char *errorMsg;
    if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
        NSLog(@"DB Error. '%s'", sqlite3_errmsg(database));
    }
}