iphone - open and close sqlite database every time I use it

Question

I am writing an iPhone app that uses SQLite. I am use to opening and closing my connections every time I use a database. However, I do not know if that is a good practice in the iPhone/SQLite environment. I want to know if I should open the database 1 time or if it is OK to open and close the database each time I use it. Please let me know.

Answer 1

I believe you should keep it open as long as you can, so data is cached in DRAM. Of course, you should also organize your transactions so you commit at logical points in time and maintain transactional integrity.

Answer 2

I would do as Matthew suggested: keep one connection opened for as long as your program is running.

Answer 3

Both answers seem right, but actually it depends from how often you're using it and how large is it. In case DB is large you should set larger page cache, but that leads to larger memory consumption and if access is rare - no reason for holding it up all the time (but if usage also small - large page cache won't help you also).

In case it's small - there is no reason to open/close it each time even with infrequent usage. But in average your resource consumption is higher with regular open/close. So all in all - don't reopen db each time you're using it.