Store huge data in SQLite [duplicate]

Question

I have to add the huge data in the SQLite database and need some suggestion how I can do that functionality in iOS. I have to sync around 1 GB of data from server to the iPhone SQLite database.

I need alternative ways to store fast data in the database on the iOS side. I have tried to store data one by one, but it's taking too much time to synchronise and store data.

Answer 1

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.