Iphone SQLite initializing and closing

Question

I'm new to working with iphone and SQLite i have my app working fine with SQLite but i would like to know what you all think is the correct / best way of opening an sqlite connection and closing it..

Would you

1. Initialise a DB connection on app load and close on app close..
2. Open and close connections when is needed..

Also is it good practise to constantly update db entries as you go or store all information in an object and write out at the end when needed..

There is not to much information on this via google just some useful small tutorials that cover delete / insert / update but not as a general overview of the best practise in using SQLite with iphone..

Thanks guys

Answer 1

Keep the connection open.

Whenever you execute a SQL query, it has to be compiled into a prepared statement. Once it's compiled, you can use it over and over and it doesn't have to be compiled again.

However, the query is compiled against the database pointer, so if you close your connection, you have to release all of your prepared statements. When you re-open your connection, you then have to re-prepare each statement as you use it again.

Save an object whenever you make a set of changes. If you are changing several things on an object (name, address, etc.) make all the changes first, then save it. Don't wait to commit several unrelated changes all at once, though (i.e. at the termination of the app). Related changes can be delayed until they are all made, before saving them.