How to insert 40000 records fast into an sqlite database in an iPad
17,584
Solution 1
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
andCOMMIT 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 usingsqlite3_prepare_v2
, you have no calls ofsqlite3_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);
Solution 2
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.
Comments
-
OscarVGG almost 2 years
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; }