Bulk inserts into sqlite db on the iphone...
- by akaii
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(database, [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);
