I' m using sqlite on iphone. I have table with 200 000 records. It' s size is small, only 2 MB. I' m doing standard select(3 collumns) with two simple condtions. I' m operating only on integer values( in return and condtion). All data are unique. I'm get usually only 10-50 records from database.
In spite of that my query take 3 sec. It' s a lot of time. How i can improve this ?
Edit: I can't load my table with other thread.
NSString * query = [ NSString stringWithFormat:@"SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks FROM rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2 ORDER by rRozklad.hour, rRozklad.minute", okres];
NSLog(@"%@", query);
const char * querystring = [ query UTF8String ];
sqlite3_stmt * statment;
if(sqlite3_prepare_v2(database, querystring, -1, &statment, nil) == SQLITE_OK){
while(sqlite3_step(statment) == SQLITE_ROW){
NSMutableArray * temp = [[NSMutableArray alloc] initWithCapacity:2];
[temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 0)]];
[temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 1)] ];
[temp addObject:[ NSString stringWithUTF8String: (const char *)sqlite3_column_text(statment, 2)]];
[wyniki addObject:temp];
[temp release];
}
}
It looks like your SQL is missing the table name and the WHERE keyword.
ReplyDeleteSELECT rRozklad.hour, rRozklad.minute, rRozklad.marks
FROM rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2
ORDER by rRozklad.hour, rRozklad.minute
Do you have your columns indexed? Especially idOkres, idPolaczeniaLinie, hour, and minute?