When should I use a CompiledQuery?

Question

I have a table:

-- Tag

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

I have a class that will have the following methods:

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

Should I use a compiled query in this case?

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

Then my GetByName method would be:

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

Which generates a SELECT ID, Name FROM Tag and execute Where on the code. Or should I avoid CompiledQuery in this case?

Basically I want to know when I should use compiled queries. Also, on a website they are compiled only once for the entire application?

Answer 1

You should use a CompiledQuery when all of the following are true:

• The query will be executed more than once, varying only by parameter values.
• The query is complex enough that the cost of expression evaluation and view generation is "significant" (trial and error)
• You are not using a LINQ feature like IEnumerable<T>.Contains() which won't work with CompiledQuery.
• You have already simplified the query, which gives a bigger performance benefit, when possible.
• You do not intend to further compose the query results (e.g., restrict or project), which has the effect of "decompiling" it.

CompiledQuery does its work the first time a query is executed. It gives no benefit for the first execution. Like any performance tuning, generally avoid it until you're sure you're fixing an actual performance hotspot.

2012 Update: EF 5 will do this automatically (see "Entity Framework 5: Controlling automatic query compilation") . So add "You're not using EF 5" to the above list.

Answer 2

compiled queries are more helpfull with linq queries with large expression trees say complex queries to gain performance over building expression tree again and again while reusing query. in your case i guess it will save a very little time.

Answer 3

Compiled queries offer a performance improvement, but it's not huge. If you have complex queries, I'd rather go with a stored procedure or a view, if possible; letting the database do it's thing might be a better approach.

Answer 4

Compiled queries are compiled when the application is compiled and every time you reuse a query often or it is complex you should definitely try compiled queries to make execution faster.

But I would not go for it on all queries as it is a little more code to write and for simple queries it might not be worthwhile.

But for maximum performance you should also evaluate Stored Procedures where you do all the processing on the database server, even if Linq tries to push as much of the work to the db as possible you will have situations where a stored procedure will be faster.

Answer 5

Compiled queries save you time, which would be spent generating expression trees. If the query is used often and you'll save the compiled query, you should definitely use it. I had many cases when the query parsing took more time than the actual round trip to the database.

In your case, if you are sure that it would generate SELECT ID, Name FROM Tag without the WHERE case (which I doubt, as your AllQueries function should return IQueryable and the actual query should be made only after calling ToList) - you shouldn't use it.

As someone already mentioned, on bigger tables SELECT * FROM [someBigTable] would take very long and you'll spend even more time filtering that on the client side. So you should make sure that your filtering is made on the database side, no matter if you are using compiled queries or not.