开发者社区> 问答> 正文

ios真机调试,在第一次进入sqlite_step()时,非常慢,需要4s左右,有大神可以解决吗

在国外的网站上找到一下类似的问题,加以修改了一下,希望有人可以帮帮忙,折腾了两天了.也不知道是什么问题

I have an app for iPhone who use the sqlite to read a database. Before I update my iOS from 4.3 to the latest one everything was fine. But since then, I notice a considerable loading of the data. I used the libsqlite3.0.dylib, which comes with the iOS sdk. I also found the source code (sqlite3.h and sqlite3.c) for version 3.7.6.3 but still have the same problem. So I traced the call of the sqlite3_step until I reached the this line: 
 for(pc=p->pc; rc==SQLITE_OK; pc++)
 {}
  in the method [b]sqlite3VdbeExec[/b], line [b]62789[/b] That for loop is the reason of the block. It remains there for few seconds, 3 - 6 seconds, but for the user interface this is lot of time. Even if I execute it on another thread it will still slow down the loading of the data which is not nice. Does anyone knows why and how this can be fixed? This is part of my code, where the method is called. Remember this block happen only at the first execution! 


     sqlite3* database;
     NSString *databasePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database.sqlite"];
      NSString *sqlQuery = ...; 
     if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
      { 
          const char* sqlStatement = [sqlQuery UTF8String]; 
          sqlite3_stmt* compiledStatement; 
          if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) 
     {
      while(sqlite3_step(compiledStatement) == SQLITE_ROW)
      { 
     ... // process here, close the loop, close the db connection, etc...
 But in the simulator will soon,
 SQL statement has four conditions:
 like this "where Categorys like '%;1036;%' and DistrictID=48 order by Ranking desc,OrderNo" 
 Categorys  is string
 DistrictID,ranking,orderNo is int

 I tested several ways with the 4 conditions:
 categorys,districtID,ranking  the 3 conditions ,,, is slow, about 4s 
 categorys, districtID, orderNO       about 1s
 districtID, Ranking,orderNO  also very slow,about 3.7s
 districtID,ranking   2 conditions ,,, about 0.2s
 districtID,orderNO       about 0.2s

还是写出整条sql语句吧

select * from Shop where Categorys like '%;1036;%' and DistrictID=48 order by Ranking desc,OrderNo limit 0, 50 

该sql语句,直接在终端里面查询的话,也是非常快的,在模拟器上速度也还正常的,不知道为什么一去到真机上就会异常的慢,整个数据库才12m左右,查询50条数据不应该那么慢才对的.

先谢谢各位大神啦!

展开
收起
杨冬芳 2016-07-01 14:46:08 4394 0
1 条回答
写回答
取消 提交回答
  • IT从业

    ,在数据库中为那个字段添加索引

    2019-07-17 19:50:02
    赞同 展开评论 打赏
问答标签:
问答地址:
问答排行榜
最热
最新

相关电子书

更多
手淘iOS性能优化探索 立即下载
From Java/Android to Swift iOS 立即下载
深入剖析iOS性能优化 立即下载