Sqlite3 query gets really slow under iOS 8.2

问题:

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;

}

回答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.

回答2:

Looks like it's ok your piece of code, I just solve the same problem over here, the SQLite framework was updated and something change under the hood. Debugging the query (EXPLAIN QUERY PLAN) I saw that my query aren't using my indexes... just make sure of that.

回答3:

I've the same problem, only, for some queries.

in the old query, in the FROM (derivate table) inner join table ...

I changed the order, in FROM table inner join (derivate table) ...

I know it's strange, but the query is speedy

回答4:

I might be late for answering this, but FWIW: I had a very similar problem under a complex, runtime-generated query, which I couldn't really mess with. It runs very quickly on all devices and all iOS versions, except when running under iOS 8.2. Needless to say, our clients were complaining for days about it.

Today I found a simple solution that seems to be working. Do NOT link against neither sqlite3.dylib nor sqlite3.0.dylib. Instead, download the SQLite amalgamation source code, which consists of a C file and a header, so it's just a quick drag'n'drop to your project. Then, replace all your

#import <sqlite3.h>

by

#import "sqlite3.h"

And run your app again, it should be working just as on iOS 8.1 and previous versions.