db4o Developer Community

Hi,

i have problem with performance of retriving my objects from database.

So I tried to run example class com.db4o.f1.chapter21.IndexedExample and here are results:

 
Test 1: no indexes
Execution time=469 ms
1
BMW[Tester/99]
Test 2: index on pilot and points
Execution time=344 ms
1
BMW[Tester/99]
Test 3: index on pilot
Execution time=344 ms
1
BMW[Tester/99]
Test 4: index on points
Execution time=391 ms
1
BMW[Tester/99] 

There must be something wrong 

---

Jan Chodura

execuse me I interrupted previous post ...

 
So I checked I have ALL jars in classpath (but in this example it's not necessary - queries are in SODA)

Even I wrote annotations for indexes for Car.pilot and Pilot.points

I checked that indexes are set before open file, e.g one of methods

    public static void pointsIndex() {
        Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(false);
        Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true);
        ObjectContainer db=Db4o.openFile(Util.YAPFILENAME);
        try {
            Query query = db.query();
            query.constrain(Car.class);
            query.descend("pilot").descend("points").constrain(new Integer(99));

            long t1 = System.currentTimeMillis();
            ObjectSet  result = query.execute();
            long t2 = System.currentTimeMillis();
            long  diff = t2 - t1;
            System.out.println("Test 4: index on points");
            System.out.println("Execution time="+diff + " ms");
            listResult(result);
        }
        finally {
            db.close();
        }
    }

 

What else have I check and change?

I think that example is OK so I have to change configuration.. 

 thank you
 

---

Jan Chodura

Jan Chodura wrote:
> Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(false);

Should be #indexed(true).

thank you for response, but it's method pointsIndex - it indexes only points

I call these method from db4o tutorial class com.db4o.f1.Main

public class Main {
    public static void main(String[] args) throws Exception {
//        FirstStepsExample.main(args);
//        QueryExample.main(args);
//        StructuredExample.main(args);
//        CollectionsExample.main(args);
//        InheritanceExample.main(args);
//        DeepExample.main(args);
//        TransactionExample.main(args);
//        ClientServerExample.main(args);
        IndexedExample.fillUpDB();
        IndexedExample.noIndex();
        IndexedExample.fullIndex();
        IndexedExample.pilotIndex();
        IndexedExample.pointsIndex();
        IndexedExample.QBEIndex();
//        DiagnosticExample.testEmpty();
//        DiagnosticExample.testArbitrary();
//        DiagnosticExample.testIndexDiagnostics();
   }
}

---

Jan Chodura

Jan Chodura wrote:
> thank you for response, but it's method pointsIndex - it indexes only points

The query code you posted requires both indexes:

query.descend("pilot").descend("points").constrain(new Integer(99));

> The query code you posted requires both indexes:
> query.descend("pilot").descend("points").constrain(new Integer(99));

You are right. I posted only one method as example from many ones of IndexedExample from tutorial code.
This class shows different times with combinations of indexing attributes.

package com.db4o.f1.chapter21;

import java.io.File;

import com.db4o.Db4o;
import com.db4o.ObjectContainer;
import com.db4o.ObjectSet;
import com.db4o.f1.Util;

import com.db4o.query.Query;


public class IndexedExample extends Util {
    public static void noIndex() {
        ObjectContainer db=Db4o.openFile(Util.YAPFILENAME);
        try {
            Query query = db.query();
            query.constrain(Car.class);
            query.descend("pilot").descend("points").constrain(new Integer(99));

            long t1 = System.currentTimeMillis();
            ObjectSet  result = query.execute();
            long t2 = System.currentTimeMillis();
            long  diff = t2 - t1;
            System.out.println("Test 1: no indexes");
            System.out.println("Execution time="+diff + " ms");
            listResult(result);
        }
        finally {
            db.close();
        }
    }
   
    public static void fillUpDB(){
        new File(Util.YAPFILENAME).delete();
        ObjectContainer db=Db4o.openFile(Util.YAPFILENAME);
        try {
            for (int i=0; i<10000;i++){
                AddCar(db);
            }
        db.commit();
        }
        finally {
            db.close();
        }
    }
 
    public static void pilotIndex() {
        Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true);
        Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(false);
        ObjectContainer db=Db4o.openFile(Util.YAPFILENAME);
        try {
            Query query = db.query();
            query.constrain(Car.class);
            query.descend("pilot").descend("points").constrain(new Integer(99));

            long t1 = System.currentTimeMillis();
            ObjectSet  result = query.execute();
            long t2 = System.currentTimeMillis();
            long  diff = t2 - t1;
            System.out.println("Test 3: index on pilot");
            System.out.println("Execution time="+diff + " ms");
            listResult(result);
        }
        finally {
            db.close();
        }
    }
  
    public static void pointsIndex() {
        Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(false);
        Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true);
        ObjectContainer db=Db4o.openFile(Util.YAPFILENAME);
        try {
            Query query = db.query();
            query.constrain(Car.class);
            query.descend("pilot").descend("points").constrain(new Integer(99));

            long t1 = System.currentTimeMillis();
            ObjectSet  result = query.execute();
            long t2 = System.currentTimeMillis();
            long  diff = t2 - t1;
            System.out.println("Test 4: index on points");
            System.out.println("Execution time="+diff + " ms");
            listResult(result);
        }
        finally {
            db.close();
        }
    }
   
   
    public static void fullIndex() {
        Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true);
        Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true);
        ObjectContainer db=Db4o.openFile(Util.YAPFILENAME);
        try {
            Query query = db.query();
            query.constrain(Car.class);
            query.descend("pilot").descend("points").constrain(new Integer(99));

            long t1 = System.currentTimeMillis();
            ObjectSet  result = query.execute();
            long t2 = System.currentTimeMillis();
            long  diff = t2 - t1;
            System.out.println("Test 2: index on pilot and points");
            System.out.println("Execution time="+diff + " ms");
            listResult(result);
        }
        finally {
            db.close();
        }
    }
   
    private static void AddCar(ObjectContainer db, int points)
    {
        Car car = new Car("BMW");
        car.setPilot(new Pilot("Tester", points));
        db.set(car);
    }
   
   
}

