title: How can I improve this method of loading objects with ORMLite on Android?
body: <p>I am loading a set of objects (and their foreign objects) using ormlite 4.35 on Android. It takes (in the emulator) between 16 and 19 seconds for the timing in the <code>getAllCragsWithLocation()</code> method below.</p>
  
  <p>There are 590 Crags in the database and 40 don't have a location.</p>
  
  <p>There are several lines logged such as</p>
  
  <pre><code>03-19 11:03:54.441: I/dalvikvm(1156): Jit: resizing JitTable from 1024 to 2048
  03-19 11:03:55.531: D/dalvikvm(1156): GC_CONCURRENT freed 544K, 37% free 5174K/8199K, external 731K/1109K, paused 6ms+11ms
  </code></pre>
  
  <p>Should I be loading the objects another way? Running the query at the sqlite3 command line takes around 100th of a second...</p>
  
  <pre><code>public List&lt;Crag&gt; getAllCragsWithLocation() {
          Log.i(TAG,"beginning db calls");
          long startQuery = System.currentTimeMillis();
          QueryBuilder&lt;Crag,Integer&gt; queryBuilder = helper.getCragDao().queryBuilder();
          List&lt;Crag&gt; results = new ArrayList&lt;Crag&gt;();
          try {
              queryBuilder.where().isNotNull("location_id");
              Log.i(TAG,queryBuilder.prepareStatementString());
              PreparedQuery&lt;Crag&gt; preparedQuery = queryBuilder.prepare();
              results = helper.getCragDao().query(preparedQuery);
          } catch (android.database.SQLException e) {
              Log.e(TAG,e.toString());
          } catch (SQLException e) {
              Log.e(TAG,e.toString());
          }
          Log.i(TAG,"ending query after "+((System.currentTimeMillis()-startQuery)/1000)+" seconds");
          return results;
      }
  </code></pre>
  
  <p>The crag object (simplified) is:</p>
  
  <pre><code>@DatabaseTable
      public class Crag {
          public Crag() {
              //ormlite requires a no-arg constructor?
              guidebooks = new ArrayList&lt;Guidebook&gt;();
          }
  
          @DatabaseField(id=true) private int id;
          @ForeignCollectionField(eager = true)
          private Collection&lt;Guidebook&gt; guidebooks;
          @DatabaseField(foreign=true, foreignAutoRefresh = true, index=true)
          private uk.co.bmc.rad.models.Location location;
          @DatabaseField(foreign=true,foreignAutoRefresh=true)
          private SubRegion subRegion;
      }
  </code></pre>
  
  <p>The foreign objects are much simpler classes than Crag. For the purposes of display only the location is necessary at the point the objects are loaded form the database but I use the foreign objects when comparing for equality so...</p>
  
  <p>The return from the <code>getAllCragsWithLocation()</code> is iterated over to prepare for addition to a <code>MapOverlay</code>. Timing that process takes less than a second.</p>
  
  <hr>
  
  <p>UPDATE:
  I've just swapped in DB4O to compare and that loads all crags essentially instantly by comparison so I think I'll go with that.</p>
  
  <hr>
  
  <p>UPDATED UPDATE:
  Instead of quickly swapping in DB4O I've spent more time comparing the two.</p>
  
  <p>In a JUnit 3 AndroidTestCase if I insert a single record into the database and then query for it directly then both ORMLite and DB4O take about a second (total) to insert and return the object. </p>
  
  <p>If I run my process to insert 590 objects then ORMLite takes around 24 seconds and DB40 takes around 40.</p>
  
  <p>Immediately after that insert if I call getAllCragsWithLocation() and then getAllCrags() DB40 takes 7 seconds and then 0 seconds to return a List of Crags so I'm guessing it has a cache that speeds the second query</p>
  
  <p>While ORMLite takes around 7 seconds for both queries.</p>
  
  <p>So there's actually very little in it</p>
  
  <hr>
  
  <p>UPDATED UPDATE UPDATED:
  Grabbed a Transformer Prime today and just ran the app on that and the crags loaded in 1 second as compared to the 7 on the emulator.</p>
  
  <p>Since I've managed to grok ormlite's query syntax already; can fall back on raw sql and the sqlite table structure is more transferable I'm going to stick with ormlite over db4o.</p>