我有一个动态查询,太大了,无法放在这里.可以肯定地说,在它的当前形式中,它利用CLR过程根据传递的搜索参数的数量动态地构建连接,然后获取该结果并将其连接到更详细的表以带回对最终用户重要的属性.我已经将整个查询转换为LINQ to Entities,我发现它生成的SQL足以完成这项工作,无论是通过EF 6运行,查询超时.获取生成的SQL并在SSMS中运行它将在3秒或更短的时间内运行.我只能想象我的问题是参数嗅探.我已经尝试更新数据库中每个表的统计信息,但这还没有解决问题.
我的问题是:
我可以通过EF以某种方式嵌入像"OPTION RECOMPILE"这样的选项吗?
I like VahidN's solution, do up vote him, but I want more control of when it happens. It turns out that DB Interceptors are very global, and I only wanted this to happen on specific contexts in specific scenarios.
Here we are setting the ground work to also support adding other query hints, that could be turned on and off as desired.
Since I often expose the method for passing a connection string, I also included support for that.
Below would give your context a flag to enable/disable the hint programatically, by extending the partial class EF generates. We also threw the small piece of reused code in the Interceptor into its own method.
Small Interface
public interface IQueryHintable { bool HintWithRecompile { get; set; } }
DB Command Interceptor
public class OptionHintDbCommandInterceptor : IDbCommandInterceptor { public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext) { AddHints(command, 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) { AddHints(command, interceptionContext); } public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { } public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { AddHints(command, interceptionContext); } private static void AddHints<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext) { var context = interceptionContext.DbContexts.FirstOrDefault(); if (context is IQueryHintable) { var hints = (IQueryHintable)context; if (hints.HintWithRecompile) { addRecompileQueryHint(command); } } } private static void addRecompileQueryHint(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)"; } } }
Extending Entity Context to Add IQueryHintable
public partial class SomeEntities : DbContext, IQueryHintable { public bool HintWithRecompile { get; set; } public SomeEntities (string connectionString, bool hintWithRecompile) : base(connectionString) { HintWithRecompile = hintWithRecompile; } public SomeEntities (bool hintWithRecompile) : base() { HintWithRecompile = hintWithRecompile; } public SomeEntities (string connectionString) : base(connectionString) { } }
Register DB Command Interceptor (global.asax)
DbInterception.Add(new OptionHintDbCommandInterceptor());
Enable context wide
using(var db = new SomeEntities(hintWithRecompile: true) ) { }
打开或关闭
db.HintWithRecompile = true; // Do Something db.HintWithRecompile = false;
我将其称为HintWithRecompile,是因为您可能还想实现HintOptimizeForUnknown或其他查询提示。
在DB上执行它们之前,可以使用EF6的拦截功能来操作其内部SQL命令,例如option(recompile)
在命令末尾添加:
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)"; } } }
要使用它,请在应用程序的开头添加以下行:
DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());