1

I have a requirement in my application wherein I have to every now and then perform insert and update queries on the database. Now, my question is shall maintain cache within the application and after specific intervals or may be after say 20 entries, commit the cache into the database?

If this is the scenario where shall I maintain the cache. Should it be on the application level or in the service?

There is a possibility that say the application abruptly crashes and hence the data that is there in the cache no longer persists.

What can be the possible scenarios for this.

I have read somewhere that everytime opening and closing the database is an overhead. Note: my database resides in the sdcard.

Thanks.

android developer
  • 1,253
  • 2
  • 13
  • 43

2 Answers2

1

I have a requirement in my application wherein I have to every now and then perform insert and update queries on the database. Now, my question is shall maintain cache within the application and after specific intervals or may be after say 20 entries, commit the cache into the database?

Insert operation is fairly fast in SQLite. Make sure you are closing the database in finally block to avoid db in opened state. That can block further inserts. As much as possible, use batch inserts, they are much faster. See here

If this is the scenario where shall I maintain the cache. Should it be on the application level or in the service?

Service also comes at the application level :) . There is a possibility of maintaining the cache in a service and making the service live in a separate process. See here.

There is a possibility that say the application abruptly crashes and hence the data that is there in the cache no longer persists.

The only option that stays is, either maintain the cache in other process(which can again crash anytime) or maintain it on disk, may be internal storage. Maintaining a cache on internal storage is as good or bad as writing to sqlite.

What can be the possible scenarios for this.

You implement cache or you dont.

I have read somewhere that everytime opening and closing the database is an overhead. Note: my database resides in the sdcard.

AFAIK, Opening an SQLite is comparable to opening a file(a little slower though) and not comparable to opening an Oracle database. So most of the times it is affordable to open the database, but batching inserts to improve performance will never hurt ;)

Community
  • 1
  • 1
Aman Gautam
  • 3,549
  • 2
  • 21
  • 25
  • Thanks for the reply. My concern is only with whether is it affordable to open/close database every time or to avoid it, maintain a cache – android developer Oct 31 '13 at 07:46
  • Yes. SQLite table is just a binary file. :) Opening a sqlite db is "almost" as fast as opening a file for caching. So you won't get much out of the cache you may end up making. Just make sure you close the db and all the cursors, SQLite can handle a lot :) – Aman Gautam Oct 31 '13 at 08:09
0

Remember that in Android, your application may be killed at any time. So if you want to make sure that all your data is saved, you should store it into the database every time some data is entered and not cache it.

There is an overhead in accessing the database and for that reason make sure you don't update the database on the UI thread. If you do it correctly, performance should not deteriorate for the user.

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • So what do you suggest shall I spawn a thread say for eg, a bound service or so.. which keeps the cache and the database inact – android developer Oct 31 '13 at 05:39
  • You could. But if you don't write heaps of data, doing it on a separate thread every time won't be a problem. – Szymon Oct 31 '13 at 05:57