ios – 在SQLite中存储大量数据

解决方法

How do I improve the performance of SQLite?

一般来说,这是如何提高sqlite性能的一个非常好的看法,当我遇到速度问题试图在iOS上的sqlite数据库中插入100,000条记录时,它非常有用.

具体而言,事务的使用大大降低了整体插入速度.这是一小段示例代码,您可以看到我的意思：

const char *dbpath = [[Utilities pathInDocumentsFolder: my_database] UTF8String];
const char *sql = "INSERT INTO Filters (Region,DiStrict,Territory) " \
    "VALUES (?,?,?)";
sqlite3 *mapDB;
char *sqliteError;

sqlite3_stmt *insertStatement;

sqlite3_open(dbpath,&mapDB);

sqlite3_exec(mapDB,"BEGIN transaCTION",NULL,&sqliteError);

if (sqlite3_prepare_v2(mapDB,sql,-1,&insertStatement,NULL) == sqlITE_OK) {
    for (NSArray *row in filtersArray) {
        sqlite3_bind_text(insertStatement,1,[[row objectATindex: 0] UTF8String],sqlITE_TRANSIENT);  // Region
        sqlite3_bind_text(insertStatement,2,[[row objectATindex: 1] UTF8String],sqlITE_TRANSIENT);  // DiStrict
        sqlite3_bind_text(insertStatement,3,[[row objectATindex: 2] UTF8String],sqlITE_TRANSIENT);  // Territory

        if (sqlite3_step(insertStatement) != sqlITE_DONE) {
            break;
        }

        sqlite3_clear_bindings(insertStatement);
        sqlite3_reset(insertStatement);
    }
}

sqlite3_exec(mapDB,"END transaCTION",&sqliteError);

sqlite3_finalize(insertStatement);

带有BEGIN和END transaCTION语句的sqlite3_exec是神奇的.