You are here: Basics Operations & Concepts > Querying > LINQ > LINQ Optimization

LINQ Optimization

LINQ queries are converted to SODA Query under the hood. The downside of this is that some of the queries cannot be converted to SODA. In this case db4o falls back to the plain LINQ to objects implementation. This means that all objects are instantiated and the query is ran against the objects. This of course is slower by an order of magnitude.

Note that db4o needs the Mono.Reflection.dll-assembly for this optimization. On the .NET compact framework you Mono.Cecil.dll- and Cecil.FlowAnalysis.dll-assembly. See "LINQ For Compact Framework"

For example a simple query like this can be optimized:

var adults = from Person p in container
             where p.Age > 18 && p.Age < 70
             orderby p.Name
             select p;
LinqExamples.cs: A query which is optimizable
Dim adults = From p As Person In container _
 Where p.Age > 18 AndAlso p.Age < 70 _
 Order By p.Name _
 Select p
LinqExamples.vb: A query which is optimizable

However, queries which invoke operations on the objects cannot be optimized:

var adults = from Person p in container
             where p.Name.ToLowerInvariant().Equals("joe")
             select p;
LinqExamples.cs: Unoptimizable query, because of the 'operations' withing the query
Dim adults = From p As Person In container _
 Where p.Name.ToLowerInvariant().Equals("joe") _
 Select p
LinqExamples.vb: Unoptimizable query, because of the 'operations' withing the query

Detect Unoptimized Queries

In Visual Studio, you can see a message in the debugger-output for each unoptimized query. First open the debug output window (Debug->Windows->Output). Then run your application. A query which cannot be optimized will produce this message:

'A first chance exception of type 'Db4objects.Db4o.Linq.QueryOptimizationException' occurred in Db4objects.Db4o.Linq.dll'

To find it out for a particular query, break before the query. Then step over the query and see if the message has been printed out.

For a broader picture, you can also use db4o's monitoring support. This will report the amount of unoptimized queries per second.

Improve Unoptimizable Queries

In cases where you have queries which cannot optimized it's often possible to split the query in two parts. The first part runs optimized. After that you run a regular LINQ to Object query to do the rest of the job. Let's look a this example:

var adults = from Person p in container
             where p.Age > 18 && p.Age < 70
                    && p.Name.Substring(2).Contains("n")
             select p;
LinqExamples.cs: Unoptimizable query
Dim adults = From p As Person In container _
 Where p.Age > 18 AndAlso p.Age < 70 AndAlso p.Name.Substring(2).Contains("n") _
 Select p
LinqExamples.vb: Unoptimizable query

In this example the part which calls the substring-operation cannot be optimized. Therefore the query runs very slow on large data sets. But the rest of the query could be optimized. So lets split this query into two parts:

var optimizedPart = from Person p in container
                    where p.Age > 18 && p.Age < 70
                    select p;
var endResult = from p in optimizedPart.AsEnumerable()
              where p.Name.Substring(2).Contains("n")
              select p;
LinqExamples.cs: Splitting into two parts
Dim optimizedPart = From p As Person In container _
 Where p.Age > 18 AndAlso p.Age < 70 _
 Select p
Dim endResult = From p In optimizedPart.AsEnumerable() _
 Where p.Name.Substring(2).Contains("n") _
 Select p
LinqExamples.vb: Splitting into two parts

The first query contains only the parts which can be optimized. After that, use the AsEnumerable()-operator to force to run the rest of the query with LINQ to objects. This splitting will improve the performance significantly, since parts of the operation run as optimized query.