<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns:MadCap="http://www.madcapsoftware.com/Schemas/MadCap.xsd" MadCap:lastBlockDepth="2" MadCap:lastHeight="120" MadCap:lastWidth="624" MadCap:disableMasterStylesheet="true" MadCap:tocPath="Basics Operations & Concepts|Querying|LINQ" MadCap:InPreviewMode="false" MadCap:RuntimeFileType="Topic" MadCap:TargetType="WebHelp" MadCap:PathToHelpSystem="../../../../" MadCap:HelpSystemFileName="index.xml" MadCap:SearchType="Stem">
<head><title>Optimization </title>
<script type="text/javascript">/* <![CDATA[ */
window.onload = function(){
var pathToFlash = $('html').attr('MadCap:PathToHelpSystem') + 'Content/Resources/Code/ZeroClipboard.swf';
ZeroClipboard.setMoviePath(pathToFlash);
function bindToClipBord(element,content){
var clip = new ZeroClipboard.Client();
clip.setText(content);
clip.glue(element);
};
if(location.protocol==='file:'){
$('.copylink-marker').remove();
} else{
$('.copylink-marker').each(function(){
var text = $(this).parent().parent().children('.prettyprint').html();
$(this).hover(function(){
bindToClipBord(this,text);
},
function(){});
});
}
prettyPrint();
};
/* ]]> */</script>
<link href="../../../SkinSupport/MadCap.css" rel="stylesheet" />
<link href="../../../Resources/Stylesheets/OnlineStyle.css" rel="stylesheet" />
<script src="../../../Resources/Code/prettify.js">
</script>
<script src="../../../Resources/Code/lang-vb.js">
</script>
<script src="../../../Resources/Code/jquery.min.js">
</script>
<script src="../../../Resources/Code/ZeroClipboard.js">
</script>
<script src="../../../SkinSupport/MadCapAll.js" type="text/javascript">
</script>
</head>
<body>
<p class="MCWebHelpFramesetLink" style="display: none;"><a href="../../../../index_CSH.html#basics/querying/linq/optimization.htm" style="">Open topic with navigation</a>
</p>
<div class="MCBreadcrumbsBox"><span class="MCBreadcrumbsPrefix">You are here: </span><a class="MCBreadcrumbsLink" href="../../../basics.htm">Basics Operations & Concepts</a><span class="MCBreadcrumbsDivider"> > </span><a class="MCBreadcrumbsLink" href="../../querying.htm">Querying</a><span class="MCBreadcrumbsDivider"> > </span><a class="MCBreadcrumbsLink" href="../linq.htm">LINQ</a><span class="MCBreadcrumbsDivider"> > </span><span class="MCBreadcrumbs">LINQ Optimization</span>
</div>
<p>
<script type="text/javascript">/*<![CDATA[*/document.write('<a href="' + location.href +'">');
document.write("Direct Link");
document.write('</a>');/*]]>*/</script>
</p>
<p>
</p>
<h1><a name="kanchor10"></a>LINQ Optimization</h1>
<p> LINQ queries are
converted to <a href="../soda_query.htm">SODA Query</a> 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.</p>
<p>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. <a href="linq_for_compact_framework.htm" target="" title="" alt="" class="MCXref" xrefformat="See "{paratext}"">See "LINQ For Compact Framework"</a></p>
<p>For example a simple query like this can be optimized:</p>
<div class="codesnippet" MadCap:conditions="Primary.c#">
<pre class="prettyprint" xml:space="preserve">var adults = from Person p in container
where p.Age > 18 && p.Age < 70
orderby p.Name
select p;</pre>
<div class="codesnippet-meta">LinqExamples.cs: A query which is optimizable
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-csharp.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<div class="codesnippet" MadCap:conditions="Primary.VB.NET">
<pre class="prettyprint lang-vb" MadCap:conditions="Primary.Online" xml:space="preserve">Dim adults = From p As Person In container _
Where p.Age > 18 AndAlso p.Age < 70 _
Order By p.Name _
Select p</pre>
<div class="codesnippet-meta">LinqExamples.vb: A query which is optimizable
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-vb.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<p>However, queries which invoke operations on the objects cannot be optimized:</p>
<div class="codesnippet" MadCap:conditions="Primary.c#">
<pre class="prettyprint" xml:space="preserve">var adults = from Person p in container
where p.Name.ToLowerInvariant().Equals("joe")
select p;</pre>
<div class="codesnippet-meta">LinqExamples.cs: Unoptimizable query, because of the 'operations' withing the query
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-csharp.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<div class="codesnippet" MadCap:conditions="Primary.VB.NET">
<pre class="prettyprint lang-vb" MadCap:conditions="Primary.Online" xml:space="preserve">Dim adults = From p As Person In container _
Where p.Name.ToLowerInvariant().Equals("joe") _
Select p</pre>
<div class="codesnippet-meta">LinqExamples.vb: Unoptimizable query, because of the 'operations' withing the query
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-vb.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<h2>Detect Unoptimized Queries</h2>
<p> 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:</p>
<p>'A first chance exception of type 'Db4objects.Db4o.Linq.QueryOptimizationException' occurred in Db4objects.Db4o.Linq.dll'</p>
<p>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.</p>
<p>For a broader picture, you can also use <a href="../../../tuning/runtime_statistics.htm">db4o's monitoring support</a>. This will report the amount of unoptimized queries per second.</p>
<h2>Improve Unoptimizable Queries </h2>
<p>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:</p>
<div class="codesnippet" MadCap:conditions="Primary.c#">
<pre class="prettyprint" xml:space="preserve">var adults = from Person p in container
where p.Age > 18 && p.Age < 70
&& p.Name.Substring(2).Contains("n")
select p;</pre>
<div class="codesnippet-meta">LinqExamples.cs: Unoptimizable query
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-csharp.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<div class="codesnippet" MadCap:conditions="Primary.VB.NET">
<pre class="prettyprint lang-vb" MadCap:conditions="Primary.Online" xml:space="preserve">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</pre>
<div class="codesnippet-meta">LinqExamples.vb: Unoptimizable query
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-vb.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<p>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:</p>
<div class="codesnippet" MadCap:conditions="Primary.c#">
<pre class="prettyprint" xml:space="preserve">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;</pre>
<div class="codesnippet-meta">LinqExamples.cs: Splitting into two parts
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-csharp.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<div class="codesnippet" MadCap:conditions="Primary.VB.NET">
<pre class="prettyprint lang-vb" MadCap:conditions="Primary.Online" xml:space="preserve">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</pre>
<div class="codesnippet-meta">LinqExamples.vb: Splitting into two parts
<div class="codedownload"><a href="../../../CodeExamples/query/linq/Example-query-linq-vb.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div>
</div>
<p>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.</p>
<script type="text/javascript" src="../../../SkinSupport/MadCapBodyEnd.js">
</script>
</body>
</html>
