This last week, I ran into poor performance loading Java objects on Android using SQLiteCursors.  I needed to load a hierarchy of objects.  This hierarchy was 4 levels deep and contained around 1,000 objects.  I tried a few approaches to see what method could load these objects the fastest.

In my first approach, I decided to de-normalize my results to limit the number of database hits.  Instead of 100’s of database hits (queries), I returned 3 large result sets.  Then, I thought I’d loop the cursors to fill the hierarchy starting with the top levels and working to the detail levels.  Loading the objects took 50.9 seconds…  Ouch.

I knew that all 3 queries took < 1 second.  So I looked at the loading of each object.  I thought part of my problem was the SQLiteCursor.getColumnIndex() method.  I’ve read that this method can be very taxing.  So I thought I’d create a HashMap of the column indexes and then pass that to the loading of each object so I didn’t have to call the getColumnIndex() method for each property for each object.  A coworker of mine made me a bet that the SQLiteCursor already lazy loaded the column indexes so subsequent calls wouldn’t have to loop the column names each call.  He was right.  So creating a HashMap of column names to indexes would not help.

My next thought was that looping SQLiteCursors over and over was not a good idea.  So I had a better idea.  Load the objects from the cursor with one loop into a generic ArrayList, then assemble the hierarchy/relationships. 

I’m used to .NET and disconnected Datasets and DataTables.  I’d usually load a DataTable then run DataTable.Select() filters on it to get my data subsets to build object hierarchies… but Android doesn’t have such mechanisms.  So I decided to add helper properties to my objects so that I could load a generic ArrayList of my objects from cursors and then assemble them after the database querying was done.  This proved to be a very good idea.  3 queries with loading of 3 generic ArrayLists of my objects ran in just over 1 second.  That proved that the SQLiteCursor.getColumnIndex() is not as taxing as I thought.

Assembling the objects into the hierarchy then boiled down to nested loops and setting the appropriate object relationship properties.  Total cost: < 3 seconds after a 50.9 second first try.  Much better… I want it in ~1 second.  We’ll see where I go from here.

Lesson: Don’t use SQLiteCursors in nested loops to build object hierarchies.  Get the data in the least number of database hits that you can, load your objects from a cursor in a single loop, close your cursor, and then assemble the object hierarchies after.  Also, remember if you are running multiple select statements to run them under one transaction.  Multiple transactions for multiple queries is more taxing than one transaction for multiple queries.  Remember that SQLite implicitly creates a transaction for each query ran unless you explicitly create one and run all queries under it.