CODEWITHSUNDEEP
iphonesqlite
karim
karim

Reputation: 15589

iPhone quick sqlite query

I am accessing nearly 30,000 words and showing in a table. The words are in a sqlite file. So it takes long time (15 seconds) to run the query and load the word in a table. Apparently the app wait 15 seconds in startup.

How can I get around this ? 

NSLog(@"Query start");


   CFTimeInterval startTime = CFAbsoluteTimeGetCurrent(); 

 FMResultSet *rs = [db executeQuery:@"select * from  words order by no ASC "];
 while ([rs next]) {
     Word *word = [[Word alloc] initWithID:[rs intForColumn:@"id"]
                                        no:[rs stringForColumn:@"no"]
                                        en:[rs stringForColumn:@"en"]];
     [words addObject: word];
 }
 [rs close];  
 [db close]; 

 CFTimeInterval endTime = CFAbsoluteTimeGetCurrent(); 
 float deltaTimeInSeconds = endTime - startTime;
 NSLog(@"Time spent for query: %f seconds", deltaTimeInSeconds);

NOTE: The original table is not sorted. It is a list of word for dictionary, so when to show, it must alphabetically sorted in the table view. that is why, I need to load all the words. A background thread may be a better idea, populate the data when fetching from sqlite. Does sqlite support this? Fetching data with a call back method. e.g. in the sqlite query, for each 1000 of records fetched, I'll update my table. I am not quite sure how to do that, if you know plz give me some tips.

Upvotes: 1

Views: 772

Answers (3)

Marcelo Cantos
Marcelo Cantos

Reputation: 185842

Use LIMIT and OFFSET to fetch chunks of the data at a time. Also, let the UITableView drive the fetching process. When it fires tableView:cellForRowAtIndexPath: and the row in question isn't available, fetch and cache a block of rows containing that row from SQLite. You could also have a background thread or an event loop fetching blocks in the background to minimise lag as the user scrolls the table.

Upvotes: 3

David Gelhar
David Gelhar

Reputation: 27900

You might also want to consider switching to Core Data and a NSFetchedResultsController instead of using Sqlite directly.

One of the big benefits of Core Data is what it gives you in terms of caching and lazy loading of objects.

See Core Data vs Sqlite and performance

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

I would suggest two things:

First: Do you need to load all 30000 objects in memory at the same time? If you don't need it, you can just load the objects you are going to show in table as long as you need them. You could use limit clause on your queries to load just small portions of the data you need for display.

Second: You can try and see if creating an index on no field helps. Although it's not likely to improve your performance a lot since a big portion of the time, iOS will spend creating Word objects and putting them in words array.

Upvotes: 1

Related Questions