Добавление подсказки запроса при вызове табличной функции

Question

Я вызываю табличную функцию из структуры сущностей, и мне нужно иметь возможность добавить option (recompile) к этому, потому что план выполнения, который это берет, не оптимален. Запустив запрос в SQL Server Management Studio, он будет выглядеть примерно так:

select 
       * 
from dbo.fDE_myquery(0, 0, 3309, '7/1/2013', '7/1/2014', 0, 0)
option (recompile)

От EF, нет никакого способа добавить эту подсказку, AFAIK. Часть EF выглядит примерно так:

var query = from f in ctx.fDE_myQuery(aBool, anotherBool, StartDate, 
            EndDate, someInt, moreBool)
            select f;

Я видел этот вопрос:

Как я могу контролировать сниффинг параметров и / или подсказки запросов в платформе сущностей?

Но оно устарело, и принятое решение не дает достаточной информации о том, как на самом деле реализовать предложенное решение (руководств по планам) с помощью структуры сущностей. Если это единственное решение, как вы можете заставить каркас сущностей использовать руководство плана в любом случае?

Answer 1

Я сталкивался с этим:

https://entityframework.codeplex.com/wikipage?title=Interception

И кажется, что вы можете сделать что-то вроде этого:

public class HintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        command.CommandText += " option (recompile)";
        base.ReaderExecuting(command, interceptionContext);
    }
}

И зарегистрируйте это так (я сделал это в Application_Start из global.asax.cs):

DbInterception.Add(new HintInterceptor());

И это позволит вам изменить CommandText, Единственная проблема заключается в том, что теперь он присоединен к каждому запросу читателя, что может быть проблемой, поскольку некоторые подсказки могут негативно повлиять на некоторые из них. Я предполагаю, что могу что-то сделать с контекстом, чтобы выяснить, является ли намек уместным или нет, или в худшем случае я мог бы изучить CommandText сам.

Не совсем кажется самым элегантным или мелкозернистым решением.

Редактировать: из interceptorContextВы можете получить DbContextsИтак, я определил интерфейс, который выглядит следующим образом:

public interface IQueryHintContext
{
    string QueryHint { get; set; }
    bool ApplyHint { get; set; }
}

А затем создал класс, который является производным от моего исходного DbContext (сгенерированного EF) и реализует вышеуказанный интерфейс. Затем я изменил свой перехватчик, чтобы выглядеть так:

public class HintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        if (interceptionContext.DbContexts.Any(db => db is Dal.IQueryHintContext))
        {
            var ctx = interceptionContext.DbContexts.First(db => db is Dal.IQueryHintContext) as Dal.IQueryHintContext;
            if (ctx.ApplyHint)
            {
                command.CommandText += string.Format(" option ({0})", ctx.QueryHint);
            }
        }
        base.ReaderExecuting(command, interceptionContext);
    }
}

Теперь, чтобы использовать его, я создаю контекст, используя мой производный класс вместо исходного QueryHint к тому, что я хочу, чтобы это было (recompile в этом случае) и установить ApplyHint прямо перед тем, как я выполню команду и впоследствии верну ей значение false.

Чтобы сделать все это немного более автономным, я определил интерфейс, подобный этому:

public interface IQueryHintContext
{
    string QueryHint { get; set; }
    bool ApplyHint { get; set; }
}

И расширил мой контекст БД следующим образом (вы можете, конечно, просто использовать частичный класс для расширения сгенерированного EF-класса):

public class MyEntities_Ext : MyEntities, IQueryHintContext
{
    public string QueryHint { get; set; }
    public bool ApplyHint { get; set; }
}

И затем, чтобы сделать часть включения-выключения немного проще в управлении, я определил это:

public class HintScope : IDisposable
{
    public IQueryHintContext Context { get; private set; }
    public void Dispose()
    {
        Context.ApplyHint = false;
    }

    public HintScope(IQueryHintContext context, string hint)
    {
        Context = context;
        Context.ApplyHint = true;
        Context.QueryHint = hint;
    }
}

Теперь, чтобы использовать это, я могу сделать это:

using (var ctx = new MyEntities_Ext()) 
{
    // any code that didn't need the query hint
    // ....
    // Now we want the query hint
    using (var qh = new HintScope(ctx, "recompile"))
    {
        // query that needs the recompile hint
    }
    // back to non-hint code
}

Это может быть немного излишним и может быть развито дальше (например, использование перечисления для доступных подсказок вместо строки - или создание подкласса recompile подсказка запроса, поэтому вам не нужно указывать строку recompile каждый раз и рискую опечаткой), но это решило мою непосредственную проблему.

Answer 2

Есть ли другие абоненты fDE_myquery за пределами вашего конкретного использования? И как часто это называют? Проблема не в том, что ваш SELECT * FROM dbo.fDE_myquery(); получает неоптимальный план, это то, что один или несколько запросов внутри fDE_myquery получает неоптимальный план. Следовательно, вы можете просто добавить OPTION(RECOMPILE) на один или несколько запросов внутри этого TVF.

Если этот TVF называется много, это негативно скажется на производительности. Вот почему я спросил о других применениях этого TVF: если это единственное или, безусловно, основное использование этого TVF, то, возможно, оно того стоит, если часто выявляются плохие планы.

Но если есть несколько других абонентов этого TVF, которые не испытывают проблемы, тогда RECOMPILE в TVF не может быть пути. Хотя, в этом случае вы могли бы создать оболочку TVF, которая инкапсулирует SELECT * FROM dbo.fDE_myquery() OPTION (RECOMPILE);, Это может показаться более гибким решением:). Это должен был быть Multistatment TVF вместо обычно лучшего Inline TVF, как я только что попробовал, и Inline TVF, похоже, не оценивает OPTION оговорка, но многопользовательский TVF был в порядке с этим.

РЕДАКТИРОВАТЬ:
Или, если вы хотите обработать это исключительно в EF, вы можете просто выполнить запрос на перекомпиляцию с одной строкой кода:

ctx.context.ExecuteStoreCommand("EXEC sp_recompile 'dbo.fDE_myquery';");

А потом сделай свое:

var query = from f in ctx.fDE_myQuery(aBool, anotherBool, StartDate, 
            EndDate, someInt, moreBool)
            select f;