Adding a query hint when calling Table-Valued Function

Question

I'm calling a Table-Valued Function from entity framework and need to be able to add the option (recompile) to it because the execution plan it picks up is not optimal. Running the query in SQL Server Management Studio, it would look something like this:

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

By from EF, there's no way to add that hint, AFAIK. The EF part looks something like:

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

I saw this question:

How do I control parameter sniffing and/or query hints in entity framework?

But it's old, and the accepted solution doesn't really give enough information on how to actually implement the suggested solution (use plan guides) with entity framework. If that is the only solution, how do you get entity framework to use a plan guide anyway?

Prefab

Answer 1

I came across this:

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

And it appears that you can do something like this:

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);
    }
}

And register it like this (I did it in Application_Start of global.asax.cs):

DbInterception.Add(new HintInterceptor());

And it will let you alter the CommandText. The only problem is that it's now attached for every reader query which might be a problem as some of them might be negatively impacted by that hint. I'm guessing I can do something with the context to figure out if the hint is appropriate or not, or worse case I could examine the CommandText itself.

Doesn't quite seem the most elegant or fine-grained solution.

Edit: From the interceptorContext, you can get the DbContexts, so I defined an interface that looks like this:

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

And then created a class that derives from my original DbContext (generated by EF) and implements the above interface. Then I changed my interceptor to look like this:

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);
    }
}

Now to use it, I create a context using my derived class instead of the original, set QueryHint to whatever I want it to be (recompile in this case) and set ApplyHint right before I execute the command and set it back to false afterwards.

To make this all a little more self-contained, I ended up defining an interface like this:

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

And extended my db context like this (you could, of course, just use a partial class to extend the EF generated class as well):

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

And then, to make the turn-on, turn-off part a little easier to handle, I defined this:

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;
    }
}

Now to use it, I can do just this:

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
}

This maybe slightly overkill and could be developed further (for example, using an enum for available hints instead of a string - or subclassing a recompile query hint so you don't need to specify the string recompile every time and risk a typo), but it solved my immediate problem.

Prefab

Can you explain more how you called it ? – Iodize

Adding option(recompile) took a >10 second linq query that I've been dealing with for days down to <1 second. Brilliant idea using an interceptor to add it. – Shamus

@drexdrex: I expanded a little more. Hope it helps. – Prefab

I should send my special thanks to the @MattBurland for this clean solution. I adapted my project to your interceptor and now I'm very happy with the execution time. – Babysit

Answer 2

Are there other callers of fDE_myquery outside of your specific usage? And how often does this get called? The issue is not that your SELECT * FROM dbo.fDE_myquery(); is getting a sub-optimal plan, it is that one or more queries inside of fDE_myquery is getting a sub-optimal plan. Hence, you could just add the OPTION(RECOMPILE) to one or more queries inside that TVF.

If this TVF is called a lot then this would have a negative impact on performance. That is why I asked about other uses of this TVF: if this is the only, or by far the main, use of this TVF, then it might be well worth it if the bad plans are being picked up frequently.

But if there are several other callers of this TVF that are not experiencing an issue, then putting the RECOMPILE in the TVF might not be the way to go. Although, in that case you could create a wrapper TVF that encapsulates the SELECT * FROM dbo.fDE_myquery() OPTION (RECOMPILE);. This would appear to be a more flexible solution :). It would have to be a Multistatment TVF instead of the typically better Inline TVF as I just tried it and the Inline TVF does not seem to appreciate the OPTION clause, but the Multistatement TVF was fine with it.

EDIT:
Or, if you want to handle this purely in EF, you could simply issue a recompile request with a single line of code:

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

And then do your:

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

Zilvia

I'm sure I could go spelunking into the TVF itself (I didn't write it) or even wrap the TVF in another TVF that just calls it with the required query hint, but I consider that to be a last resort approach. It seems there ought to be a way to specify a query hint in EF that doesn't involve having to change things on the database. – Prefab

@MattBurland : fair enough. I just had another idea that I updated my answer with. There is more than 1 way to get a recompile :). – Zilvia