Optimization proposals for ListView with data from multiple "tables"

Question

Hi,

I just encountered the following situation. I have an Android app with a scenario which I guess may happen in multiple apps. It's about tagging/labeling/categorizing, call it as you want. I basically have the following relations in the SQLite DB

 --------                 --------------              ---------
|  Tags  |               |  DeviceTags  |            | Devices |
|--------|               |--------------|            |---------|
| ID     | 1 ------ *    | ID           | * ------ 1 | ID      |
| NAME   |               | TAGS_ID      |            | NAME    |
 --------                | DEVICE_ID    |            | ...     |
                          --------------              ---------

Everything is exposed over a ContentProvider I've written. So far everything is fine.

On the UI part, I have an ListActivity showing all of the stored Devices (from the Devices table) and for customizing the UI a bit further, I created custom row items showing a small image in front based on the device type etc.

What I'd like to achieve now is to also show the associated tags on that list for each device. Now here comes my problem. For the simple device list I created a custom ResourceCursorAdapter where I set the according information in the bindView method

@Override
public void bindView(final View view, final Context context, final Cursor cursor) {
  final int objectId = cursor.getInt(cursor.getColumnIndex(Devices._ID));

  TextView deviceName = (TextView) view.findViewById(R.id.deviceName);
  deviceName.setText(...); //set it from the cursor
  ...
  TextView associatedTagsView = (TextView)...;
  associatedTagsView.setText(...); //<<<???? This would need a call to a different table
  ...
}

As you can see, for being able to know what kind of tags my device has associated, I'd need to query DeviceTags. So I did:

@Override
public void bindView(final View view, final Context context, final Cursor cursor) {
   ...
   TextView associatedTagsView = (TextView)view.findViewById(R.id.deviceTags);
   String tagsString = retrieveTagsString(view.getContext().getContentResolver(), objectId);
   ...
}

private String retrieveTagsString(ContentResolver contentResolver, int objectId) {
    Cursor tagForDeviceCursor =  contentResolver.query(DroidSenseProviderMetaData.TABLE_JOINS.TAG_DEVICETAG,...);
    if(tagForDeviceCursor != null && tagForDeviceCursor.moveToFirst()){
        StringBuffer result = new StringBuffer();

        boolean isFirst = true;
        do{
            if(!isFirst)
                result.append(", ");
            else
                isFirst = false;

            result.append(retrieve name from cursor column...);
        }while(tagForDeviceCursor.moveToNext());

        return result.toString();
    }       
    return null;
}

I tested this and it actually works just fine, but to be honest I don't feel well doing this. Somehow seems weird to me...

Are there any better solutions on how to solve this??

//Edit:

After CommonsWare's feedback here a bit of a clarification. I'm weird about doing the second query to the DB inside the CursorAdapter, basically this would result in one query per row and I fear this will heavily impact my performance (I've still to test it on a real device with a substantial amount of data to see how much this impacts).

My question therefore is on whether there are some strategies on how to avoid this given my data model or whether I have to basically "live" with that :)

Answer 1

You complained on Twitter that you weren't getting any feedback. I'm not terribly surprised, because while your question has an exemplary level of detail, it suffers from two major flaws:

1. Your question, at the end, is vague
2. Your question relies on your own interpretation of what "this" is, to which we aren't privy

Hence, I have no clear picture what you think is "weird". do...while() loops? :-)

I am going to go out on a limb and guess that your concern is about doing a second query and round of string concatenation for each row in your ListView. It all depends on whether you think that "a comma-delimited list of tags" is part of the data model or part of the presentation. If it is part of the presentation, I don't see how you can avoid what you're doing here.

If you really think it is part of your data model, adjust your ContentProvider to supply the comma-delimited list of tags as part of whatever you're querying on in the first place (not shown).

In either case, performance is a probable issue. Doing one query per row, particularly at the point in time of scrolling, is likely to be bad. After all, Romain Guy emphasizes repeatedly that you need to recycle rows in your adapter, and I would have to imagine that a database query is a wee bit more expensive than inflating a layout. Unfortunately, you went with a data model that emphasized space over time, and so you're going to have to pay the piper somewhere along the line.

Answer 2

I tested my solution (proposed in my question) with about 100 entries. It was still usable, but scrolling was not that smooth as one would expect. As CommonsWare already pointed out, the best is to adapt the data model or at least to aggregate it properly s.t. in the end the cursor contains all the data needed for being displayed on the UI.

I didn't want to change the underlying datamodel completely. What I did is to aggregate the data in a way, s.t. the cursor returned by the ContentProvider contains the data for the devices including an already prepared string representing the associated labels' names:

| ID | NAME             | ... | LABELSSTRING   |
------------------------------------------------
| 1  | "My Device name" | ... | "Work, Friend" |
| 1  | "Some other"     | ... |                |
| 1  | "Yet another"    | ... | "Work"         |

This pretty much solves the problem, because there is no need to requery in the CursorAdapter's bindView(..) for each device, which btw. is quite bad. This solution was possible by using SQLite's group_concat(X, Y) function.