4

I have about 4k rows in sqlite table, table has 7 columns.

I created working ListView with my own CursorAdapter.

Query is like this SELECT * FROM [table] ORDER BY [column] DESC;

Table has first column _id INTEGER PRIMARY KEY but ordering is done by another column.

For opening db using my own subclass of SQLiteOpenHelper

Creating cursor

mySQLiteOpenHelper pm = new mySQLiteOpenHelper();
SQLiteDatabase db = pm.getReadableDatabase();
Cursor c = db.query([tablename], new String[]{"_id", "[column]", "[column]", "[column]", "[column]", "[column]"}, null, null, null, null, "name ASC");

Passing it to ListView

ListView lv = (ListView) findViewById(R.id.list_items);
lv.setOnItemClickListener(this);
pa = new ItemsAdapter(ItemsActivity.this, c);

In ItemsAdapter I have reimplemented

private LayoutInflater inflater;

@Override
public View newView(Context arg0, Cursor arg1, ViewGroup arg2) {
    return inflater.inflate(R.layout.items_row, arg2,false);
}

and 

@Override
public void bindView(View rtn, Context arg1, Cursor c) {
    item_name = (TextView) rtn.findViewById(R.id.item_name);
    item_description = (TextView) rtn.findViewById(R.id.item_description);
    item_catalog_id = (TextView) rtn.findViewById(R.id.item_catalog_id);
    item_true_price = (TextView) rtn.findViewById(R.id.item_true_price);
    item_display_price = (TextView) rtn.findViewById(R.id.item_display_price);
    item_button = (Button) rtn.findViewById(R.id.item_button);

    item = new MyWrapClass(c);

    // some work with item to fill up all UI items

}

MyWrapClass

public final class MyWrapClass {

    public String name = "";
    public String notes = "";
    public int id = 0;
    public String catalogId = "";
    public int price = 0;
    public int publicPrice = 0;
    public String groupPrice = "";
    public int order_count = 0;

    public MyWrapClass(Cursor c) {
        try {
            id = c.getInt(0);
            catalogId = c.getString(1);
            name = c.getString(2);
            price = c.getInt(3);
            publicPrice = c.getInt(4);
            groupPrice = c.getString(5);
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }
}

The same row init code was used in ListView and there it worked very good.

So if you can say from this code, is there ANY reason, why should load of 6 row items (one screen height) and scroll refresh (mean when you scroll one item down) take up to 1 minute?

Just load of ListView takes up to 2 minutes, and then about half time to scroll one list item down/up. Where can be the performance issue?

Marek Sebera
  • 39,650
  • 37
  • 158
  • 244

1 Answers1

2

I'd create a custom Adapter, that only loads whatever is needed for the active views and that reuses views in the getView() method. It's really quite simple.

Update

I found an excellent example, that you should be able to use: http://android.amberfog.com/?p=296

Michell Bak
  • 13,182
  • 11
  • 64
  • 121
  • for active views? you mean load only data for rows +- on screen position? how can I achieve this? – Marek Sebera Oct 04 '11 at 16:44
  • Yes, exactly. Simply reuse the views you've already created and populate them with only the data for the current list view position. Android engineer Romain Guy has an excellent video on it. It starts at around 12 minutes: http://www.youtube.com/watch?v=UApv-ZMJ51g – Michell Bak Oct 04 '11 at 16:48
  • can you please add to your answer what code will be then in `getView`,`newView` and `bindView` ? I'm going to watch, but make your answer complex, so I can accept it. Because `getView` is not forced to be override, but `bindView` is – Marek Sebera Oct 04 '11 at 16:52
  • Yes, I'll add a more detailed answer in a bit. You don't need `newView()` and `bindView()`. – Michell Bak Oct 04 '11 at 16:54
  • Please see the updated answer. I've added a link to an excellent tutorial. If you go through the Cursor and add the elements to ArrayLists, you should easily be able to use the example in the link. – Michell Bak Oct 04 '11 at 17:01
  • Why on earth was this downvoted? It's the correct solution, just like Mighter's will work just fine. – Michell Bak Oct 04 '11 at 17:17
  • Because you're suggesting a solution that is already implemented. Please look at how newView and bindView works. In fact.. go debug it. See how often newView gets called. It gets called the first few times and then never again. Recycling is already implemented using newView and bindView. You can implement recycling on your own if you want but Android already doing it. See here: http://stackoverflow.com/questions/3535074/getview-vs-bindview-in-a-custom-cursoradapter You solution is doing nothing different. that is why I downvoted. – dymmeh Oct 04 '11 at 17:24
  • Additionally, as I said to Mighters answer, why would you convert 4k cursor results into an array? That's a huge waste. – dymmeh Oct 04 '11 at 17:26
  • Might be, but it's considerably faster than anything else, and that's what the question was about: Speed. Nothing else. Zip. Nada. The solution Mighter and I have proposed is the best. Adding them to an array and reusing views in getView() is the best way. – Michell Bak Oct 04 '11 at 17:27
  • I can tell you now that your answers are not right. They may work. But they aren't the fastest. How is looping through an entire cursor and creating a massive array faster than directly using the cursor? And since I mentioned that getView is "automagically" called while using CursorAdapter with newView and bindView you're just wasting development time on something that's already implemented. (Plus you're reinforcing someone else to waste their time and use it) – dymmeh Oct 04 '11 at 18:10
  • But if @Marek Sebera doesn't mind waiting for the cursor to loop through and create 4k objects on initial start up, it will be faster once that is done. The problem is, most users will not be happy about this. There should be a way to implement a partial cursor that only gets a small subset of your full results. I'd imagine doing something like this would be far more effective than looping through your entire cursor every time you want to work with it. – dymmeh Oct 04 '11 at 18:15
  • @dymmeh can you please write your suggestion as an answer (example how to use cursor within CursorAdapter in combination with ViewHolder and other technics mentioned in that '09 GDD talk) ? – Marek Sebera Oct 04 '11 at 20:26
  • @dymmeh The thing is that Mark Sebera DOES mind waiting - hence the part about "performance issues" in the title of this question. I fully agree with you that there should be a way to get cursor results more effectively, but right now my solution is better, and that's what Android engineers recommend as well. Marek Sebera even states that it takes "about half time to scroll one list item down/up" using a CursorAdapter. This would not happen using my solution. – Michell Bak Oct 04 '11 at 20:45
  • If he does mind waiting then he will mind your solution (which was my point). 4k objects is going to take a while to create along with the memory wasted on objects that dont need to be created. He can do this on a background thread to avoid initial UI lag. Can you show me where "Android engineers" have recommended doing this (and if you point me to getView again I'm not even going to bother responding). His performance issues are caused by getCount being called on the adapter. (See here: http://stackoverflow.com/questions/4330565/using-cursor-with-listview-adapter-for-a-large-amount-of-data) – dymmeh Oct 05 '11 at 13:02
  • @MarekSebera, If you check out the link I posted above there are a few proposed solutions. I've never had the need to do them so I can't give you a good answer. If you want do to a "quick and dirty" fix that won't require much reworking you can convert your cursor to a List as stated above. This will cause UI lag if you do not put this in a background thread. I'd suggest looking into doing queries that only retrieve parts of the full result in order to get the best performance out of your app. – dymmeh Oct 05 '11 at 13:09
  • Look into LIMIT and OFFSET for sqlite. They will help you get a subset of your full query – dymmeh Oct 05 '11 at 13:13
  • LIMIT / OFFSET won't help, SQLite implements this as "retrieve everything, discard results up to OFFSET, return LIMIT records" – Rafael Nobre Jul 05 '12 at 18:19