Android: Best practice for keeping data in Memory and Database at same time.

Question

We're designing an Android app that has a lot of data ("customers", "products", "orders"...), and we don't want to query sqlite every time we need some record. We wanna avoid to query database as most as we can, so we decided to keep certain data allways in memory.

Our initial idea is to create 2 simple classes:

1. "MemoryRecord": a class that will contain basically an array of objects (string/int/double/datetime/etc...), that are the data from a table record, and all methods to get those data in/out from this array.

2. "MemoryTable": a class that will contain basically a Map of [Key,MemoryRecord] and all methods to manipulate this Map and insert/update/delete record into/from database.

Those classes will be derived to every kind of table we have in database. Of course that there are other usefull methods not listed above, but they are not important at this point.

So, when starting app, we will load those tables from SQLite database to memory using those classes, and every time we need to change some data, we will change in memory and post it into database right after.

But, we want some help/advice from you. Can you suggest something more simple or efficient to implement such thing? Or maybe some existing classes that already do it for us?

Thanks in advance.

Update:

I understand what you guys are trying to show me, and I thank you for that.

But, let's say we have a table with 2000 records, and I will to list those records. For each one, I have to query other 30 tables (some of them with 1000 records, others with 10 records) to add additional info in the list, and this while it's "flying" (and as you know , we must be very fast at this momment).

Now you'll gonna say: "just build your main query with all those 'joins', and bring all you need in one step. SQLite can be very fast, if your database is well designed, etc...".

OK, but this query will become very complicated and sure, even SQLite be very fast, it will be "too" slow (2 a 4 seconds, as I confirmed, and this isn't a acceptable time for us).

Another complicator is that, depending of user interaction, we need to "re-query" all records, because the tables involved are not the same, and we have to "re-join" with another set of tables.

So, an alternative is bring only the main records (this will never change, no matter what user does or wants) with no join (this is very fast!) and query the others tables every time we want some data. Note that on the table with 10 records only, we will fetch the same records many and many times. In this case, it is a wast of time, because no matter fast sqlite be, it will allways be more expensive to query/cursor/fetch/etc... than just grab the record from a kind of "memory cache". I want to make clear that we don't plan to keep all data in memory allways, just some tables we query very offten.

And we came to the original question: what is the best way to "cache" those records. I really like to focus the discussion on that and not "why do you need to cache data?"

Thanks again.

Answer 1

The problem with a memory cache is of course that you need to keep it in sync with the database. I've found that querying the database is actually quite fast, and you may be pre-optimizing here. I've done a lot of tests on queries with different data sets and they never take more than 10-20 ms.

It all depends on how you're using the data, of course. ListViews are quite well optimized to handle large numbers of rows (I've tested into the 5000 range with no real issues).

If you are going to stay with the memory cache, you may want have the database notify the cache when it's contents change and then you can update the cache. That way anyone can update the database without knowing about the caching. Also, if you build a ContentProvider over your database, you can use the ContentResolver to notify you of changes if you register using registerContentObserver.

Answer 2

The vast majority of the apps on the platform (contacts, Email, Gmail, calendar, etc.) do not do this. Some of these have extremely complicated database schemas with potentially a large amount of data and do not need to do this. What you are proposing to do is going to cause huge pain for you, with no clear gain.

You should first focus on designing your database and schema to be able to do efficient queries. There are two main reasons I can think of for database access to be slow:

• You have really complicated data schemas.
• You have a very large amount of data.

If you are going to have a lot of data, you can't afford to keep it all in memory anyway, so this is a dead end. If you have complicated structures, you would benefit in either case with optimizing them to improve performance. In both cases, your database schema is going to be key to good performance.

Actually optimizing the schema can be a bit a of a black art (and I am no expert on it), but some things to look out for are correctly creating indices on rows you will query, designing joins so they will take efficient paths, etc. I am sure there are lots of people who can help you with this area.

You could also try looking at the source of some of the platform's databases to get some ideas of how to design for good performance. For example the Contacts database (especially starting with 2.0) is extremely complicated and has a lot of optimizations to provide good performance on relatively large data and extensible data sets with lots of different kinds of queries.