Why doesn't this CompiledQuery give a performance improvement?

Question

I am trying to speed up an often used query. Using a CompiledQuery seemed to be the answer. But when I tried the compiled version, there was no difference in performance between the compiled and non-compiled versions.

Can someone please tell me why using Queries.FindTradeByTradeTagCompiled is not faster than using Queries.FindTradeByTradeTag?

static class Queries
{
    // Pre-compiled query, as per http://msdn.microsoft.com/en-us/library/bb896297
    private static readonly Func<MyEntities, int, IQueryable<Trade>> mCompiledFindTradeQuery =
        CompiledQuery.Compile<MyEntities, int, IQueryable<Trade>>(
            (entities, tag) => from trade in entities.TradeSet
                               where trade.trade_tag == tag
                               select trade);

    public static Trade FindTradeByTradeTagCompiled(MyEntities entities, int tag)
    {
        IQueryable<Trade> tradeQuery = mCompiledFindTradeQuery(entities, tag);

        return tradeQuery.FirstOrDefault();
    }

    public static Trade FindTradeByTradeTag(MyEntities entities, int tag)
    {
        IQueryable<Trade> tradeQuery = from trade in entities.TradeSet
                                       where trade.trade_tag == tag
                                       select trade;

        return tradeQuery.FirstOrDefault();
    }
}

This link may help http://msdn.microsoft.com/en-us/magazine/ee336024.aspx — orandov, Apr 12 '10 at 23:49

Grammarian · Accepted Answer · 2010-04-13T01:16:24.523

Thanks to orandov, I found the answer here (at the end). If you make any changes to the query, the precompiled statement is discarded. In my case, FirstOrDefault() was changing the underlying query.

Solution was to call AsEnumerable() on the query first. By calling AsEnumerable() the precompiled query was protected, and FirstOrDefault() was executed locally on the results (it was called against Linq.Enumerable.FirstOrDefault rather than Linq.Queryable.FirstOrDefault).

Net result: execution time was reduced from 45ms to 4ms. 11x faster.

public static Trade FindTradeByTradeTagCompiled(MyEntities entities, int tag)
{
    IQueryable<Trade> tradeQuery = mCompiledFindTradeQuery(entities, tag);

    return tradeQuery.AsEnumerable().FirstOrDefault();
}

Marc Gravell · Answer 2 · 2010-04-13T08:31:31.207

Rather than AsEnumerable (which won't limit the results at the database), have you tried:

// Pre-compiled query, as per http://msdn.microsoft.com/en-us/library/bb896297
private static readonly Func<MyEntities, int, IQueryable<Trade>> mCompiledFindTradeQuery =
    CompiledQuery.Compile<MyEntities, int, IQueryable<Trade>>(
        (entities, tag) => (from trade in entities.TradeSet
                           where trade.trade_tag == tag
                           select trade).Take(1));

score 4 · Answer 3 · answered Apr 12 '10 at 23:52

4

Queries are always "compiled" it's just that if you don't use the CompiledQuery then it'll be compiled on-demand. Also, the CompiledQuery is only compiled the first time it's executed anyway (the difference being that a CompiledQuery is only compiled once, whereas the 'regular' way will be compiled each time). For such a simple query like the one you've got, the overhead of compiling is probably quite small.

Do you have an index on the trade_tag field? That'll provide you with the biggest performance increase.

answered Apr 12 '10 at 23:52

Dean Harding

71,468
13
145
180

Thank you for your general comments, but you have not answered the question at all. Yes, there is an index on trade_tag. If the query is called 100,000 times (as it will be), the overhead is not "quite small". – Grammarian Apr 13 '10 at 00:37
I think this answers the question very well, even if the FirstOrDefault was the actual problem. The question never asked why there's no performance improvement in 100,000 calls - it was just why is the compiled not faster than the not compiled. Thus the answer "they are actually both compiled" is quite fitting. – joniba May 24 '11 at 17:09

score 0 · Answer 4 · edited May 23 '17 at 12:08

Instead of returning IQueryable, just set up the compiled query to return the single Trade object directly. This is a lot cleaner code than the previous solutions.

// Pre-compiled query, as per http://msdn.microsoft.com/en-us/library/bb896297
private static readonly Func<MyEntities, int, Trade> mCompiledFindTradeQuery =
    CompiledQuery.Compile<MyEntities, int, Trade>(
        (entities, tag) => (from trade in entities.TradeSet
                           where trade.trade_tag == tag
                           select trade).FirstOrDefault() );

public static Trade FindTradeByTradeTagCompiled(MyEntities entities, int tag)
{
    return mCompiledFindTradeQuery(entities, tag);
}

Another example is here: Linq to SQL to Linq compiled performance

Why doesn't this CompiledQuery give a performance improvement?

4 Answers4