在iOS 8.2下，Sqlite3查询变得非常慢 [英] Sqlite3 query gets really slow under iOS 8.2

问题描述

我已经制作了一款适用于app内sqlite数据库的应用。在iOS 8.2之前它运行良好，但在更新后查询方法的工作速度大约慢了100（!!!）倍。我试图找到有关此问题的信息，但我还没有找到任何信息。有没有人有同样的经历？这是我迄今为止完美运作的方法。你看到里面有任何错误或优化的可能吗？

I've made an app that works with sqlite database inside the app. Before iOS 8.2 it worked fine, but after update the query method works about 100(!!!) times slower. I tried to find info about this issue but I haven't found anything yet. Is anybody has same experience? Here is my method that worked perfectly until now. Do you see any error or optimalization possibilities inside it?

感谢您的帮助！

- (NSArray *)databaseContentWithQueryString:(NSString *)queryString {

NSDate *methodStart = [NSDate date];

NSMutableArray *retArray = [[NSMutableArray alloc] init];

sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_database, [queryString UTF8String], -1, &statement, nil) == SQLITE_OK) {
    while (sqlite3_step(statement) == SQLITE_ROW) {
        int columnCount = sqlite3_column_count(statement);
        NSMutableArray *valueArray = [[NSMutableArray alloc] init];
        NSMutableArray *keyArray = [[NSMutableArray alloc] init];
        for (int i=0; i<columnCount; i++) {
            int type = sqlite3_column_type(statement, i);
            char *name = (char *) sqlite3_column_name(statement, i);
            [keyArray addObject:[NSString stringWithFormat:@"%s",name]];
            int intVal;
            char *charVal;
            if (type == SQLITE_INTEGER) {
                intVal = sqlite3_column_int(statement, i);
                [valueArray addObject:[NSNumber numberWithInt:intVal]];
            }
            if (type == SQLITE_TEXT) {
                charVal = (char *) sqlite3_column_text(statement, i);
                [valueArray addObject:[NSString stringWithUTF8String:charVal]];
            }
            if (type == SQLITE_NULL) {
                intVal = 0;
                [valueArray addObject:[NSNumber numberWithInt:intVal]];
            }
        }
        NSDictionary *dict = [[NSDictionary alloc] initWithObjects:valueArray forKeys:keyArray];
        [retArray addObject:dict];
    }
    sqlite3_finalize(statement);
}

//sqlite3_close(_database);

NSDate *methodFinish = [NSDate date];
NSTimeInterval executionTime = [methodFinish timeIntervalSinceDate:methodStart];
NSLog(@"executionTime = %f s", executionTime);

return retArray;

}

推荐答案

有些事情发生了变化：

• 在iOS 8.2中，sqlite从3.7.13升级到3.8.5 *


• 在sqlite 3.8.0中，查询计划程序被替换为'下一代查询计划程序

• In iOS 8.2, sqlite was upgraded from 3.7.13 to 3.8.5 *
• In sqlite 3.8.0, the query planner was replaced with a 'Next Generation Query Planner'

这两项更改的组合可能是导致性能问题的原因。虽然NGPQ可能会提高许多复杂查询的性能，但它会对一些复杂的查询产生负面影响，比如你的（以及我的！）。

The combination of these two changes is likely the cause of your performance issue. While the NGPQ is likely to improve the performance of many complex query, it is going to have negative affects on a few complex queries, like yours (and mine!).

为了解决您的问题，我会检查您的特定查询，以查看是否缺少可能提高性能的任何索引。使用 EXPLAIN QUERY PLAN 可能会让您了解正在发生的事情。

To address your issue, I would review your specific query to see if you are missing any indexes that may improve your performance. Using EXPLAIN QUERY PLAN is likely to give you some insight into what is going on.

• 遗憾的是，除了推文之外，我找不到更好的iOS更改来源。一些科技论坛帖子的深度。

• Regrettably, I cannot find a better source for the change to iOS other than this tweet and the depths of some tech forum posts.

这篇关于在iOS 8.2下，Sqlite3查询变得非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！