Bulk inserts into sqlite db on the iphone

I'm inserting a batch of 100 records, each containing a dictonary containing arbitrarily long HTML strings, and by god, it's slow. On the iphone, the runloop is blocking for several seconds during this transaction. Is my only recourse to use another thread? I'm already using several for acquiring data from HTTP servers, and the sqlite documentation explicitly discourages threading with the database, even though it's supposed to be thread-safe... Is there something I'm doing extremely wrong that if fixed, would drastically reduce the time it takes to complete the whole operation?

    NSString* statement;
    statement = @"BEGIN EXCLUSIVE TRANSACTION";
    sqlite3_stmt *beginStatement;
    if (sqlite3_prepare_v2(dat开发者_StackOverflowabase, [statement UTF8String], -1, &beginStatement, NULL) != SQLITE_OK) {
        printf("db error: %s\n", sqlite3_errmsg(database)); 
        return;
    }
    if (sqlite3_step(beginStatement) != SQLITE_DONE) {
        sqlite3_finalize(beginStatement);
        printf("db error: %s\n", sqlite3_errmsg(database)); 
        return;
    }

    NSTimeInterval timestampB = [[NSDate date] timeIntervalSince1970];
    statement = @"INSERT OR REPLACE INTO item (hash, tag, owner, timestamp, dictionary) VALUES (?, ?, ?, ?, ?)";
    sqlite3_stmt *compiledStatement;
    if(sqlite3_prepare_v2(database, [statement UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)
    {
        for(int i = 0; i < [items count]; i++){
            NSMutableDictionary* item = [items objectAtIndex:i];
            NSString* tag       = [item objectForKey:@"id"];
            NSInteger hash      = [[NSString stringWithFormat:@"%@%@", tag, ownerID] hash];
            NSInteger timestamp = [[item objectForKey:@"updated"] intValue];
            NSData *dictionary  = [NSKeyedArchiver archivedDataWithRootObject:item];

            sqlite3_bind_int(   compiledStatement, 1, hash);
            sqlite3_bind_text(  compiledStatement, 2, [tag UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(  compiledStatement, 3, [ownerID UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(   compiledStatement, 4, timestamp);
            sqlite3_bind_blob(  compiledStatement, 5, [dictionary bytes], [dictionary length], SQLITE_TRANSIENT);

            while(YES){
                NSInteger result = sqlite3_step(compiledStatement);
                if(result == SQLITE_DONE){
                    break;
                }
                else if(result != SQLITE_BUSY){
                    printf("db error: %s\n", sqlite3_errmsg(database)); 
                    break;
                }
            }
            sqlite3_reset(compiledStatement);
        }
        timestampB = [[NSDate date] timeIntervalSince1970] - timestampB;
        NSLog(@"Insert Time Taken: %f",timestampB);

        // COMMIT
        statement = @"COMMIT TRANSACTION";
        sqlite3_stmt *commitStatement;
        if (sqlite3_prepare_v2(database, [statement UTF8String], -1, &commitStatement, NULL) != SQLITE_OK) {
            printf("db error: %s\n", sqlite3_errmsg(database)); 
        }
        if (sqlite3_step(commitStatement) != SQLITE_DONE) {
            printf("db error: %s\n", sqlite3_errmsg(database)); 
        }

        sqlite3_finalize(beginStatement);
        sqlite3_finalize(compiledStatement);
        sqlite3_finalize(commitStatement);

The thing that you need to be aware of is that the SQLite documentation warns you away from accessing/writing to the database from multiple threads. As long as you access the database from a single thread, you'll be fine. It doesn't matter if that thread is your program's main thread or some other thread.

Keep in mind that compiled version of SQLite on the iPhone has its threading mode set to "multi-thread" which, according to the documentation, "disables mutexing on database connection and prepared statement objects. The application is responsible for serializing access to database connections and prepared statements but other mutexes are enabled so that SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time." So, if you do decide to put this transaction on another thread, be careful of what else you try to do with the database.

That being said, I'd first follow Yonel's advice and switch to "BEGIN" AND "COMMIT". If that doesn't help, move the transaction to another thread. Working with "blobs" can be pretty slow, from what I've heard.

Did you try the same as your code but with "BEGIN" and "COMMIT" instead of "BEGIN EXCLUSIVE TRANSACTION" and "COMMIT TRANSACTION" ?

I'm simply using BEGIN and COMMIT and it's pretty much faster than committing for each transaction so I guess it's working with those keywords.

http://www.sqlite.org/lang_transaction.html

I see a lot of cases where developers new to the iPhone believe the code is slow when it is simply a case of asking lightweight hardware to do to much processing. Processing several hundred (thousands?) of "arbitrarily long HTML strings" might to heavy a task for the iPhone to carry out in a timely fashion.

Remember that the iPhone isn't a very powerful piece of hardware. It pulls off all the nifty graphics with dedicated hardware whose computational power you can't access for other task. Even if you optimize the code it maybe way slower than you would intuitively expect based on your experience with full fledged laptops and desktops.

Instead of guessing where the bottleneck is, I suggest that you profile the code with Instruments (or even just using NSLog with timestamps) to see exactly where the code is spending most of its time.

A better approach to avoid blocking problems is to use an asynchronous callbacks. Try using Enorm EGO sqlite wrapper https://github.com/jdp-global/egodatabase

Have a look at my Readme section for EGODatabaseRequest - asynchronous requests /inserts to db.

2) Add the requestDidSucceed /requestDidFail callback methods.

 -(void)requestDidSucceed:(EGODatabaseRequest*)request withResult:(EGODatabaseResult*)result
    idx++
    if ([items count]<idx) [self insertRow];

}

-(void)requestDidFail:(EGODatabaseRequest*)request withError:(NSError*)error{

    NSLog(@"WARNING requestDidFail");
}




-(void)insertRow{
    NSMutableDictionary* item = [items objectAtIndex:idx];
    NSInteger hash      = [[NSString stringWithFormat:@"%@%@", tag, ownerID] hash];
    NSInteger timestamp = [[item objectForKey:@"updated"] intValue];
    NSData *dictionary  = [NSKeyedArchiver archivedDataWithRootObject:item];
    NSString *qry = [NSString stringWithFormat:@"INSERT OR REPLACE INTO item (hash, tag, owner, timestamp, dictionary) VALUES (%@, %@, %@, %@, %@);",NUMBER(hash),[tag UTF8String],[ownerID UTF8String],NUMBER(timestamp),dictionary];

    // be sure to use NSNumbers not NSIntegers
    EGODatabaseRequest* request = [[EGODatabaseRequest alloc] initWithQuery:qry parameters:nil];
    request.delegate = self;
    request.database = appDelegate.database;
    request.requestKind = EGODatabaseUpdateRequest; // use update not select
    [request fire];
    [request release];
}