什么时候应该使用CompiledQuery?

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:

那么我的GetByName方法将是:

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?

它从标记生成一个SELECT ID、名称并在代码上执行。在这种情况下，我应该避免使用CompiledQuery吗?

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

基本上，我想知道什么时候应该使用编译查询。而且，在一个网站上，整个应用程序只编译一次?

5 个解决方案

#1

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

当所有以下内容都为真时，您应该使用CompiledQuery:

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.
您没有使用像IEnumerable - T>. contains()这样的LINQ特性，它不能处理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.
您不打算进一步组合查询结果(例如，limit或project)，它具有“反编译”的效果。

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.

CompiledQuery在第一次执行查询时执行工作。第一次执行没有任何好处。与任何性能调优一样，通常要避免调优，直到您确定正在修复一个实际的性能热点。

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.

2012年更新:EF 5将自动执行此操作(参见“实体框架5:控制自动查询编译”)。所以在上面的列表中加上“你没有使用EF 5”。

#2

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.

在您的示例中，如果您确信它将生成SELECT ID，那么它将从标记中生成名称，而不带WHERE大小写(我对此表示怀疑，因为AllQueries函数应该返回IQueryable，实际查询应该在调用ToList之后进行)—您不应该使用它。

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.

正如有人已经提到的，在较大的表[someBigTable]中选择*将花费很长时间，您将在客户端花费更多时间来过滤它。因此，无论是否使用编译查询，都应该确保在数据库端进行过滤。

#3

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.

使用带有大型表达式树的linq查询，编译后的查询更有帮助，在重用查询的同时，通过不断构建表达式树来获得性能。就你的情况而言，我想这能节省一点时间。

#4

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.

编译查询提供了性能改进，但并不是很大。如果您有复杂的查询，我宁愿使用存储过程或视图(如果可能的话);让数据库这样做也许是更好的方法。

#5

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.

但是为了获得最大的性能，您还应该对存储过程进行评估，在这些存储过程中，您可以在数据库服务器上进行所有的处理，即使Linq试图将尽可能多的工作推到db上，您也会遇到存储过程速度更快的情况。

#1