Why does the access to the SQLite database takes longer on iOS6?

Question

On my iPhone app I'm using a SQLite database, and for that I'm relying on fmdb. On iPhones with iOS 5, that initial DB access takes, at max, 10s, but on iOS 6 it takes between 30s and 2m40s (this on iPhone 4 and 4S, on iPhone 5 it takes around 15-20s). OIne thing that I noticed is that the times are always consistent, if on one phone it takes 60s, it will always take ~60s.

Is there any reason for the use of fmdb in iOS 6 to be slower than on iOS 5? Is there a better alternative to fmdb?

In terms of code, here's a sample of what I'm doing:

Getting the data

+ (NSMutableArray*) getLevadas
{
    FMDatabase *db = [DBAdapter getDB];
    if([db open])
    {
        FMResultSet *result = [db executeQuery:@"SELECT Levada.Levada_id, Levada.Nome, a.Nome as Inicio, b.Nome as Fim, Dificuldade.NomePT as DificuldadePT, Dificuldade.NomeES as DificuldadeES, Dificuldade.NomeEN as DificuldadeEN, Altitude, Distancia, DescricaoPT, DescricaoEN, DescricaoES, Latitude, Longitude, Autocarro, Levada.dataModificado, a.Sector, Levada.Duracao, b.Sector as SectorFim, GROUP_CONCAT(DISTINCT PalavrasChave.NomePT) as PalavrasChavePT, GROUP_CONCAT(DISTINCT PalavrasChave.NomeEN) as PalavrasChaveEN, GROUP_CONCAT(DISTINCT PalavrasChave.NomeES) as PalavrasChaveES, GROUP_CONCAT(DISTINCT Equipamento.NomeEN) as EquipamentoEN, GROUP_CONCAT(DISTINCT Equipamento.NomePT) as EquipamentoPT, GROUP_CONCAT(DISTINCT Equipamento.NomeES) as EquipamentoES, Levada.Coordenadas, Levada.LongitudeFim, Levada.LatitudeFim, Levada.PontosInteresse, GROUP_CONCAT(DISTINCT Fotos.url) as Fotos FROM Levada, Localizacoes as a, Localizacoes as b, Dificuldade, Levada_has_PalavrasChave, PalavrasChave, Levada_has_Equipamento, Equipamento, Fotos WHERE ((Inicio_id = a.Localizacoes_id) AND (Fim_id = b.Localizacoes_id) AND (Levada.Dificuldade_id = Dificuldade.Dificuldade_id) AND (Levada.Levada_id = Levada_has_PalavrasChave.Levada_id) AND (PalavrasChave.PalavrasChave_id = Levada_has_PalavrasChave.PalavrasChave_id) AND (Levada.Levada_id = Levada_has_Equipamento.Levada_id) AND (Levada_has_Equipamento.Equipamento_id = Equipamento.Equipamento_id) AND Levada.Levada_id = Fotos.idLevada) GROUP BY Levada.Levada_id ORDER BY Levada.Nome;"];

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

        while ([result next])
        {
            Levada *l = [[Levada alloc] init];
            [l ID:[NSNumber numberWithInt: [result intForColumn:@"Levada_id"]]];
            [l Nome:[result stringForColumn:@"Nome"]];
            [l Dificuldade:[result stringForColumn:[NSString stringWithFormat:@"Dificuldade%@", NSLocalizedString(@"lingua", NULL)]]];
            [l Distancia:[result objectForColumnName:@"Distancia"]];
            [l Duracao:[result stringForColumn:@"Duracao"]];
            [l Inicio:[result stringForColumn:@"Inicio"]];
            [l Fim:[result stringForColumn:@"Fim"]];
            [l Altitude:[result stringForColumn:@"Altitude"]];
            [l PalavrasChave:[[result stringForColumn:[NSString stringWithFormat:@"PalavrasChave%@", NSLocalizedString(@"lingua", NULL)]] stringByReplacingOccurrencesOfString:@"," withString:@", "]];
            [l Descricao:[result stringForColumn:[NSString stringWithFormat:@"Descricao%@", NSLocalizedString(@"lingua", NULL)]]];
            [l Fotos: [[result stringForColumn:@"Fotos"] componentsSeparatedByString:@","]];
            [l Latitude:[NSNumber numberWithDouble:[[result stringForColumn:@"Latitude"] doubleValue]]];
            [l LatitudeFim:[NSNumber numberWithDouble:[[result stringForColumn:@"LatitudeFim"] doubleValue]]];
            [l Longitude:[NSNumber numberWithDouble:[[result stringForColumn:@"Longitude"] doubleValue]]];
            [l LongitudeFim:[NSNumber numberWithDouble:[[result stringForColumn:@"LongitudeFim"] doubleValue]]];
            [l Coordenadas:[result stringForColumn:@"Coordenadas"]];
            [l Equipamento: [[result stringForColumn:[NSString stringWithFormat:@"Equipamento%@", NSLocalizedString(@"lingua", NULL)]] componentsSeparatedByString:@","]];
            [l DataModificado:[result stringForColumn:@"dataModificado"]];
            [l Sector: [result stringForColumn:@"Sector"]];
            [l SectorFim: [result stringForColumn:@"SectorFim"]];
            [l Autocarro: [result stringForColumn:@"Autocarro"]];
            [levadas addObject:l];
        }
        [result close];
        [db close];
        return levadas;
    }
    return nil;
}

