Improving iPhone performance - loading images from an SQLite3 database

Question

I'm experiencing some performance problems loading about 60 images from a SQLite3 table with the following table:

select rowid, thumbnail
from my_table
where category_id = 4

I have an index on category_id, yet it takes about 2.5 seconds to load a table of 170 70x70 PNG images.

In my code, I create an NSMutableArray of objects representing each row, like this:

    Item* item = [[Item alloc] init];
    item.rowid = sqlite3_column_int( statement, 0 );

    int thumbnailLength = sqlite3_column_bytes( statement, 1 );
    NSData* data = [NSData dataWithBytes:sqlite3_column_blob( statement, 1 ) length:thumbnailLength];
    item.thumbnail = [[UIImage imageWithData:data] autorelease];

    [result addObject: item]

iPhoto can load an album with almost no visible loading time. Is there some SQLite3 trick that I'm missing that can boost performance here?

Answer 1

I'm betting that the Photos application only loads the thumbnail images that are visible on screen at a given time, retrieving the others as you scroll down the list in your album. This is easy to do using Core Data in 3.x, NSFetchedResultsController, and its setFetchBatchSize: method. Manually implementing this using raw SQLite will take a lot more code, but it's still doable. This approach has both loading-time and memory advantages.

In my SQLite data accesses, I didn't notice a significant load time pulling up to 17 150 x 50 PNG images from an SQLite database, so there's the possibility something else is slowing you down. You might also try running Instruments and Shark on your data loading method to see if SQLite really is your bottleneck there. I had a case where I'd assumed it was, but it turned out I had an inefficient string processing routine that was what really slowed down my creation of objects from SQLite data.