iOS - 提高 SQLite 的插入性能

Question

如何提高在 SQLite 数据库中插入数据的性能。

数据量为30000条记录。代码是:

- (void)insertBranchDB:branchlistArray
{
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    int count = 0;
    for (Branch *branch in branchlistArray) {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"insert or replace into Branch (id, lang, lat, lng, province_id, district_id, optional, bus, status, name, address, contact, telephone, remark, province_name, district_name, lastupdate) values (\"%@\", \"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\")",branch._id,branch.lang,branch.lat,branch.lng,branch.province_id,branch.district_id,branch.optional,branch.bus,branch.status,branch.name,branch.address,branch.contact,branch.telephone,branch.remark,branch.province_name,branch.district_name,branch.lastupdate];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE) {
            NSLog(@"Completed to Add Branch %d",count);
            count;
        }
        else {
            NSLog(@"Failed to Add Branch %d",count);
            count;
        }
    }
    sqlite3_finalize(statement);
    sqlite3_close(database);
    }
}

Answer 1

如果您使用事务，您将看到性能的显着提高。因此，在执行所有 INSERT 语句之前，请执行 BEGIN TRANSACTION，最后执行 COMMIT(或 COMMIT TRANSACTION 或结束交易)。

请参阅Transactions讨论http://sqlite.org .