How to optimize sqlite on iphone (load 100000 rows)?

Question

I have big problem. I have 100000 rows in my database(sqlite). I can't reduce this number now.

I' m using following code to get dataa from this large table: wpis

-(NSMutableArray *) return_hours_for_near_two: (int) lacze : (int) hour :(int) okres {
    NSString * query = [ NSString stringWithFormat:@"SELECT godzina, minuta FROM wpis WHERE liniaprzystanekid=%i AND okres=%i AND (godzina = %i OR godzina = %i)", lacze, okres, hour,hour+1];
    NSLog(@"%@", query);
    const char * querystring = [ query UTF8String];
    NSMutableArray * temp = [ [NSMutableArray alloc] init];
    sqlite3_stmt * statment;
    if(sqlite3_prepare_v2(database, querystring, -1, &statment, nil) == SQLITE_OK){
        while(sqlite3_step(statment) == SQLITE_ROW){
            NSMutableArray * arr = [[[NSMutableArray alloc] init] autorelease];
            [arr addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 0)]];
            [arr addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 1)]];
            [temp addObject:arr];
        }
    } else {
        return NULL;
    }

    sqlite3_finalize(statment);
    return temp;
}

EDIT: sorry for mixing languages in database :)

I know I could use CoreData, but now it is too late. What I can do to optimize this query? Because I must call it for every table cell and it's very, very slow. Is there any solution?

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

1

Return the object instead of returning NSMutableArray It will process fast as compare to your NSMutableArray for 1000 lines.

You can also check this link:- What are the performance characteristics of sqlite with very large database files?

edited May 23 '17 at 12:15

Community

1
1

answered Dec 06 '11 at 09:49

Anil Kothari

7,653
4
20
25

I wonder why it run so slow. I have 100000 rows and i returning only max. 50 for each table row(that have max. 20 rows). My database file have only 2.5 MB. Unfortunately returning the object didn' t improve performance. – user968991 Dec 06 '11 at 10:05
Your processing time is due to first fetching the data from database and second is loading the data in the tableView. I have deal with the same problem by making a new thread for fetching data from table and perform UILoading in the mainThread. – Anil Kothari Dec 06 '11 at 10:14
Thanks a lot. I use this: http://www.icodeblog.com/2010/03/04/iphone-coding-turbo-charging-your-apps-with-nsoperation/ and it is quite good(not perfect, but enough). – user968991 Dec 06 '11 at 11:21

How to optimize sqlite on iphone (load 100000 rows)?

1 Answers1