db4o Developer Community

Hi everyone,

Last weeks I evaluated Db4o features and functions. I'm very impressed by database performance and how easy Db4o is to use.

But, queries with order by definition I have huge performance issues.

Here is my example test case:
I have 100 000 objects in database and my query have to list last ten added elements of these 100 000 objects

-- Data object --
public class TestElement
{
   private String name
   private int insertIndex
... and setters and getters for these fields
}

-- Db4o Configuration --

Configuration config = Db4o.newConfiguration();
config.objectClass(TestElement.class).objectField("insertIndex").indexed(true);
config.optimizeNativeQueries(true);
config.queries().evaluationMode(QueryEvaluationMode.SNAPSHOT);


-- SODA: query --
Constraint cons = query.constrain(TestElement.class);
query.descend("insertIndex").orderDescending();


-- Results--
Execution time:1500 – 2000ms / operation.
Repeat same query again many times: execution times are the same

-- Resource usege --
100 % CPU usage.
no any iowait


With this query and this configuration, execution times are linear with objects in database. I hope that someone can show what is wrong with this case. Is there some kind of order by index available ... or something ... please help. Current response times and resource usage are too much for my application.

Hi,

any chance to restrict your object-set via an additional constraint? If I remember correctly, db4o doesn't use indices for ordering an object-set (at least this is true if the resulting object-set is too large).

The following example (additional constraint is marked as bold) executes "your" query in about 2 milliseconds. Maybe your application can remember some kind of "watermark"-index for the last inserted items.

Cheers, Maik

import java.io.File;

import com.db4o.Db4o;
import com.db4o.ObjectContainer;
import com.db4o.config.Configuration;
import com.db4o.query.Query;

public class OrderTest {

    private int index;

    public OrderTest(int index) {
        this.index = index;
    }

    public String toString() {
        return "" + index;
    }

    public static void main(String[] args) {
        new File("test.yap").delete();

        Configuration config = Db4o.newConfiguration();
        config.objectClass(OrderTest.class).objectField("index").indexed(true);

        ObjectContainer db4o = Db4o.openFile(config, "test.yap");
        for (int i = 0; i < 100000; i++) {
            db4o.store(new OrderTest(i));
        }
        db4o.commit();

        Query query = db4o.query();
        query.constrain(OrderTest.class);
        query.descend("index").constrain(99900).greater();
        query.descend("index").orderDescending();

        for (int i = 0; i < 20; i++) {
            long start = System.currentTimeMillis();
            System.out.println(query.execute().subList(0,10));
            System.out.println(System.currentTimeMillis() - start);
        }
       
        db4o.close();
    }
}
 

Thanks,

Good point.