performance - Ormlite DAO in android getting really slow when querying more than few thousand results

Question

Have a problem with querying data via Ormlite DAO, when there are few thousand results.

Code:

List<Point> pl = db.getPointsDAO().queryBuilder().where().
            eq("route_id", croute).query();

When I want to get a large list of points List<Point> pl for current Route croute I have to wait like 40 sec for 40.000 points.

where Point.class is:

@DatabaseTable(tableName = "points")
public class Point extends BaseEntity {
    @DatabaseField(generatedId = true)
    private Integer point_id;
    @DatabaseField(canBeNull = false)
    ...
    @DatabaseField(canBeNull = false)
    private Double dose;
    @DatabaseField(dataType=DataType.DATE_STRING, format="yyyy-MM-dd HH:mm:ss")
    public Date date;
    @DatabaseField(canBeNull=true,foreign=true)
    private Route route;

public Point() {
    super();
};
... ...
}

and Route.class is:

@DatabaseTable(tableName = "routes")
public class Route extends BaseEntity {

    @DatabaseField(generatedId = true)
    private Integer route_id;

    @DatabaseField(canBeNull = true)
    private String name;

    @ForeignCollectionField(eager = false)
    ForeignCollection<Point> points;

    public Route() {
        super();
    }
    ... ...
}

Some ideas what I'm doing wrong?

Thanks, Toni

Answer 1

Couple of things to try @toni.

1. I'd consider storing your Date as a DATE_LONG instead of a string which will save 40k string/date conversions.
2. @Selvin is right that if there is some way for you can iterate through the database, this may lower your memory requirements and speed things up. See dao.iterator() in ORMLite.
3. I'd use int and double primitives to lower your GC for each of your objects although I doubt it will make much of a difference.
4. Try loading in 1000 points, then 10000, then 20000 to see if there is a drop off in performance at some point. That will tell you that you are hitting up against memory limits.
5. Use the adb logcat utility to see if you can see the GC times to see if you are just thrashing the collector. Anything that you can do to lower your memory usage will help then. Look for lines like:

   GC_EXPLICIT freed 4140 objects / 216560 bytes in 114ms

6. Although I doubt it is the issue, could you be missing an index? Try adding a index = true on the foreign route field.