How to increase query speed in db4o?

Question

OutOfMemoryError caused when db4o databse has 15000+ objects

My question is in reference to my previous question (above). For the same PostedMessage model and same query.

With 100,000 PostedMessage objects, the query takes about 1243 ms to return first 20 PostedMessages.

Now, I have saved 1,000,000 PostedMessage objects in db4o. The same query took 342,132 ms. Which is non-linearly high.

How can I optimize the query speed?

FYR: The timeSent and timeReceived are Indexed fields. I am using SNAPSHOT query mode. I am not using TA/TP.

Answer 1

@Gamlor No, I am not sorting at all. The code is as follows:

public static ObjectSet<PostedMessage> getMessagesBetweenDates(
        Calendar after,
        Calendar before,
        ObjectContainer db) {

    if (after == null || before == null || db == null) {
        return null;
    }
    Query q = db.query(); //db is pre-configured to use SNAPSHOT mode.
    q.constrain(PostedMessage.class);
    Constraint from = q.descend("timeRecieved").constrain(new Long(after.getTimeInMillis())).greater().equal();
    q.descend("timeRecieved").constrain(new Long(before.getTimeInMillis())).smaller().equal().and(from);
    ObjectSet<EmailMessage> results = q.execute();
    return results;
}

The arguments to this method are as follows:

after = 13-09-2011 10:55:55

before = 13-09-2011 10:56:10

And I expect only 10 PostedMessages to be returned between "after" and "before". (I am generating dummy PostedMessage with timeReceived incremented by 1 sec each.)

Answer 2

Do you sort the result? Unfortunatly db4o doesn't use the index for sorting / orderBy. That means it will run a regular sort algorith, with O(n*log(n)). It won't scala liniearly.

Also db4o doesn't support a TOP operator. That means even without sorting it takes quite a bit of time to copy the ids to the results set, even when you never read the entities afterwards.

So, there's no real good solution for this, except trying to use some criteria which cut down the result size.

Some adventerous people might use a different query evaluation, but personally don't recommend that.