Enable Field Indexes

For class Car with field "pilot":

c#: Db4oFactory.Configure().ObjectClass(typeof(Car)).ObjectField("pilot").Indexed(true)

VB: Db4oFactory.Configure().ObjectClass(GetType(Car)).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.cs: FillUpDB
01public static void FillUpDB(){ 02 File.Delete(YapFileName); 03 IObjectContainer db=Db4oFactory.OpenFile(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.vb: FillUpDB
01Public Shared Sub FillUpDB() 02 File.Delete(YapFileName) 03 Dim db As IObjectContainer = Db4oFactory.OpenFile(YapFileName) 04 Try 05 For i As Integer = 0 To 10000 06 AddCar(db, i) 07 Next 08 Finally 09 db.Close() 10 End Try 11 End Sub

IndexedExample.cs: AddCar
1private static void AddCar(IObjectContainer db, int points) 2 { 3 Car car = new Car("BMW"); 4 car.Pilot= new Pilot("Tester", points); 5 db.Set(car); 6 }

IndexedExample.vb: AddCar
1Public Shared Sub AddCar(ByVal db As IObjectContainer, ByVal points As Integer) 2 Dim car As Car = New Car("BMW") 3 Dim pilot As Pilot = New Pilot("Tester", points) 4 car.Pilot = pilot 5 db.[Set](car) 6 End Sub

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

IndexedExample.cs: NoIndex
01public static void NoIndex() { 02 IObjectContainer db=Db4oFactory.OpenFile(YapFileName); 03 try { 04 IQuery query = db.Query(); 05 query.Constrain(typeof(Car)); 06 query.Descend("_pilot").Descend("_points").Constrain("99"); 07 08 DateTime dt1 = DateTime.UtcNow; 09 IObjectSet result = query.Execute(); 10 DateTime dt2 = DateTime.UtcNow; 11 TimeSpan diff = dt2 - dt1; 12 Console.WriteLine("Test 1: no indexes"); 13 Console.WriteLine("Execution time="+diff.Milliseconds + " ms"); 14 ListResult(result); 15 } 16 finally { 17 db.Close(); 18 } 19 }

IndexedExample.vb: NoIndex
01Public Shared Sub NoIndex() 02 Dim db As IObjectContainer = Db4oFactory.OpenFile(YapFileName) 03 Try 04 Dim query As IQuery = db.Query() 05 query.Constrain(GetType(Car)) 06 query.Descend("_pilot").Descend("_points").Constrain("99") 07 Dim dt1 As DateTime = DateTime.UtcNow 08 Dim result As IObjectSet = query.Execute() 09 Dim dt2 As DateTime = DateTime.UtcNow 10 Dim diff As TimeSpan = dt2 - dt1 11 Console.WriteLine("Test 1: no indexes") 12 Console.WriteLine("Execution time=" + diff.Milliseconds.ToString() + " ms") 13 ListResult(result) 14 Finally 15 db.Close() 16 End Try 17 End Sub

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.cs: FullIndex
01public static void FullIndex() { 02 Db4oFactory.Configure().ObjectClass(typeof(Car)).ObjectField("_pilot").Indexed(true); 03 Db4oFactory.Configure().ObjectClass(typeof(Pilot)).ObjectField("_points").Indexed(true); 04 IObjectContainer db=Db4oFactory.OpenFile(YapFileName); 05 try { 06 IQuery query = db.Query(); 07 query.Constrain(typeof(Car)); 08 query.Descend("_pilot").Descend("_points").Constrain("99"); 09 10 DateTime dt1 = DateTime.UtcNow; 11 IObjectSet result = query.Execute(); 12 DateTime dt2 = DateTime.UtcNow; 13 TimeSpan diff = dt2 - dt1; 14 Console.WriteLine("Test 2: index on pilot and points"); 15 Console.WriteLine("Execution time="+diff.Milliseconds + " ms"); 16 ListResult(result); 17 } 18 finally { 19 db.Close(); 20 } 21 }

IndexedExample.vb: FullIndex
01Public Shared Sub FullIndex() 02 Db4oFactory.Configure().ObjectClass(GetType(Car)).ObjectField("_pilot").Indexed(True) 03 Db4oFactory.Configure().ObjectClass(GetType(Pilot)).ObjectField("_points").Indexed(True) 04 Dim db As IObjectContainer = Db4oFactory.OpenFile(YapFileName) 05 Try 06 Dim query As IQuery = db.Query() 07 query.Constrain(GetType(Car)) 08 query.Descend("_pilot").Descend("_points").Constrain("99") 09 Dim dt1 As DateTime = DateTime.UtcNow 10 Dim result As IObjectSet = query.Execute() 11 Dim dt2 As DateTime = DateTime.UtcNow 12 Dim diff As TimeSpan = dt2 - dt1 13 Console.WriteLine("Test 2: index on pilot and points") 14 Console.WriteLine("Execution time=" + diff.Milliseconds.ToString() + " ms") 15 ListResult(result) 16 Finally 17 db.Close() 18 End Try 19 End Sub

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.cs: PilotIndex
01public static void PilotIndex() { 02 Db4oFactory.Configure().ObjectClass(typeof(Car)).ObjectField("_pilot").Indexed(true); 03 Db4oFactory.Configure().ObjectClass(typeof(Pilot)).ObjectField("_points").Indexed(false); 04 IObjectContainer db=Db4oFactory.OpenFile(YapFileName); 05 try { 06 IQuery query = db.Query(); 07 query.Constrain(typeof(Car)); 08 query.Descend("_pilot").Descend("_points").Constrain("99"); 09 10 DateTime dt1 = DateTime.UtcNow; 11 IObjectSet result = query.Execute(); 12 DateTime dt2 = DateTime.UtcNow; 13 TimeSpan diff = dt2 - dt1; 14 Console.WriteLine("Test 3: index on pilot"); 15 Console.WriteLine("Execution time="+diff.Milliseconds + " ms"); 16 ListResult(result); 17 } 18 finally { 19 db.Close(); 20 } 21 }

IndexedExample.vb: PilotIndex
01Public Shared Sub PilotIndex() 02 Db4oFactory.Configure().ObjectClass(GetType(Car)).ObjectField("_pilot").Indexed(True) 03 Db4oFactory.Configure().ObjectClass(GetType(Pilot)).ObjectField("_points").Indexed(False) 04 Dim db As IObjectContainer = Db4oFactory.OpenFile(YapFileName) 05 Try 06 Dim query As IQuery = db.Query() 07 query.Constrain(GetType(Car)) 08 query.Descend("_pilot").Descend("_points").Constrain("99") 09 Dim dt1 As DateTime = DateTime.UtcNow 10 Dim result As IObjectSet = query.Execute() 11 Dim dt2 As DateTime = DateTime.UtcNow 12 Dim diff As TimeSpan = dt2 - dt1 13 Console.WriteLine("Test 3: index on pilot") 14 Console.WriteLine("Execution time=" + diff.Milliseconds.ToString() + " ms") 15 ListResult(result) 16 Finally 17 db.Close() 18 End Try 19 End Sub

IndexedExample.cs: PointsIndex
01public static void PointsIndex() { 02 Db4oFactory.Configure().ObjectClass(typeof(Car)).ObjectField("_pilot").Indexed(false); 03 Db4oFactory.Configure().ObjectClass(typeof(Pilot)).ObjectField("_points").Indexed(true); 04 IObjectContainer db=Db4oFactory.OpenFile(YapFileName); 05 try { 06 IQuery query = db.Query(); 07 query.Constrain(typeof(Car)); 08 query.Descend("_pilot").Descend("_points").Constrain("99"); 09 10 DateTime dt1 = DateTime.UtcNow; 11 IObjectSet result = query.Execute(); 12 DateTime dt2 = DateTime.UtcNow; 13 TimeSpan diff = dt2 - dt1; 14 Console.WriteLine("Test 4: index on points"); 15 Console.WriteLine("Execution time="+diff.Milliseconds + " ms"); 16 ListResult(result); 17 } 18 finally { 19 db.Close(); 20 } 21 }

IndexedExample.vb: PointsIndex
01Public Shared Sub PointsIndex() 02 Db4oFactory.Configure().ObjectClass(GetType(Car)).ObjectField("_pilot").Indexed(False) 03 Db4oFactory.Configure().ObjectClass(GetType(Pilot)).ObjectField("_points").Indexed(True) 04 Dim db As IObjectContainer = Db4oFactory.OpenFile(YapFileName) 05 Try 06 Dim query As IQuery = db.Query() 07 query.Constrain(GetType(Car)) 08 query.Descend("_pilot").Descend("_points").Constrain("99") 09 Dim dt1 As DateTime = DateTime.UtcNow 10 Dim result As IObjectSet = query.Execute() 11 Dim dt2 As DateTime = DateTime.UtcNow 12 Dim diff As TimeSpan = dt2 - dt1 13 Console.WriteLine("Test 4: index on points") 14 Console.WriteLine("Execution time=" + diff.Milliseconds.ToString() + " ms") 15 ListResult(result) 16 Finally 17 db.Close() 18 End Try 19 End Sub

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.