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

Question

I want to insert 40000 records that i get from a web service into a sqlite database in my iPad app.

I wrote the following code, but it takes around 20 minutes, is there a faster way?

- (NSArray *)insertPriceSQLWithPrice:(Price *) price
{

SQLiteManager *dbInfo = [SQLiteManager sharedSQLiteManagerWithDataBaseName:@"codefuel_catalogo.sqlite"];


sqlite3 *database;

NSString *querySQL=[self formatStringQueryInsertWithTable:@"prices_list" andObject:price];


if(sqlite3_open([dbInfo.dataBasePath UTF8String], &database) == SQLITE_OK)
{
    sqlite3_stmt * compiledStatement;


    const char *query_stmt = [querySQL UTF8String];

    int result = sqlite3_prepare_v2(database, query_stmt, -1, &compiledStatement, NULL);

    if (result == SQLITE_OK)
    {
        int success = sqlite3_step(compiledStatement);

        NSLog(@"el numero de success es -> %i",success);
        if (success == SQLITE_ERROR)
            NSLog(@"Error al insertar en la base de datps");

    }
    else
        NSLog(@"Error %@ ERROR!!!!",querySQL);

    sqlite3_finalize(compiledStatement);
}

sqlite3_close(database);
return nil;
}

I'd take the opening and closing of the database out of the method. That is costing you a fair amount of time. Create and persist the connectoin through each of the 40,000 inserts then destroy it once complete. Also, the NSLogs may be slowing actual execution. I'd try running one without the logs to see how long it takes. — WhoaItsAFactorial, Jan 31 '13 at 17:23
Do you reopen database 40000 times? (You should also avoid recompilation of the statement once per record, and it's better to do all inserts in a single transaction -- but that's less important than reopening for each row). — Anton Kovalenko, Jan 31 '13 at 17:24
You can insert multiple rows per insert with the right query, and wrap the whole process in a transaction - at the moment SQLite has to flush to disk after every insert which will slow you right down. You can also reuse the prepared statement, just rebinding the changed values each time — Frederick Cheung, Jan 31 '13 at 17:27
Open the DB and leave it open, start a transaction, prepare the statement only once. — Hot Licks, Jan 31 '13 at 17:39

Sergey Kalinichenko · Accepted Answer · 2018-01-04T14:50:00.500

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);

score 5 · Answer 2 · answered Sep 22 '14 at 19:18

5

For me, calling BEGIN TRANSACTION then loading some 20 inserts, then calling COMMIT TRANSACTION gave an 18x performance increase - great tip! Caching the prepared statements was little help.

answered Sep 22 '14 at 19:18

Jason

1,323
14
19

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

2 Answers2

Linked

Related