重写LINQ Expression查询以启用缓存SQL执行计划 [英] Rewriting a LINQ Expression query to enable caching SQL Execution Plan

问题描述

在阅读实体框架性能，我遇到了以下信息:

While reading an article on Entity Framework performance, I came across this piece of information:

第二，问题 [SQL Server将不会重用执行计划] 首先是因为将int传递给Skip()和Take()时(由于实现细节) )方法，则Entity Framework无法查看它们是否传递了诸如Take(100)之类的绝对值，还是诸如Take(resultsPerPage)之类的变量，因此它不知道该值是否应该参数化.

Secondly, the problem [SQL Server won’t reuse the execution plan] occurs in the first place because (due to an implementation detail) when passing an int to the Skip() and Take() methods, Entity Framework can’t see whether they were passed absolute values like Take(100), or a variable like Take(resultsPerPage), so it doesn’t know whether the value should be parameterized.

建议的解决方案是更改这种代码样式:

The proposed solution is to change this style of code:

var schools = db.Schools
    .OrderBy(s => s.PostalZipCode)
    .Skip(model.Page * model.ResultsPerPage)
    .Take(model.ResultsPerPage)
    .ToList();

以这种风格:

int resultsToSkip = model.Page * model.ResultsPerPage;
var schools = db.Schools
    .OrderBy(s => s.PostalZipCode)
    .Skip(() => resultsToSkip) //must pre-calculate this value
    .Take(() => model.ResultsPerPage)
    .ToList();

这可以使Entity Framework知道这些是变量，并且应该对生成的SQL进行参数化，从而可以重用执行计划.

Which allows Entity Framework to know that these are variables and that the generated SQL should be parametrized, which in turn allows the execution plan to be reused.

我们的应用程序中有一些代码以相同的方式使用变量，但是由于类型未知，我们必须在运行时构建表达式.

We have some code in our application that uses variables in the same way, but we must build the Expression at runtime as the type is not known in advance.

这是以前的样子:

var convertedId = typeof(T).GetConvertedIdValue(id);
var prop = GetIdProperty(typeof(T));

var itemParameter = Expression.Parameter(typeof(T), "item");
var whereExpression = Expression.Lambda<Func<T, bool>>
    (
    Expression.Equal(
        Expression.Property(
            itemParameter,
            prop.Name
            ),
        Expression.Constant(convertedId)
        ),
    new[] { itemParameter }
    );

return Get<T>().Where(whereExpression);

问题是使用Expression.Constant(convertedId)会导致将常量插入到生成的SQL中.这将导致您查找的每个新项目的SQL发生更改，这将停止所有执行计划缓存:

The problem is that using Expression.Constant(convertedId) causes a constant to be inserted in to the generated SQL. This causes the SQL to change for every new item you look up, which stops any execution plan caching:

WHERE [Extent1].[Id] = 1234

和:

WHERE [Extent1].[Id] = 1235

和:

WHERE [Extent1].[Id] = 1236

然后的问题是，如何使用表达式构建来强制生成的SQL进行参数化? () => convertedId语法将不起作用.我已经在下面回答了这个问题.

The question then, is How can you use Expression building in such a way as to force the parametrization of the generated SQL? The () => convertedId syntax will not work. I have answered this below.

推荐答案

经过反复试验，我们发现您仍然可以通过稍微改变传入的方式来强制Entity Framework将convertedId识别为参数:

After a lot of trial and error, we found you can still force Entity Framework to recognise convertedId as a parameter by slightly changing how we pass it in:

....

var convObj = new
{
    id = convertedId
};
var rightExp = Expression.Convert(Expression.Property(Expression.Constant(convObj), "id"), convertedId.GetType());

var whereExpression = Expression.Lambda<Func<T, bool>>
    (
    Expression.Equal(
        Expression.Property(
            itemParameter,
            prop.Name
            ),
        rightExp
        ),
    new[] { itemParameter }
    );

return Get<T>().Where(whereExpression);

这会导致生成的SQL对任何给定的id使用相同的参数(和代码):

Which causes the generated SQL to use the same parameter (and code) for any given id:

WHERE [Extent1].[Id] = @p__linq__0 

我们正在处理的问题查询花费了很长时间才能生成执行计划，因此我们发现访问新ID的执行时间显着减少(从3到4秒减少到300毫秒)

The query in question that we were dealing with takes a long time to generate the execution plan, so we saw a significant decrease in execution time for accessing new IDs (from 3~4 seconds down to ~300 milliseconds)

这篇关于重写LINQ Expression查询以启用缓存SQL执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！