iphone - 从API插入34000条记录到iphone中的sqlite

Question

我必须根据从 JSON API 获取的数据创建一个 sqlite 数据库。代码工作正常，通过 for 循环将它们一一添加，但每次点击 api 响应时间为 1 秒，因此 34000 秒加上通过代码将它们插入到 sqlite 中将需要大约 9 小时。有什么办法可以加快速度吗？

编辑:我正在使用 Objective C/sqlite3 框架/Xcode 4.2

代码如下...

 dbPath=[self.databasePath UTF8String];
if(sqlite3_open(dbPath,&database)==SQLITE_OK)
{
  //   sqlite3_exec(database, "BEGIN", 0, 0, 0);
    const char *sqlstatement="insert into artist values(?,?,?,?,?)";
    sqlite3_stmt *compiledstatement;

    if(sqlite3_prepare_v2(database,sqlstatement , -1, &compiledstatement, NULL)==SQLITE_OK)
    {    
for(i=4611;i<=34803;i++)
{  
    NSURLResponse *response;
    NSError *err;
  NSData *data= [NSURLConnection sendSynchronousRequest:[NSURLRequest requestWithURL:[NSURL URLWithString:[NSString stringWithFormat:@"API&id=%i",i]]] returningResponse:&response error:&err];
    if(data.length>0)
    {
        NSError *err;
        NSDictionary *jsonDict=[NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingAllowFragments error:&err];
        // sqlite3_exec(database, "BEGIN", 0, 0, 0);




                sqlite3_bind_text(compiledstatement,1,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"id"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,2,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"name"] UTF8String], -1, SQLITE_TRANSIENT);
                if([[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"])
                    sqlite3_bind_text(compiledstatement,3,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"] UTF8String], -1, SQLITE_TRANSIENT);
                else
                    sqlite3_bind_text(compiledstatement,3,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if([[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"])
                    sqlite3_bind_text(compiledstatement,4,[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"] UTF8String], -1, SQLITE_TRANSIENT);
                else
                    sqlite3_bind_text(compiledstatement,4,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if([[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0]objectForKey:@"url"])
                    sqlite3_bind_text(compiledstatement,5,[[[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0] objectForKey:@"url"] UTF8String], -1, SQLITE_TRANSIENT);
                else
                    sqlite3_bind_text(compiledstatement,5,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if(sqlite3_step(compiledstatement)==SQLITE_DONE)
                {
                    NSLog(@"done %i",i);
                }
                else NSLog(@"ERROR");


        }
        sqlite3_reset(compiledstatement);
                }

    }

}

else
    NSLog(@"error");

sqlite3_close(database);

Answer 1

是否可以重组您的代码，以便您不在每次迭代时打开数据库？

• 打开数据库
• 开始事务 sqlite3_exec(..., "BEGIN", ...)
• 编译语句
• 迭代数据集
  • 插入记录
• 最终确定编译语句
• 提交事务 sqlite3_exec(..., {"ROLLBACK"或 "COMMIT"}, ...)
• 关闭数据库

这与您现在的情况形成鲜明对比

• 迭代数据集
  • 打开数据库
  • 编译语句
  • 插入记录
  • 最终确定编译语句
  • 关闭数据库

按照您的方式进行操作的开销将影响性能。尝试重构我上面概述的方法，看看效果如何。

编辑

我已经重新格式化了您的代码以表明我正在谈论的内容。此外，我认为影响您的另一个性能(如另一位用户所示)是 JSON 调用。这可能才是真正让你放慢速度的原因。

dbPath=[self.databasePath UTF8String];
if(sqlite3_open(dbPath,&database)==SQLITE_OK)
{
    sqlite3_exec(database, "BEGIN", 0, 0, 0);
    const char *sqlstatement="insert into artist values(?,?,?,?,?)";
    sqlite3_stmt *compiledstatement;

    if(sqlite3_prepare_v2(database,sqlstatement , -1, &compiledstatement, NULL)==SQLITE_OK)
    {
        int hasError= 0;
        for(i=4611; hasError == 0 && i<=34803; i++)
        {  
            NSURLResponse *response;
            NSError *err;
            NSData *data= [NSURLConnection sendSynchronousRequest:[NSURLRequest requestWithURL:[NSURL URLWithString:[NSString stringWithFormat:@"API&id=%i",i]]] returningResponse:&response error:&err];
            if(data.length>0)
            {
                NSDictionary *jsonDict=[NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingAllowFragments error:&err];
                // sqlite3_exec(database, "BEGIN", 0, 0, 0);
                sqlite3_bind_text(compiledstatement,1,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"id"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,2,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"name"] UTF8String], -1, SQLITE_TRANSIENT);
                if([[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"])
                    sqlite3_bind_text(compiledstatement,3,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"] UTF8String], -1, SQLITE_TRANSIENT);
                else
                    sqlite3_bind_text(compiledstatement,3,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if([[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"])
                    sqlite3_bind_text(compiledstatement,4,[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"] UTF8String], -1, SQLITE_TRANSIENT);
                else
                    sqlite3_bind_text(compiledstatement,4,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if([[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0]objectForKey:@"url"])
                    sqlite3_bind_text(compiledstatement,5,[[[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0] objectForKey:@"url"] UTF8String], -1, SQLITE_TRANSIENT);
                else
                    sqlite3_bind_text(compiledstatement,5,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if(sqlite3_step(compiledstatement)==SQLITE_DONE)
                {
                    NSLog(@"done %i",i);
                }
                else {
                    NSLog(@"ERROR");
                    hasError= 1;
                }
            }
            sqlite3_reset(compiledstatement);
        }
        // Really need to check error conditions with commit/rollback
        if( hasError == 0 ) {
            sqlite3_exec(database, "COMMIT", 0, 0, 0);
        }
        else {
            sqlite3_exec(database, "ROLLBACK", 0, 0, 0);
        }
    }
    sqlite3_close(database);
}
else {
    NSLog(@"error");
}