Store huge data in SQLite

This question already has an answer here:

Improve INSERT-per-second performance of SQLite? 10 answers

---

I would suggest you read this Stack Overflow question:

How do I improve the performance of SQLite?

It's a very thourough look at how to improve the performance of SQLite in general, and it was very helpful when I was hitting speed problems trying to insert 100,000 records into an SQLite database on iOS.

In specific, the use of Transactions dramatically cut down on the overall insert speed. Here is a short block of sample code so you can see what I mean:

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, 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], -1, SQLITE_TRANSIENT);  // Region
        sqlite3_bind_text(insertStatement, 2, [[row objectAtIndex: 1] UTF8String], -1, SQLITE_TRANSIENT);  // District
        sqlite3_bind_text(insertStatement, 3, [[row objectAtIndex: 2] UTF8String], -1, SQLITE_TRANSIENT);  // Territory

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

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

sqlite3_exec(mapDB, "END TRANSACTION", NULL, NULL, &sqliteError);

sqlite3_finalize(insertStatement);

The sqlite3_exec with the BEGIN and END TRANSACTION statements are the magic.

链接地址: http://www.djcxy.com/p/19772.html

上一篇: 插入到Sqlite很慢

下一篇: 在SQLite中存储大量数据