I am experiencing parameter sniffing problem sometimes. So I would like to add OPTION (RECOMPILE) to the end of query to fix it. How can I do it in EF 6?
Add a comment
|
4 Answers
I implemented IDbCommandInterceptor interface to fix the parameter sniffing error.
The usage of the new interceptor:
using (var dataContext = new AdventureWorks2012Entities())
{
var optionRecompileInterceptor = new OptionRecompileInterceptor();
DbInterception.Add(optionRecompileInterceptor);
string city = "Seattle";
var seattle = (
from a in dataContext.Addresses
where a.City == city
select a).ToList();
DbInterception.Remove(optionRecompileInterceptor); //Remove interceptor to not affect other queries
}
Implementation of the interceptor:
public class OptionRecompileInterceptor : DbCommandInterceptor
{
static void AddOptionToCommand(DbCommand command)
{
string optionRecompileString = "\r\nOPTION (RECOMPILE)";
if (!command.CommandText.Contains(optionRecompileString)) //Check the option is not added already
{
command.CommandText += optionRecompileString;
}
}
public override void NonQueryExecuting(
DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
AddOptionToCommand(command);
}
public override void ReaderExecuting(
DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
AddOptionToCommand(command);
}
public override void ScalarExecuting(
DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
AddOptionToCommand(command);
}
}
4 Comments
Ivan Stoev
Good, but it would be easier if you inherit
DbCommandInterceptor class, so you don't have to override the unneeded methods.
Rudey
I would use
try ... finally to ensure that DbInterception.Remove always gets called.
Jake Walden
@Rudey or let it implement
IDisposable so it can be used in a using statement
ps2goat
I used this as a base for a .NET Core 3.1 app with the same issue. I'll post that with some updates, since core kind of sucks when you have to hunt down the packages and namespaces if they aren't specified. Times went from ~35s to 350ms! (3.1 was the first Core version to have interceptors exposed, apparently)
In EF core 6 there is no static DbInterception.Remove method and that will cause all the queries to be processed get that with recompile option.
You can read the following article from Microsoft describing how it is done for adding OPTION (ROBUST PLAN) to your TSQLs by adding a simple tag to your code and it works the same for adding OPTION (RECOMPILE)
var blogs1 = context.Blogs.TagWith("Use hint: RECOMPILE").ToList();
--update
check below page for more explanation https://davecallan.com/how-to-add-sql-query-hints-to-entity-framework-core-queries/
Comments
If you are here and you are using EF Core, you can add an interceptor and tag them in the query. Super easy, but sometimes hard to find references.
https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors
Comments
Better late than never: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew#force-or-prevent-query-parameterization
async Task<List<Post>> GetPostsForceConstant(int id)
=> await context.Posts
.Where(e => e.Title == ".NET Blog" && e.Id == EF.Constant(id))
.ToListAsync();
Output: Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [p].[Id], [p].[Archived], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Discriminator], [p].[PublishedOn], [p].[Title], [p].[PromoText], [p].[Metadata] FROM [Posts] AS [p] WHERE [p].[Title] = N'.NET Blog' AND [p].[Id] = 1
Comments
