Best SQLite practices on the iPhone

Question

What are some best practices to keep in mind when working extensively with SQLite on the iPhone? Tips/tricks/convenience factors all appreciated.

Answer 1

I can recommend using FMDB as a nice Cocoa SQLite wrapper.

Answer 2

Off the top of my head:

• Use Transactions.
• Make sure your SQL leverages tables in the correct order.
• Don't add indexes you're not entirely sure you need.

Perhaps not only specific to iPhone but to embedded devices there are some great tips here.

This link pertains to an older version of SQLite but still proves useful.

Lastly this Stack Question also has some good info.

We use SQLite with a .Net Compact Framework Application currently and it's performance is fantastic and we've spent a bit of time optimizing but not nearly as much as we could.

Best of luck.

Answer 3

Measure your app's memory footprint and look for leaks in Instruments. Then try it after invoking sqlite3_exec with:

• pragma cache_size=1

and/or

• pragma synchronous=0

YMMV. There are reports of performance boosts, large reductions in RAM usage, and fewer leaks. However, be careful about making adjustments without understanding the impact (for example, synchronous turns off flushing which speeds things up by a lot, but can cause DB corruption if the phone is power-cycled at the wrong time).

More here: http://www.sqlite.org/pragma.html

Answer 4

I've found that it's often faster to just get the ID's I'm looking for in a complex query and then get the rest of the information on demand.

So for example:

SELECT person_id
  FROM persons
 WHERE (complex where clause)

and then as each person is being displayed I'll run

SELECT first_name, last_name, birth_date, ...
  FROM persons
 WHERE person_id = @person_id

I typically find this makes the complex query run in 1/2 the time and the lookups for a given person are typically on the order of 2ms (this is on tables with 17k rows).

Your experience may vary and you should time things yourself.

Also, I have to give credit to Wil Shipley for suggesting this technique in his talk here: http://www.vimeo.com/4421498.

I actually use the hydration/dehydration pattern extensively from the sqlitebooks which is a superset of this technique.

Answer 5

Hi,

I am lazy and like to stick in the core code as much as possible, hence I like the ORM tool SQLitePersistentObjects:

http://code.google.com/p/sqlitepersistentobjects/

You make your domain model objects inherit from SQLitePersistentObject (ok a little intrusive) and then you can persist/retrieve your objects as needed.

To persist:

[person save];

Loading it back in is almost as easy. Any persistable object gets dynamic class methods added to it to allow you to search. So, we could retrieve all the Person objects that had a last name of "Smith" like so:

NSArray *people = [PersistablePerson findByLastName:@"Smith"];

Answer 6

One other option I have not tried yet is Core Data (need to be an Apple iphone dev), although its a 3.0 feature and so it depends on your app whether thats an option..

Answer 7

PLDatabase is an FMDB alternative: http://code.google.com/p/pldatabase/

I've used it in one of my projects without issue.