Method fullIndex() is the most important here - attributes pilot and points are indexed both together. But execution time is about 350ms for 10K objects.

thanks
 

---

Jan Chodura

Jan Chodura wrote:
> This class shows different times with combinations of
> indexing attributes.

The problem with your code:
Because your settings for indexes are different between
the method calls, indexes will be created and dropped,
the first time they are needed.

That means your code is measuring the time for indexing
the complete database.

If you want to measure the difference between indexed
queries and non-indexed queries in one app, you should
test storing and retrieving objects of different classes.

Thank you Carl,

> If you want to measure the difference between indexed
> queries and non-indexed queries in one app, you should
> test storing and retrieving objects of different classes.

I cut the example to only one searching method - fullIndex:

import java.io.File;

import com.db4o.Db4o;
import com.db4o.ObjectContainer;


import com.db4o.query.Query;

class Car {  
  private String model;
  private Pilot pilot;
 
  public Car(String model) {
      this.model=model;
      this.pilot=null;
  }
   
  public Pilot getPilot() {
      return pilot;
  }
 
  public void setPilot(Pilot pilot) {
      this.pilot = pilot;
  }
 
  public String getModel() {
      return model;
  }
 
  public String toString() {
      return model+"["+pilot+"]";
  }
}

class Pilot {
  private String name;
  private int points;
 
  public Pilot(final String name, final int points) {
      this.name = name;
      this.points = points;
  }

  public int getPoints() {
      return points;
  }
 
  public void addPoints(int points) {
      this.points+=points;
  }
 
  public String getName() {
      return name;
  }
 
  public String toString() {
      return name+"/"+points;
  }
}


public class FullIndexedExample{

  private static final int COUNTQUERIES = 20;
  private static ObjectContainer db;
 
  public static void main(String[] args) {
    fillUpDB();
    fullIndex();
  }
 
  public static void fillUpDB(){
    new File("formule1.yap").delete();
    db = Db4o.openFile("formule1.yap");
    int count = 1000;
   
    try {
      for (int i=0; i<count;i++){
      addCar(i);
      }
      db.commit();
    }
    finally {
      db.close();
    }
  }
 
  public static void fullIndex() {
    Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true);
    Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true);
    db=Db4o.openFile("formule1.yap");
   
    try {
      long avgTime = avgTime();
      System.out.println("Test: index on pilot and points");
      System.out.println("Execution time=" + avgTime + " ms");
    }
    finally {
        db.close();
    }
  }
 
  private static void addCar(final int points){
    Car car = new Car("BMW");
    car.setPilot(new Pilot("pilot" + points, points));
    db.set(car);
  }
   
    private static long avgTime(){
     
      Query query = db.query();
      query.constrain(Car.class);
      query.descend("pilot").descend("points").constrain(99);
     
      long sumType = 0;
      for(int i = 0; i<COUNTQUERIES; i++){
       
        long t1 = System.currentTimeMillis();
        query.execute();
        long t2 = System.currentTimeMillis();
        long  diff = t2 - t1;
         sumType += diff;
       
      }
     
      return sumType/COUNTQUERIES;
    }
   
}

I watched in Object Manager into formule1.yap and both fields were indexed. But result  is average searching time 37 ms among 1000 objects. When I tried to change field pilot from Pilot class to String,  searching time was 0ms. So I see problem in my example is in indexing associated class Pilot.

What I do bad?

---

Jan Chodura

Jan Chodura wrote:
> I watched in Object Manager into formule1.yap and both
> fields were indexed. But result is average searching
> time 37 ms among 1000 objects.

Hi Jan,

you have to tell db4o to maintain the index while you store
the objects.

To do this, call the following two lines *before* you call
fillUpDB()

Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true);
Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true);

Right now your code is still measuring the time to index all
the objects.

> To do this, call the following two lines *before* you call >
> fillUpDB()

> Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true);
> Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true);

Carl,  thanks for tip.

Unfortunately it doesn't work. Result is forever bad:

Test: index on pilot and points
Execution time=39 ms

I run code on another computer and results had been similiar. Really is my code fast in your computer?

BTW I thought index must be set before any opening of database and the first query is slower because of initial indexing of fields.
I have db4o 6.1.501

PS: Thank you very much for spending time on such "silly" questions.

---

Jan Chodura

I tried another versions of db4o

5.3 - 25ms/1000objects

6.1 - 38ms/1000objects

6.2(developer) - 17ms/1000 

---

Jan Chodura

Hi,

I found, http://tracker.db4o.com/browse/COR-280

---

Jan Chodura

Jan Chodura wrote:
> I found, http://tracker.db4o.com/browse/COR-280

Hi Jan,

I can confirm the slow behaviour for two-level indexes,
for some cases.

From code a user sent, I created a second Jira ticket
just now...

http://tracker.db4o.com/browse/COR-655

....only to find that you linked to the original issue.

Thanks for insisting.