Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding a query hint when calling Table-Valued Function

Tags:

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?

like image 771
Matt Burland Avatar asked Nov 05 '14 16:11

Matt Burland


People also ask

How do you call a table-valued function in SQL query?

A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table , you can use a table-valued function anywhere in SQL that you can use a table.

Which hint do you use to execute a query using a specific query plan?

The PARAMETERIZATION query hint can only be specified inside a plan guide to override the current setting of the PARAMETERIZATION database SET option. It can't be specified directly within a query. For more information, see Specify Query Parameterization Behavior by Using Plan Guides.

How do you write table-valued functions?

In table-valued function the return type is TABLE. Also, there are no BEGIN and END statements. You simply return a query which retrieves records from the database. In the above script we created a function "BornBefore" which accepts one parameter (named @DOB) of type DATETIME.

What clause is a table-valued function used in SQL?

Table-valued functions in Oracle are the functions, returning a collection of rows and can be queried like usual database tables by calling the function in the FROM clause of SELECT statements.


Video Answer


2 Answers

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.

like image 57
Matt Burland Avatar answered Sep 25 '22 10:09

Matt Burland


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; 
like image 25
Solomon Rutzky Avatar answered Sep 22 '22 10:09

Solomon Rutzky