Databases
StackOverflow2013
Postsdbo
POLINQ to SQL CompiledQuery Slowing Down
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLINQ to SQL CompiledQuery Slowing Down
    text
    copied!<p>I'm trying to use a <code>CompiledQuery</code> in LINQ to SQL (WP7, C# and a SQLCE 3.5 database), but after the first use the query slows down to uncompiled speeds. I'm new to this, and I'm sure I've missed something obvious, but I'm not sure what.</p> <p>As context, I have a fairly large database of terms (about 100,000 records), and I want to search this database. After trying various different approaches and optimisations, my queries were still very slow, hence why I considered using <code>CompileQuery</code>.</p> <p>Below is some code I threw together in LINQPad:</p> <pre><code>// A list of search terms List&lt;string&gt; keywords = new List&lt;string&gt;() { "almond", "banana", "chocolate", "date", "elderberry", }; // Searches for each keyword in the database void Main() { int i = 0; while (i &lt; keywords.Count) { Stopwatch timer = Stopwatch.StartNew(); IQueryable&lt;Result&gt; r = CQ(this, keywords[i]); timer.Stop(); Console.WriteLine("Query: {0}\nTime: {1}ms\n", query, timer.ElapsedMilliseconds); i++; } } // The compiled query property static Func&lt;TypedDataContext, string, IQueryable&lt;Result&gt;&gt; CQ { get { return CompiledQuery.Compile&lt;TypedDataContext, string, IQueryable&lt;Result&gt;&gt; ( (TypedDataContext dc, string query) =&gt; ( from x in dc.MyTable where x.MyColumn.Contains(query) select new Result { Something = x.MyColumn } ) ); } } // A simple class to hold the results class Result { public string Something { get; set; } } </code></pre> <p>Of course this is overly simplified, but you get the idea. Now the results produced are:</p> <pre><code>Query: almond Time: 14ms Query: banana Time: 1197ms Query: chocolate Time: 1191ms Query: date Time: 1226ms Query: elderberry Time: 1201ms </code></pre> <p>What everyone says is that the first query will be slower, but subsequent queries will be faster. However in my case it's the opposite: it looks like the first query is compiled but the latter ones are not.</p> <p>I'm sure it's something obvious, but I'm not sure what I'm missing. Any pointers?</p> <p>Many thanks in advance!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload