You are here: Tuning > Main Operations Performance > Query Performance > Different Query Types

Different Query Types

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:

c#