db4o provides different query syntaxes: Query By Example,
SODA, Native Queries and LINQ (for .NET 3.5 and higher). Under the hood all these syntaxes are
converted to SODA. The conversion can be very straightforward (as in case of
QBE), or pretty sophisticated (some Native queries). The fact that conversion
takes place and can be more or less complex affects the performance of queries
expressed with different syntax. Another factor affecting the performance can
be using unoptimized Native Queries: this can happen if the query is too
complex to analyze or when optimization is disabled through configuration.
Optimization is also applicable to LINQ queries, i.e. some of LINQ queries are
currently too complex to analyze and optimize. In cases when optimization does
not happen, the query is run against all instances of an object in the database,
which is quite slow and consumes a lot of RAM.
QueryPerformanceBenchmark.cs: RunDifferentQueriesTest private void RunDifferentQueriesTest() { Init(); Clean(); System.Console.WriteLine("Storing objects as a bulk:"); Open(Configure()); Store(); Close(); Open(Configure()); // System.Console.WriteLine("Query by example:"); StartTimer(); Item item = (Item)objectContainer.QueryByExample( new Item("level1/1", null)).Next(); StopTimer("Select 1 object QBE: " + item._name); // System.Console.WriteLine("SODA:"); StartTimer(); IQuery query = objectContainer.Query(); query.Constrain(typeof(Item)); query.Descend("_name").Constrain("level1/1"); item = (Item)query.Execute().Next(); StopTimer("Select 1 object SODA: " + item._name); // System.Console.WriteLine("LINQ: "); StartTimer(); var resultLINQ = from Item it in objectContainer where it._name.Equals("level1/1") select it; IEnumerator<Item> i = resultLINQ.GetEnumerator(); if (i.MoveNext()) { item = i.Current; StopTimer("Select 1 object LINQ: " + item._name); } // System.Console.WriteLine("Native Query:"); StartTimer(); IList<Item> result = objectContainer.Query<Item>(delegate(Item it) { return it._name.Equals("level1/1"); }); item = result[0]; StopTimer("Select 1 object NQ: " + item._name); Close(); // Open(ConfigureUnoptimizedNQ()); System.Console.WriteLine("Native Query Unoptimized:"); StartTimer(); result = objectContainer.Query<Item>(delegate(Item it) { return it._name.Equals("level1/1"); }); item = result[0]; StopTimer("Select 1 object NQ: " + item._name); Close(); }
QueryPerformanceBenchmark.cs: Init private void Init() { _filePath = "performance.db4o"; // amount of objects _count = 10000; // depth of objects _depth = 3; _isClientServer = false; }
QueryPerformanceBenchmark.cs: Configure private IConfiguration Configure() { IConfiguration config = Db4oFactory.NewConfiguration(); return config; }
The following results were obtained on a test machine:
Storing objects as a bulk:
Store 30000 objects: 5337 ms
Query by example:
Select 1 object QBE: level1/1: 1021 ms
SODA:
Select 1 object SODA: level1/1: 809 ms
LINQ:
Select 1 object LINQ: level1/1: 915 ms
Native Query:
Select 1 object NQ:
level1/1: 1604 ms
Native Query Unoptimized:
Select 1 object NQ: level1/1: 5008 ms
You can see that SODA query shows the best performance. The other query types are less performant due to conversion, however they can be easier to support and refactor. The worst performance is shown in the case of unoptimized Native Query: in this case all the objects from the database were instantiated and tested against the constraint.
Download example code: