SQLite query takes too much CPU

Question

In my app I'm using these two methods:

- (NSString *)Method1:(NSString *)identificativo :(int)idUtente {
fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *dbase;
NSString *ora;
NSString *database = [self.GetDocumentDirectory stringByAppendingPathComponent:@"db.sqlite"];
sqlite3_open([database UTF8String], &dbase);
NSString *query = [NSString stringWithFormat:@"select MAX(orario) from orari where flag=0 and nome=\"%@\" and idutente=%d group by orario", identificativo, idUtente];
const char *sql = [query UTF8String];
sqlite3_prepare_v2(dbase, sql, -1, &stmt, NULL);
while(sqlite3_step(stmt) == SQLITE_ROW) {
    NSString *orario = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 0)];
    NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
    [formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    NSDate *dataV = [formatter dateFromString:orario];
    ora = [formatter stringFromDate: dataV];
}
sqlite3_finalize(stmt);
sqlite3_close(dbase);
return ora;
}

Second one:

- (int)Method2:(NSString *)nomeM :(int)idUtente {
__block int conteggio = 0;
dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
dispatch_async(queue, ^(void) {
    fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *dbase;
    NSString *database = [self.GetDocumentDirectory stringByAppendingPathComponent:@"db.sqlite"];
    sqlite3_open([database UTF8String], &dbase);
    NSString *query = [NSString stringWithFormat:@"select nome, count(*) from orari where datetime(orario)>datetime('now','localtime') and flag=0 and nome=\"%@\" and idutente=%d group by nome", nomeM, idUtente];
    const char *sql = [query UTF8String];
    sqlite3_prepare_v2(dbase, sql, -1, &stmt, NULL);
    while(sqlite3_step(stmt) == SQLITE_ROW) {
    conteggio = [[NSNumber numberWithInt:(int)sqlite3_column_int(stmt, 1)] intValue];
    }
sqlite3_finalize(stmt);
sqlite3_close(dbase);
});
return conteggio;
}

Both these methods, when executed send the simulator CPU speed to 100% and block the UI. In the second one I've tried to use another thread, but it's the same. The table from which they are reading contains something like 7000 records, so it may depend on the poor optimization of the queries, or it might be something else. I've got no clue.

EDIT: this is the table schema:

dataid -> integer -> Primary key
orario -> datetime
nome -> varchar (150)
flag -> integer
pos -> varchar (150)
idutente -> integer

Where should I use the indexes and what kind?

Another thing: watching the table schema right now, I've noticed that there's an error: the column "nome" should be a varchar (and in effect it contains a string) but in my schema is of type integer. I don't know if this is related to my problem and how could an integer column store a text string...

The first query does not make sense; with `GROUP BY orario`, there is only one distinct `orario` value per group, so why do you run `MAX` on it? — CL., Oct 15 '13 at 12:38
@CL. I need the higher value of "orario" and I don't have any index. — Aleph72, Oct 15 '13 at 13:14
There is no higher value of "orario"; each group has only one value. — CL., Oct 15 '13 at 14:31
Yes @CL., the query was incorrect. The new one is: select MAX(orario) from orari where flag=0 and idutente=1 group by nome — Aleph72, Oct 15 '13 at 15:06
Put an index over table orari, on either nome or idutente, whichever has more diverse values. And fix up that NSDateFormater thing -- move creation/setup of the date formatter out of the loop. And eventually you should learn how to do "prepared" statements, but that can be a later lesson. — Hot Licks, Oct 15 '13 at 16:40
I have a lot of things to learn, @HotLicks. If you answer with the same things as your comment, I would be glad to accept the answer. — Aleph72, Oct 15 '13 at 17:01
You can check if SQLite has decided to use an index with [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html). — CL., Oct 15 '13 at 20:49

score 1 · Accepted Answer · edited May 23 '17 at 12:21

These lines are a big problem:

NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
[formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

they can take 0.2+ seconds. It is a crazy expensive call. You really need to have just one NSDateFormatter that you set up just once and have them all use it. Either set it up outside of the loop, or even better, make it static and keep it around for multiple calls.

Similarly in this:

NSString *query = [NSString stringWithFormat:@"select nome, count(*) from orari where datetime(orario)>datetime('now','localtime') and flag=0 and nome=\"%@\" and idutente=%d group by nome", nomeM, idUtente];

If you are able to modify your database, this solution might help your speed.

You are doing the same calculations over and over for datetime('now', 'localtime'). There are much faster ways of storing and comparing times in your database.

SQLite query takes too much CPU

1 Answers1