EF 6 参数嗅探 [英] EF 6 Parameter Sniffing

问题描述

我有一个动态查询太大而无法放在这里.可以肯定地说，在它的当前形式中，它利用 CLR 过程根据传递的搜索参数的数量动态构建连接，然后获取该结果并将其连接到更详细的表，以带回对最终用户很重要的属性.我已将整个查询转换为 LINQ to Entities，我发现它生成的 SQL 的效率足以完成这项工作，但是通过 EF 6 运行，查询超时.获取生成的 SQL 并在 SSMS 中运行它只需 3 秒或更短的时间.我只能想象我的问题是参数嗅探.我尝试更新数据库中每个表的统计信息，但这并没有解决问题.

I have a dynamic query that is just too large to put here. Safe to say that in it's current form it utilizes a CLR procedure to dynamically build joins based upon the number of search parameters passed then takes that result and joins it to more detailed tables to bring back attributes important to the end-user. I have converted the entire query into LINQ to Entities and what I have found is that the SQL that it produces is efficient enough to do the job, however running via EF 6, the query timesout. Taking the resulting SQL and running it in SSMS runs in 3 or less seconds. I can only imagine that my problem is parameter sniffing. I have tried updating statistics on every table in the database and this has not solved the problem.

我的问题是:

我可以通过 EF 以某种方式嵌入诸如OPTION RECOMPILE"之类的选项吗?

Can I somehow embed options like an "OPTION RECOMPILE" via EF?

推荐答案

可以利用 EF6 的拦截功能在 DB 上执行之前操作其内部 SQL 命令，例如添加 option(recompile)在命令的末尾:

It's possible to use the interception feature of EF6 to manipulate its internal SQL commands before executing them on DB, for instance adding option(recompile) at the end of the command:

public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        addQueryHint(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        addQueryHint(command);
    }

    private static void addQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

要使用它，请在应用程序的开头添加以下行:

To use it, add the following line at the beginning of the application:

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

这篇关于EF 6 参数嗅探的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！