When is it OK to utilize a CompiledQuery?

asp.net c# compiled-query entity-framework

Question

I possess a table.

-- Tag

ID  | Name
-----------
1   | c#
2   | linq
3   | entity-framework

The following methods will be included in my class:

IEnumerable<Tag> GetAll();
IEnumerable<Tag> GetByName();

In this situation, should I use a constructed query?

static readonly Func<Entities, IEnumerable<Tag>> AllTags =
    CompiledQuery.Compile<Entities, IEnumerable<Tag>>
    (
        e => e.Tags
    );

next, myGetByName approach would be:

IEnumerable<Tag> GetByName(string name)
{
    using (var db = new Entities())
    {
        return AllTags(db).Where(t => t.Name.Contains(name)).ToList();
    }
}

This results in aSELECT ID, Name FROM Tag and carry outWhere inside the code. Or, should I abstain?CompiledQuery if so, how?

In essence, I'm curious about when to utilize compiled queries. Additionally, are they just only compiled once for the full program on a website?

1
22
2/8/2011 12:04:45 PM

Accepted Answer

If possible, utilize aCompiledQuery when all three conditions are met:

  • The only thing that will change across executions of the query are the parameter values.
  • The cost of expression evaluation and view creation is "substantial" due to the query's complexity (trial and error)
  • You are not using a LINQ functionality like asIEnumerable<T>.Contains() which is incompatible withCompiledQuery .
  • When it is feasible, you have already streamlined the query, which improves speed more.
  • You do not plan to "decompile" the output of the query (using a tool like restrict or project, for example).

CompiledQuery performs its function on the initial query execution. For the first execution, it offers no advantages. Like any performance optimization, it's best to hold off unless you're certain you're addressing a real performance problem.

Updated in 2012: This will be carried out automatically by EF 5 (see "Controlling automated query compilation with Entity Framework 5"). So, to the list above, add "You're not using EF 5".

31
10/18/2012 7:51:29 PM

Popular Answer

You save time by using compiled queries instead of creating expression trees. Use it if the query is often used and you plan to store the built query. In many instances, the time it took to parse the query was longer than the actual round trip to the database.

If you know it would have positive results in your situation,SELECT ID, Name FROM Tag lacking theWHERE (which I doubt given that yourAllQueries function must yieldIQueryable and only after phoning should the real inquiry be made.ToList ) — you ought not to use it.

Earlier, someone remarked that on larger tablesSELECT * FROM [someBigTable] would take a long time, and filtering it on the client side would take much longer. Consequently, regardless of whether you use compiled queries or not, you should make sure that your filtering is done on the database side.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow