我想在我的iPad应用程序中将从Web服务获取的40000条记录插入到sqlite数据库中.
- (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; }
解决方法
为了加快插入速度,您需要做三件事：
>在循环外移动sqlite3_open的调用.目前,循环未显示,因此我假设它在您的代码段之外
>添加BEGIN TRANSACTION和COMMIT TRANSACTION调用 – 您需要在插入循环之前开始事务,并在循环结束后立即结束.
>使formatStringQueryInsertWithTable真正参数化 – 目前看来你没有充分利用预准备语句,因为尽管使用了sqlite3_prepare_v2,你的代码中没有调用sqlite3_bind_XYZ.
这是a nice post that shows you how to do all of the above.它是普通的C,但它可以作为Objective C程序的一部分正常工作.
char* errorMessage; sqlite3_exec(mDb,"BEGIN TRANSACTION",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,4,getDouble()); sqlite3_bind_int(stmt,5,getInt()); sqlite3_bind_int(stmt,6,7,getInt()); if (sqlite3_step(stmt) != sqlITE_DONE) { printf("Commit Failed!\n"); } sqlite3_reset(stmt); } sqlite3_exec(mDb,"COMMIT TRANSACTION",&errorMessage); sqlite3_finalize(stmt);
