How can I improve this way of loading objects from ORMLite on Android?
I am loading a collection of objects (and their foreign objects) using ormlite 4.35 on Android. It takes (in the emulator) 16 to 19 seconds for the time in the method getAllCragsWithLocation()
below.
There are 590 Crags in the database while 40 are not.
There are multiple lines, for example
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
Should I load objects differently? Running the query on the sqlite3 command line takes about 100th second ...
public List<Crag> getAllCragsWithLocation() {
Log.i(TAG,"beginning db calls");
long startQuery = System.currentTimeMillis();
QueryBuilder<Crag,Integer> queryBuilder = helper.getCragDao().queryBuilder();
List<Crag> results = new ArrayList<Crag>();
try {
queryBuilder.where().isNotNull("location_id");
Log.i(TAG,queryBuilder.prepareStatementString());
PreparedQuery<Crag> 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;
}
Crag object (simplified):
@DatabaseTable
public class Crag {
public Crag() {
//ormlite requires a no-arg constructor?
guidebooks = new ArrayList<Guidebook>();
}
@DatabaseField(id=true) private int id;
@ForeignCollectionField(eager = true)
private Collection<Guidebook> guidebooks;
@DatabaseField(foreign=true, foreignAutoRefresh = true, index=true)
private uk.co.bmc.rad.models.Location location;
@DatabaseField(foreign=true,foreignAutoRefresh=true)
private SubRegion subRegion;
}
Foreign Objects is much easier than Crag. For display purposes, only the location is required at the point where the objects are loaded from the database, but I'm using extraneous objects when comparing for equality, so ...
The return from is getAllCragsWithLocation()
repeated to prepare to be appended to MapOverlay
. This process takes less than a second.
UPDATE: I just swapped DB4O to compare and this loads all rocks essentially instantly by comparison, so I think I'll go with that.
UPDATED UPDATE: Instead of a quick swap in DB4O, I spent more time comparing the two.
In JUnit 3 AndroidTestCase, if I insert one record into the database and then query it directly, then both ORMLite and DB4O take about a second (total) to insert and return an object.
If I run my process to insert 590 objects, then ORMLite will take about 24 seconds and DB40 will take about 40.
Immediately after this insert, if I call getAllCragsWithLocation () and then getAllCrags () DB40 takes 7 seconds and then 0 seconds to return the list of skins, so I assume it has a cache that speeds up the second request
Whereas ORMLite takes about 7 seconds for both requests.
So there is actually very little in it
UPDATED UPDATE UPDATED: Today we grabbed Transformer Prime and just ran the app on this and rocks loaded in 1 second, up from 7 on the emulator.
Since I managed to check the ormlite query syntax again; could fall back to raw sql and the sqlite table structure is more portable. I'm going to stick with ormlite over db4o.
source to share
I am very surprised that it takes so long.
@ForeignCollectionField(eager = true)
private Collection<Guidebook> guidebooks;
@DatabaseField(foreign=true, foreignAutoRefresh = true, index=true)
private uk.co.bmc.rad.models.Location location;
@DatabaseField(foreign=true,foreignAutoRefresh=true)
private SubRegion subRegion;
Each of these fields will generate a different database query, which would take time, although I would not expect seconds. ORMLite does not know how to use JOIN
to satisfy the desired remote objects.
One thing to try is to use the table-config process to work with the dog, the dog of the slow reflection implementation when building DAO implementations. See the docs for this:
I would be curious if you figure this out, although I understand if you need to switch to a different package in order to work.
source to share