CODEWITHSUNDEEP
iosperformancesqlite
Aleph72
Aleph72

Reputation: 887

SQLite query takes too much CPU

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

Upvotes: 1

Views: 1929

Answers (1)

HalR
HalR

Reputation: 11073

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.

Upvotes: 1

Related Questions