Sqlite3 query gets really slow under iOS 8.2

Question

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?

Thanks for your help!

- (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;

}

Answer 1

A couple of things have changed:

• 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'

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!).

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.

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