How can I improve this method of loading objects w

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 getAllCragsWithLocation() method below.

There are 590 Crags in the database and 40 don't have a location.

There are several lines logged such as

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 be loading the objects another way? Running the query at the sqlite3 command line takes around 100th of a 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;
    }

The crag object (simplified) is:

@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;
    }

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...

The return from the getAllCragsWithLocation() is iterated over to prepare for addition to a MapOverlay. Timing that process takes less than a second.

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.

UPDATED UPDATE: Instead of quickly swapping in DB4O I've spent more time comparing the two.

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.

If I run my process to insert 590 objects then ORMLite takes around 24 seconds and DB40 takes around 40.

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

While ORMLite takes around 7 seconds for both queries.

So there's actually very little in it

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.

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.

回答1:

I'm really surprised that it is taking that 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 those fields will generate another database query that would take time although I would not expect seconds. ORMLite is not smart about using JOIN to satisfy eager fetched remote objects.

One thing to try is to use the table-config process to work around Android's dog, dog slow reflection implementation when generating DAO implementations. See the docs for that:

http://ormlite.com/docs/table-config

I would be curious if you figure it out although I understand if you need to switch to some other package to get working.

回答2:

I think you should consider using a ContentProvider. I have made an application manipulating 2k rows but the query and object regeneration is done in less than 10 sec on the emulator