iPhone quick sqlite query

Question

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.

• karim: I dont think so. This is a sqlite table of words, used for dictionary created in Java (data input is done in desktop).
• karim: Indexing improve the performance. But not much. It lowered the loading time from 15 seconds to 6/7 seconds.

Answer 1

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.

Answer 2

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.

• Pablo Santa Cruz: Try creating an index on that column then.
• karim: I was also concerned with the Word objects created. I thought using parallel array of string. But that will complicate the sorting and object management.

Answer 3

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

• karim: When using core date, the sqlite fields are given some unfriendly names. And the sqlite DB is created elsewhere using java and other sqlite browser tools. So I need to use sqlite.