In iOS 8.2, Sqlite3 query becomes very slow

Question

I created an application that works with the sqlite database inside the application. Prior to iOS 8.2, it worked fine, but after updating, the request method works about 100 (!!!) times slower. I tried to find information about this problem, but so far I have not found anything. Does anyone have the same experience? Here is my method that worked perfectly so far. Do you see any errors or optimization opportunities in 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 = %fs", executionTime); return retArray; 

}

Answer 1

A few things have changed:

• In iOS 8.2, sqlite has been upgraded from 3.7.13 to 3.8.5 *
• In sqlite 3.8.0, the query planner has been replaced by the next generation query planner

The combination of these two changes is probably the cause of your performance problem. Although NGPQ is likely to improve the performance of many complex queries, it will have negative consequences for several complex queries, such as yours (and mine!).

To solve your problem, I would consider your specific query to find out if you have any indexes that can improve your performance. Using EXPLAIN QUERY PLAN will most likely give you some idea of ​​what is going on.

• Unfortunately, I can’t find the best source of changes for iOS, other than tweeting and the depth of some posts on the technical forum.

Answer 2

It seems like this is good for your part of the code, I just solve the same problem, the SQLite infrastructure has been updated here and something has changed under the hood. Debugging a query (EXPLAIN QUERY PLAN) I saw that my query is not using my indexes ... just make sure.

Answer 3

I have the same problem, only for some queries.

in the old query, in the FROM internal join table (table derivation) ...

I changed the order in the inner join of the FROM table (table derivation) ...

I know this is strange, but the request is quick

Answer 4

I may be late for this, but FWIW: I had a very similar problem in a complex query created at runtime that I could not handle. It works very fast on all devices and all versions of iOS, except when running under iOS 8.2. Needless to say, our customers complained about this for several days.

Today I found a simple solution that seems to work. DO NOT bind to either sqlite3.dylib or sqlite3.0.dylib . Instead, download the source code of the SQLite alliance, which consists of a C file and a header, so this is just a quick drag'n'drop to your project. Then replace all your

 #import <sqlite3.h> 

by

 #import "sqlite3.h" 

And run the application again, it should work just like on iOS 8.1 and previous versions.