A friend and I have been working on a small iPhone app, and we have been using an sql database for our data store. There had been large lags in our system for loading/saving (which we have now made lazy), but the majority of this was due to encoding images as PNGs/JPEGs. The storing into BLOBs didn’t appear to be all that slow, so I am fairly confident that sqlite3 was not to blame.
However, in our initial UITableView a number of objects are loaded from the database, and contain thumbnails (64×64 ish), and some meta-data collected from a simple join on a couple tables. This join collected information from children objects (such as size of blobs), and number of children objects. Surprisingly, with only 8 objects in the database, this loading step sometimes took longer than 2 seconds (including DB setup time). This timing seemed to scale roughly linear with the number of objects in the DB.
| Num Objects | Using join | Cached stats |
| 3 | 0.95 | 0.53 |
| 5 | 1.12 | 0.65 |
| 7 | 1.33 | 0.81 |
| 8 | 1.44 | 0.84 |
So each entry adds roughly 0.11s, and initialization takes roughly 0.4-0.5s. We get a slight improvement with caching the sizes (again I find this odd, as there are so few entries in the DB). Taking out the processing for the thumbnails (decoding), the times for 3 entries drop from 0.95 to 0.55 and 0.53 to 0.16 (for cached). So loading 4 thumbnails takes roughly 0.4/3s, approx 0.13s. This still seems pretty slow, but the difference in 0.55 and 0.16 seems to suggest that the originial query is that much slower.
I still think that this loading should be faster, but we may have to look into other ways to load the header info if there are many entries.
