批量插入到iphone上的sqlite数据库中 [英] Bulk inserts into sqlite db on the iphone

问题描述

我正在插入一批100条记录，每条记录包含一个包含任意长HTML字符串的dictonary，而且按照上帝的说法，它很慢。在iphone上，runloop在此事务期间阻塞了几秒钟。我唯一可以使用另一个线程吗？我已经使用了几个从HTTP服务器获取数据，并且sqlite文档显然不鼓励使用数据库进行线程化，即使它应该是线程安全的......有什么我做的非常错误，如果修复，会大幅减少完成整个操作所需的时间？

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

推荐答案

你需要注意的是那个SQLite文档警告您远离多个线程访问/写入数据库。只要您从单个线程访问数据库，您就可以了。如果该线程是您的程序的主线程或其他线程并不重要。

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.

请记住，iPhone上编译的SQLite版本的线程模式设置为多线程，根据文档，禁用数据库连接上的静音应用程序负责序列化对数据库连接和预处理语句的访问，但是启用了其他互斥锁，这样只要没有两个线程尝试使用相同的数据库连接，SQLite就可以安全地在多线程环境中使用。同时。因此，如果您决定将此事务放在另一个线程上，请注意您尝试对数据库执行的其他操作。

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.

话虽如此，我先说按照Yonel的建议切换到BEGIN和COMMIT。如果这没有帮助，请将事务移动到另一个线程。根据我所听到的情况，使用blob可能会非常缓慢。

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.

这篇关于批量插入到iphone上的sqlite数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！