LINQ to SQL CompiledQuery Slowing Down

Question

I'm trying to use a CompiledQuery 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.

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 CompileQuery.

Below is some code I threw together in LINQPad:

// A list of search terms
List<string> keywords = new List<string>()
{
    "almond",
    "banana",
    "chocolate",
    "date",
    "elderberry",
};

// Searches for each keyword in the database
void Main()
{
    int i = 0;

    while (i < keywords.Count)
    {
        Stopwatch timer = Stopwatch.StartNew();

        IQueryable<Result> 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<TypedDataContext, string, IQueryable<Result>> CQ
{
    get
    {
        return CompiledQuery.Compile<TypedDataContext, string, IQueryable<Result>>
        (
            (TypedDataContext dc, string query) =>    
            (
                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; }
}

Of course this is overly simplified, but you get the idea. Now the results produced are:

Query: almond
Time: 14ms

Query: banana
Time: 1197ms

Query: chocolate
Time: 1191ms

Query: date
Time: 1226ms

Query: elderberry
Time: 1201ms

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.

I'm sure it's something obvious, but I'm not sure what I'm missing. Any pointers?

Many thanks in advance!

Answer 1

Try saving the delegate result of the query compilation to a static backing field. You're likely recompiling everytime you access your property. Not sure why the first execution is so fast. It's not possible its something data related is it?