Enable Field Indexes

For class Car with field "pilot":

Java: Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true)

Advantage

The fastest way to improve the performance of your queries is to enable indexing on some of your class's key fields. You can read how to do it in Indexing chapter of this documentation.

Further step of index tuning is to optimize indexes for Class.Field1.Field2 access. What will give us the best performance:

  • index on Field1;
  • index on Field2;
  • index on both fields?

To find the answer let's consider classes Car and Pilot from the previous chapters. In order to see indexing influence we will put 10000 new cars in our storage (note that for db4o version > 5.6 the amount of objects  should be much more to see the differences in execution time due to BTree based index optimized for big amounts of data ):

IndexedExample.java: fillUpDB
01public static void fillUpDB(){ 02 new File(Util.YAPFILENAME).delete(); 03 ObjectContainer db=Db4o.openFile(Util.YAPFILENAME); 04 try { 05 for (int i=0; i<10000;i++){ 06 AddCar(db,i); 07 } 08 } 09 finally { 10 db.close(); 11 } 12 }

IndexedExample.java: addCar
1private static void AddCar(ObjectContainer db, int points) 2 { 3 Car car = new Car("BMW"); 4 car.setPilot(new Pilot("Tester", points)); 5 db.set(car); 6 }

Now we have lots of similar cars differing only in the amount of pilots' points - that will be our constraint for the query.

IndexedExample.java: noIndex
01public static void noIndex() { 02 ObjectContainer db=Db4o.openFile(Util.YAPFILENAME); 03 try { 04 Query query = db.query(); 05 query.constrain(Car.class); 06 query.descend("pilot").descend("points").constrain(new Integer(99)); 07 08 long t1 = System.currentTimeMillis(); 09 ObjectSet result = query.execute(); 10 long t2 = System.currentTimeMillis(); 11 long diff = t2 - t1; 12 System.out.println("Test 1: no indexes"); 13 System.out.println("Execution time="+diff + " ms"); 14 listResult(result); 15 } 16 finally { 17 db.close(); 18 } 19 }

You can check execution time on your workstation using interactive version of this tutorial.

Let's create index for pilots and their points and test the same query again:

IndexedExample.java: fullIndex
01public static void fullIndex() { 02 Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true); 03 Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true); 04 ObjectContainer db=Db4o.openFile(Util.YAPFILENAME); 05 try { 06 Query query = db.query(); 07 query.constrain(Car.class); 08 query.descend("pilot").descend("points").constrain(new Integer(99)); 09 10 long t1 = System.currentTimeMillis(); 11 ObjectSet result = query.execute(); 12 long t2 = System.currentTimeMillis(); 13 long diff = t2 - t1; 14 System.out.println("Test 2: index on pilot and points"); 15 System.out.println("Execution time="+diff + " ms"); 16 listResult(result); 17 } 18 finally { 19 db.close(); 20 } 21 }

That result is considerably better and proves the power of indexing.

But do we really need 2 indexes? Will single pilot or points index suffice? Let's test this as well:

IndexedExample.java: pilotIndex
01public static void pilotIndex() { 02 Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(true); 03 Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(false); 04 ObjectContainer db=Db4o.openFile(Util.YAPFILENAME); 05 try { 06 Query query = db.query(); 07 query.constrain(Car.class); 08 query.descend("pilot").descend("points").constrain(new Integer(99)); 09 10 long t1 = System.currentTimeMillis(); 11 ObjectSet result = query.execute(); 12 long t2 = System.currentTimeMillis(); 13 long diff = t2 - t1; 14 System.out.println("Test 3: index on pilot"); 15 System.out.println("Execution time="+diff + " ms"); 16 listResult(result); 17 } 18 finally { 19 db.close(); 20 } 21 }

IndexedExample.java: pointsIndex
01public static void pointsIndex() { 02 Db4o.configure().objectClass(Car.class).objectField("pilot").indexed(false); 03 Db4o.configure().objectClass(Pilot.class).objectField("points").indexed(true); 04 ObjectContainer db=Db4o.openFile(Util.YAPFILENAME); 05 try { 06 Query query = db.query(); 07 query.constrain(Car.class); 08 query.descend("pilot").descend("points").constrain(new Integer(99)); 09 10 long t1 = System.currentTimeMillis(); 11 ObjectSet result = query.execute(); 12 long t2 = System.currentTimeMillis(); 13 long diff = t2 - t1; 14 System.out.println("Test 4: index on points"); 15 System.out.println("Execution time="+diff + " ms"); 16 listResult(result); 17 } 18 finally { 19 db.close(); 20 } 21 }

Single index does not increase query performance on second level fields.

To maximize retrieval performance on encapsulated fields of different levels of enclosure

Class.Field1.Field2.Field3(.FieldN)

indexes for each field level should be created:

Class.Field1.Indexed(true)

Field1Class.Field2.Indexed(true)

Field2Class.Field3.Indexed(true)

. . .

Field(N-1)Class.FieldN.Indexed(true)

Alternate Strategies

Field indexes dramatically improve query performance but they may considerably reduce storage and update performance. The best way to decide where to put the indexes is to test them on completed application with typical typical data load.