Opening the DB

+ (FMDatabase*) getDB
{
    [DBAdapter copyDatabaseIfNeeded];
    FMDatabase *db = [FMDatabase databaseWithPath:[DBAdapter getDBPath]];
    [db setLogsErrors:YES];
    return db;
}
+ (void) copyDatabaseIfNeeded
{
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSString *dbPath = [DBAdapter getDBPath];
    BOOL success = [fileManager fileExistsAtPath:dbPath];

    if(!success)
    {
        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"WalkMe.sqlite"];
        success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
    }
}
+ (NSString *) getDBPath
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"WalkMe.sqlite"];
}

UPDATE:

Here's a screenshot of the time profiling:

From what I can gather the most time consuming is the [FMResultSet next] instruction, more specifically on rc = sqlite3_step([_statement statement]); inside the FMDB next code. So I'm guessing the problem really is the SELECT statement.

UPDATE 2:

I tried to simplify the SQL Statement to SELECT Levada_id FROM Levada and it still takes too much time in the [FMResultSet next] Always clean your project.

Answer 1

Having spent some time going through FMDB code in detail, there's nothing there which would account for those sorts of delays. FMDB is a pretty thin wrapper, and almost certainly is not accountable for the behavior. There are also enough SQLite/FMDB users out there, I'll be very surprised if there's actually a SQLite/FMDB related problem in iOS 6. I would have thought that we would have heard a lot about it by now if there was.

But glancing at your SELECT statement, that appears to be pretty complex and if I were to wager money, if the problem is actually database related, I bet that's the source of the delay. This would be easy to verify if you single-step through your code. Or put in NSLog statements before and after and you'll get timestamped log statements, which illuminate the problem.

Bottom line, assuming the problem is actually database related, it's almost certainly a SQLite issue. If you rewrite this function to do everything using sqlite3_xxx() calls, you'll probably see the same performance issues. Having said that, I haven't seen any performance issues with FMDB/SQLite with the shift to iOS6. I also would have thought that we would have heard blood curdling howls if there was some widespread performance problem with SQLite/FMDB on iOS 6.

I'd focus on putting that sql statement through SQLite's EXPLAIN, and then optimize the tables, making sure you have the appropriate indexes or otherwise restructuring the SQL. Just open the database in SQLite on your computer and try analyzing the query there.

In terms of what could cause this sort of performance issue on iOS 6 vs iOS 5, I'm not familiar with any changes in how it's opening databases.

It will be very interesting to hear if your profiling confirms a problem with SQLite, or rather if it's something completely unrelated to SQLite/FMDB.

---

Update:

I decided to check to see if there were any interesting configuration changes between the SQLite configuration in iOS 5.1.1 and 6.0.1, but nothing jumps out at me. (A different threading model could definitely affect performance, but they look the same.) iOS 6 is definitely using a slightly newer version of SQLite, but I doubt that would affect it negatively.

Device: iPhone OS
iOS: 5.1.1
SQLite Configuration:
  Version: 3.7.7
  Threadsafe: Yes
  Options:
    ENABLE_FTS3: Yes
    ENABLE_FTS3_PARENTHESIS: Yes
    ENABLE_LOCKING_STYLE=1: Yes
    ENABLE_RTREE: Yes
    OMIT_AUTORESET: Yes
    OMIT_BUILTIN_TEST: Yes
    OMIT_LOAD_EXTENSION: Yes
    TEMP_STORE=1: Yes
    THREADSAFE=2: Yes

Device: iPhone OS
iOS: 6.0.1
SQLite Configuration:
  Version: 3.7.13
  Threadsafe: Yes
  Options:
    CURDIR: Yes
    ENABLE_FTS3: Yes
    ENABLE_FTS3_PARENTHESIS: Yes
    ENABLE_LOCKING_STYLE=1: Yes
    ENABLE_RTREE: Yes
    OMIT_AUTORESET: Yes
    OMIT_BUILTIN_TEST: Yes
    OMIT_LOAD_EXTENSION: Yes
    TEMP_STORE=1: Yes
    THREADSAFE=2: Yes