How do I Increase the speed of this sqlite db query / object creation in Objective-C?

Question

Hi, I have the following method which queries a sqlite db table on the iPhone it pulls about 6,000 rows from the table and then cycles through those creating 6,000 objects based on the information and stuffing them into an array.

The while loop is somewhat slow (takes ~ 10 seconds to iterate through them on my device) Any thoughts on how I could speed this up?

TKS!!!

NSMutableArray  *dees              = [[NSMutableArray alloc] init];

if(sqlite3_open([database_path UTF8String], &database) == SQLITE_OK) {
  const char      *sqlStatement = "select cn, gn, df, or, id from doits order by lower(cn)";
  sqlite3_stmt    *compiledStatement;
  if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

    //loop through the results and feed them into the array
    while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
        // Read the data from the result row
        NSString    *a_cn             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
        NSString    *a_gn             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
        NSString    *a_df             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
        NSString    *an_or            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
        NSString    *ident            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];

        DL   *dl = [[DL alloc] init];
        dl.cn            = a_cn;
        dl.gn            = a_gn;
        dl.df            = a_df;
        dl.or            = an_or;
        dl.primary_id    = [ident intValue];
        [dees      addObject:dl];
    }
}
sqlite3_finalize(compiledStatement);

Answer 1

Why are you creating all 6K at runtime? How about lazy loading them and only creating the items you need, when you need them?

The iphone is not a desktop PC, its a 400+MHz CPU and iterating on 6K results is going to be slow.

Is your table indexed properly? Could you optimize the schema possibly (field types?)? You're really not doing much, except iterating on 6K elements - so I don't see a direct way to optimize that loop.

I'd look larger picture and attempt to find a way to lazy load or partially load the data. Say your using the data to populate a UITableView, maybe you just need the first layer, not the fully fledged objects?

EDIT:

What I mean by field types is that you have 4x "sqlite3_column_text" text fields in your database. Do you have to use text fields, or could you use something smaller and possibly more optimal for your data?

Answer 2

As Mr-sk says, it's a bad idea to load all 6,000 elements at once, for memory as well as performance reasons. You'll want to batch your fetching so that you're only loading the elements on screen at any given time. For running searches, you'll need to perform those as queries against your database.

If you were to switch your data model to use Core Data, this batched fetching becomes trivial with the use of NSFetchedResultsController and -setFetchBatchSize: on NSFetchRequest. Feeding only the data you need to your table view is exactly what this class was designed to do. For searches, you can supply an NSFetchRequest that filters results based on the search criteria. This will lead to a huge increase in the performance of your application.