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)
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:
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 ):
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
}
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
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
}
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.
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
}
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:
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
}
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:
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
}
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
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
}
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)
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.