OPTIMIZE FOR UNKNOWN on specific EF Core query

user_5333805 0
Viewed 448

I have a webjob project running on .NET Framework with EF Core 3.1. The webjob processes messages from an Azure Service Bus and saves them into an Azure SQL Database.

The problem I have is that the Azure SQL Database generates really bad query plans for the query that EF Core generated. With the generated query plan the execution time is 1-2 minutes. However when I use OPTION (OPTIMIZE FOR UNKNOWN) the execution time drops down to 0.01 - 0.02 minutes.

So now I want to implement the OPTION (OPTIMIZE FOR UNKNOWN) in EF Core. I have found that they added a DbCommandInterceptor in EF Core 3.1 where can you append things to your query: MSDOCS

public class HintCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // Manipulate the command text, etc. here...
        command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
        return result;
    }
}

But it seems like this interceptor will run on every query and I only want it for a specific query. I could implement a seperate DbContext for this interceptor but that doesn't seem like a solid solution. Does anyone have an idea how I could implement this in a correct way?

entity-framework-core entity-framework c#
2 Answers

I created an interface:

public interface IInterceptable
{
    bool EnableCommandInterceptors { get; set; }
}

And implemented it in my context class:

public bool EnableCommandInterceptors { get; set; }

And in the interceptor I have:

public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
    if(command.CommandText.StartsWith("SELECT") 
        && eventData.Context is IInterceptable intercepatable
        && intercepatable.EnableCommandInterceptors)
    {
        command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
    }
    return result;
}

This allows turning this feature on and off, which may be enough if this specific query is the only query that a context instance will run. If not, you could add more conditions to the part if(command.CommandText.StartsWith("SELECT").

Another way is to tag a specific query with .TagWith and look for the tag text in the interceptor:

if (command.CommandText.StartsWith("SELECT") 
        && command.CommandText.Contains("my tagged text"))
{
    command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
}
user_8617160

I'd like to append to Gert Arnold's post...

If you want both synchronous and async methods to work, for example ToListAsync(), then you need to overload the Async version, too.

public class OptimizeForUnknownInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
        return base.ReaderExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = new CancellationToken())
    {
        command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
}

I also use this interceptor in more targeted fashion. So I can apply it to a single Context:

var builder = new DbContextOptionsBuilder<CustomModelContext>();
builder.AddInterceptors(new OptimizeForUnknownInterceptor());

// Includes IConfiguration for appsettings ConnectionStrings, using dependency injection
await using (var db = new CustomModelContext(builder.Options, _configuration))
{
    ...
    var lst = await query.ToListAsync();
    ...
}

Using this, I created a partial with all the constructors for CustomModelContext:

public partial class CustomModelContext
{
    private readonly IConfiguration _configuration;

    public CustomModelContext(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public CustomModelContext(DbContextOptions<CustomModelContext> options, IConfiguration configuration)
        : base(options)
    {
        _configuration = configuration;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        if (!optionsBuilder.IsConfigured)
            optionsBuilder.UseSqlServer(_configuration.GetConnectionString("CustomModelConnection"));
    }

}

For reference, I'm using .NET 5 and EF Core 5

user_44274570
Related