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
01private static void FillUpDB() 02 { 03 File.Delete(Db4oFileName); 04 IObjectContainer db=Db4oFactory.OpenFile(Db4oFileName); 05 try { 06 for (int i=0; i<10000;i++){ 07 AddCar(db,i); 08 } 09 } 10 finally { 11 db.Close(); 12 } 13 }

IndexedExample.vb: FillUpDB
01Private Shared Sub FillUpDB() 02 File.Delete(Db4oFileName) 03 Dim db As IObjectContainer = Db4oFactory.OpenFile(Db4oFileName) 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
1Private 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
01private static void NoIndex() { 02 IObjectContainer db=Db4oFactory.OpenFile(Db4oFileName); 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
01Private Shared Sub NoIndex() 02 Dim db As IObjectContainer = Db4oFactory.OpenFile(Db4oFileName) 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
01private static void FullIndex() 02 { 03 IConfiguration configuration = Db4oFactory.NewConfiguration(); 04 configuration.ObjectClass(typeof(Car)).ObjectField("_pilot").Indexed(true); 05 configuration.ObjectClass(typeof(Pilot)).ObjectField("_points").Indexed(true); 06 IObjectContainer db = Db4oFactory.OpenFile(configuration, Db4oFileName); 07 try { 08 IQuery query = db.Query(); 09 query.Constrain(typeof(Car)); 10 query.Descend("_pilot").Descend("_points").Constrain("99"); 11 12 DateTime dt1 = DateTime.UtcNow; 13 IObjectSet result = query.Execute(); 14 DateTime dt2 = DateTime.UtcNow; 15 TimeSpan diff = dt2 - dt1; 16 Console.WriteLine("Test 2: index on pilot and points"); 17 Console.WriteLine("Execution time="+diff.Milliseconds + " ms"); 18 ListResult(result); 19 } 20 finally { 21 db.Close(); 22 } 23 }

IndexedExample.vb: FullIndex
01Private Shared Sub FullIndex() 02 Dim configuration As IConfiguration = Db4oFactory.NewConfiguration() 03 configuration.ObjectClass(GetType(Car)).ObjectField("_pilot").Indexed(True) 04 configuration.ObjectClass(GetType(Pilot)).ObjectField("_points").Indexed(True) 05 Dim db As IObjectContainer = Db4oFactory.OpenFile(configuration, Db4oFileName) 06 Try 07 Dim query As IQuery = db.Query() 08 query.Constrain(GetType(Car)) 09 query.Descend("_pilot").Descend("_points").Constrain("99") 10 Dim dt1 As DateTime = DateTime.UtcNow 11 Dim result As IObjectSet = query.Execute() 12 Dim dt2 As DateTime = DateTime.UtcNow 13 Dim diff As TimeSpan = dt2 - dt1 14 Console.WriteLine("Test 2: index on pilot and points") 15 Console.WriteLine("Execution time=" + diff.Milliseconds.ToString() + " ms") 16 ListResult(result) 17 Finally 18 db.Close() 19 End Try 20 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
01private static void PilotIndex() 02 { 03 IConfiguration configuration = Db4oFactory.NewConfiguration(); 04 configuration.ObjectClass(typeof(Car)).ObjectField("_pilot").Indexed(true); 05 configuration.ObjectClass(typeof(Pilot)).ObjectField("_points").Indexed(false); 06 IObjectContainer db=Db4oFactory.OpenFile(configuration, Db4oFileName); 07 try { 08 IQuery query = db.Query(); 09 query.Constrain(typeof(Car)); 10 query.Descend("_pilot").Descend("_points").Constrain("99"); 11 12 DateTime dt1 = DateTime.UtcNow; 13 IObjectSet result = query.Execute(); 14 DateTime dt2 = DateTime.UtcNow; 15 TimeSpan diff = dt2 - dt1; 16 Console.WriteLine("Test 3: index on pilot"); 17 Console.WriteLine("Execution time="+diff.Milliseconds + " ms"); 18 ListResult(result); 19 } 20 finally { 21 db.Close(); 22 } 23 }

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

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

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