Databases
StackOverflow2013
Postsdbo
POBest practice for keeping data in memory and database at same time on Android
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest practice for keeping data in memory and database at same time on Android
    primarykey
    data
    text
    <p>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 want to avoid to query the database as most as we can, so we decided to keep certain data always in memory.</p> <p>Our initial idea is to create two simple classes:</p> <ol> <li><p>"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.</p></li> <li><p>"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.</p></li> </ol> <p>Those classes will be derived to every kind of table we have in the database. Of course there are other useful methods not listed above, but they are not important at this point.</p> <p>So, when starting the app, we will load those tables from an 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 the database right after.</p> <p>But, we want some help/advice from you. Can you suggest something more simple or efficient to implement such a thing? Or maybe some existing classes that already do it for us?</p> <p>I understand what you guys are trying to show me, and I thank you for that.</p> <p>But, let's say we have a table with 2000 records, and I will need 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 information in the list, and this while it's "flying" (and as you know, we must be very fast at this moment).</p> <p>Now you'll be going to 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...".</p> <p>OK, but this query will become very complicated and sure, even though SQLite is very fast, it will be "too" slow (2 a 4 seconds, as I confirmed, and this isn't an acceptable time for us).</p> <p>Another complicator is that, depending on 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.</p> <p>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 other 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 waste of time, because no matter fast SQLite is, it will always be more expensive to query, cursor, fetch, etc... than just grabbing the record from a kind of "memory cache". I want to make clear that we don't plan to keep all data in memory always, just some tables we query very often.</p> <p>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?"</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPeter Mortensen
    1. USChristian
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest practice for keeping data in memory and database at same time on Android
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBest practice for keeping data in memory and database at same time on Android
      1. USthe100rabh
      1. VTFavorite
    3. VO
      singulars
      1. POBest practice for keeping data in memory and database at same time on Android
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO"We wanna avoid to query database as most as we can, so we decided to keep certain data allways in memory." -- have you used Traceview to confirm this is an issue for your app? "we want some help/advice from you" -- my advice is: prove there is a problem first. You have very little RAM to work with. Building some big framework to deal with a non-existent problem would be a waste of effort. If you have demonstrated that this is an issue, I'd love a pointer to the blog post where you wrote it up, as I am always interested in performance test results.
      singulars
      1. POBest practice for keeping data in memory and database at same time on Android
      1. USCommonsWare
    2. CO@CommonsWare: I understand your point and totally agree with you. But, as PalmOS and .NetCF developers, we already face this problem before. In PalmOS all data are in memory by design (.pdb) and there is no performance issue while getting data. In the other hande, in WM we face "the problem", and then we created this "solution" listed above. But now, in Android, we wish to do in the "right way". We want to Know if we are going to face performance problems when quering database "every time". So we decided to ask for suggestions here. Thanks anyway.
      singulars
      1. POBest practice for keeping data in memory and database at same time on Android
      1. USChristian
    3. CO"We want to Know if we are going to face performance problems when quering database "every time". So we decided to ask for suggestions here." -- no, you did not. I wish you had. Instead, you declared a problem existed ("we don't want to query sqlite every time we need some record") and you wanted help with your solution. As with most platforms, the answer to if you are "going to face performance problems when quering database" is "it depends on the queries". Trust me, just because there is a problem on WM does not mean the same problem exists elsewhere. Use Traceview.
      singulars
      1. POBest practice for keeping data in memory and database at same time on Android
      1. USCommonsWare
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload