How to Insert 40000 Records Fast into an SQLite Database in an iPad

How to insert 40000 records fast into an sqlite database in an iPad

There are three things that you need to do in order to speed up the insertions:

• Move the call of sqlite3_open outside the loop. Currently, the loop is not shown, so I assume it is outside your code snippet
• Add BEGIN TRANSACTION and COMMIT TRANSACTION calls - you need to begin transaction before the insertion loop and end it right after the loop is over.
• Make formatStringQueryInsertWithTable truly parameterized - Currently it appears that you are not using prepared statements to their fullest, because despite using sqlite3_prepare_v2, you have no calls of sqlite3_bind_XYZ in your code.

Here is a nice post that shows you how to do all of the above. It is plain C, but it will work fine as part of an Objective C program.

char* errorMessage;
sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);
char buffer[] = "INSERT INTO example VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";
sqlite3_stmt* stmt;
sqlite3_prepare_v2(mDb, buffer, strlen(buffer), &stmt, NULL);
for (unsigned i = 0; i < mVal; i++) {
    std::string id = getID();
    sqlite3_bind_text(stmt, 1, id.c_str(), id.size(), SQLITE_STATIC);
    sqlite3_bind_double(stmt, 2, getDouble());
    sqlite3_bind_double(stmt, 3, getDouble());
    sqlite3_bind_double(stmt, 4, getDouble());
    sqlite3_bind_int(stmt, 5, getInt());
    sqlite3_bind_int(stmt, 6, getInt());
    sqlite3_bind_int(stmt, 7, getInt());
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        printf("Commit Failed!\n");
    }
    sqlite3_reset(stmt);
}
sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);
sqlite3_finalize(stmt);

inserting into sqlite database taking too much time - iOS

You need to use BEGIN TRANSACTION before starting to iterate over 6000 records and END TRANSACTION after all entries addition has been issued - this way you will lower I/O load and make things happened faster.

Getting unable to open database file while inserting bulk data in sqlite db

After opening database add this code

sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);

And, before closing database add this code

sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);

For more details check this link-

How to insert 40000 records fast into an sqlite database in an iPad

---

Related Topics