Android: Extending user's contact book. Performance ContentProvider vs Sqlite vs List in memory

Question

Me and my Android team have a problem. We have an app that present the user's contact book, with extended information.

Current setup

Our app reads the Contacts Provider of the Android OS. Sends this information to our Server that calculates a couple of necessary fields for us. This information is later fetched by our app and we save this information in an SQLite database. What we end up with in our database is two tables. One with all Numbers and all the extra information that the server calculated for us. The other table is one with all Contacts (one contact can have multiple numbers). This Contacts table was created only for performance; we can have a Cursor selecting all rows in this Contacts table in our CursorAdapter when presenting the contact book for the user. Hence, when presenting the contact book to the user, we only need to read from our own SQLite database and only one table (e.g. no JOINs).

The main problem

There is a lot of syncing going on. Since, data is duplicated, we need to check for adds/changes/removes and need to sync all the f-ing time. Moreover, when we now are about to change a particular thing in our presentation layer, we need to change our Contacts table to include this particular information.

Priority for us

1st: Performance when presenting the contact book to the user.

2nd: Code maintainability.

Hence, do not comment "Do not duplicate data--it's the root of all problems". It is more important to us that the user does not have performance issues than if we as developers have to spend some extra time writing good synchronization algorithms.

Solutions?

I don't know why, but I've always thought that a CursorAdapter (reading all rows from one table) is performing much better than an ArrayAdapter with a List of objects (held in memory). Anyone know if this is true? Because one solution which will help us at least half the way is to, on start up, join the Contacts Provider (native contact book) and our extended information, save this in a List in memory and present this with an ArrayAdapter.

Creating your own Content Providers? I know little about creating your own content provider. Anyone tried to create a content provider that extend the information of the native contact book and join these. Maybe with the implementation of this interface: ContactsContract.DataColumnsWithJoins? Anyone tried anything similar? How's the performance when presenting this information in a CursorAdapter?

Please ask for any more information I might have forgot and I will update the question!

Thanks a lot in advance for all helpful tips and solutions!

Reiche

A well designed databse in normal form (e.g., bkent.net/Doc/simple5.htm) does not only help maintaining your code, it will also speed up processing dramatically. That is, if indexes are used wisely. Moreover, you don't have to spend computation time to synchronize your data (since you have to lock the database for such operations to avaid inconsistent data). It also blows your database up, which you should avoid at any cost in a mobile environment. In my practical work, I cannot find any reason not to use a normalized database design (that does not have doublicated data). – Elinorelinore

Now you are talking generally about databases, and yes I agree. However, as Android uses something they call Cursors. These cursors are the object you get back when you issue a query. But they are not filled with data. You step forward and get one row at a time while the cursor is a direct link into the database--this helps memory consumption. However, they perform very bad when using joins. – Reiche

As far as I know, the cursor buffers the data retrieved from a query. It wouldn't make much sense otherwise. Thus, the join performance should be the join performance of the SQLite DB. – Elinorelinore

I think (but not sure) that you are wrong. For instance, you cannot close a database while having an open cursor to it. – Reiche

Answer 1

I have come to conclusion (working on my app JReader which relies on fast DB operations a lot) that SQLite in Android is pretty quick as in other platforms but there are some Android specific issues. Some advises regarding database performance and the questions you have asked:

• Content providers are mostly useless if you are not planning sharing your data through them. But they provide at least 2 advantages. First you get data change notifications and you cursor gets updated automatically, and the second and important one: CursorLoaders require a Content provider, and if the performance matters to you, I would strongly suggest using them for loading your cursor;
• Accessing Collections is much faster that accessing database, but it is a double work, since you have to persist the data anyway, and DB access is quite fast even for fetching data for a super-fast scrolling list and especially from a single table, it shouldn't be a problem;
• Design your DB properly (with JOINS, indexes, etc) :) BUT DO NOT USE JOIN QUERIES IN CURSOR QUERIES! I have had lots of performance issues with that on multiple Android platforms (including 4.0+), not always though. The way I access joint tables is by simply getting foreign key first and then querying child table.

In my experience, I've had the situations when the DB performance was very poor, but in the end I have always managed to tweak the code and as a result would gain 10-100 fold performance increase. So keep profiling you DB code, and you will definitely achieve the desired performance.

Sike

And I totally agree with Dan's 1st comment, but not the 2nd one – Sike

Your third bullet is exactly what my question is about. How can you design your DB properly (with joins indexes etc) without using joins in the cursor queries? You mean by not using a CursorAdapter and query the DB with a join at start up and keep the result in a List in memory? – Reiche

"The way I access joint tables is by simply getting foreign key first and then querying child table." What do you mean by that? First querying table A with some foreign key X and then querying table B for each X? So that you do size of X + 1 number of queries? Or how do you mean? – Reiche

This one is extremely slow most of the time: SELECT * FROM table_b INNER JOIN .... WHERE table_a.somevalue == 1 This one is always fast: SELECT * FROM table_b WHERE table_b.foreign_key IN (SELECT table_a.id FROM table_a WHERE table_a.somevalue == 1) The same applies to updates, deletes, etc, I use: DELETE ... WHERE id IN (); – Sike

Answer 2

Dan's 2nd comment is true .. Android Uses Cursor Window below the screens to Cache data from the Cursor (approx 1M ) . see Android docs on Cursor Window, Which is how the Cursor Adapter is quicker .

If you do not prefer joining two tables seperately you could consider a CursorJoiner which is faster, This can go to your custom contentProvider where one of the provider is pointing to the Contacts and returns a Cursor , similarly the second one points to your own table and returns a cursor . The cursorjoiner can join both these cursors .

(Though it is a complicated process )

Reprovable

If that is true, why would cursor.getCount() have to go through the whole result set linearly then? I mean if the result set is less than a million then the size should be available without having to count them one by one. – Reiche