CODEWITHSUNDEEP
c#sql-serverconcurrencyentity-framework-corelocking
Cristiano Ghersi
Cristiano Ghersi

Reputation: 2122

EF Core takes a lot of time, sometimes, to perform SELECT query

I am using EF Core 6 vs a SQL Server DB. Sometimes, the execution of a SELECT query takes more than 30 seconds, and goes in timeout.

If I execute the very same SQL generated by EF Core (with the very same parameters, on the same DB, just a few seconds after the timeout), it takes less than a second.

During the overall period, the DB server stays with CPU < 30%.

Running the SQL query on SQL Server Management Studio, I can see that the execution plan is ideal (i.e. it uses the indexes, etc.).

So I am afraid there could be some locking that stops the DB from returning the result of the query.

Is there a way to specify to EF Core e.g. the ISOLATION LEVEL of the query, or even some concurrency / locking strategy?

In my specific scenario, it's ok to have dirty or not completely up-to-date reads, because we have procedures in place to retrieve the clean data in a subsequent round of queries.

Thanks

Upvotes: 8

Views: 2918

Answers (3)

H.A.H.
H.A.H.

Reputation: 3897

EF Core 9. Control (force or prevent) parametrization:

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

Why is this better?

  1. Not writing extension.
  2. No writing interceptor.
  3. No hints and string checking with substrings and other code.
  4. You have the option to determine what is parameter and what constant. (Believe it or not, you do not want OPTION(RECOMPILE) all the time.)

Upvotes: 1

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

It is not new question when SQL Server may slowdown queries because of Parameter Sniffing. Problem can be solved by converting parameters to constants or by adding OPTION(RECOMPILE) to the end of the query. This answer adds DbCommandInterceptor to DbContextOptions and appends OPTION(RECOMPILE) hint to particular queries.

Configuring DbContext

builder.UseSqlServer(connectionString)
    .UseRecompileExtensions(); // registering interceptor

How to use in queries:

var name = "SomeName";
var result = context.SomeItems
    .Where(x => x.Name == name)
    .WithRecompile() // it marks query as a query which needs RECOMPILE query hint
    .ToList();

Then to SQL Server will be sent the following SQL:

SELECT [s].[Id], [s].[Name]
FROM [SomeItems] AS [s]
WHERE [s].[Name] = @__name_0
OPTION(RECOMPILE)

And implementation of extensions:

I have put everything into one static class for simplifying answer. Tested on EF Core 6, but should work for lower versions also.

public static class RecompileExtensions
{
    private const string RecompileTag = "recompile_query_tag";
    private const string RecompileComment = "-- " + RecompileTag + "\r\n";

    public static DbContextOptionsBuilder UseRecompileExtensions(this DbContextOptionsBuilder builder)
    {
        return builder.AddInterceptors(RecompileInterceptor.Instance);
    }

    public static IQueryable<T> WithRecompile<T>(this IQueryable<T> query)
    {
        return query.TagWith(RecompileTag);
    }

    private class RecompileInterceptor : DbCommandInterceptor
    {
        public static RecompileInterceptor Instance = new();

        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
            CancellationToken cancellationToken = new CancellationToken())
        {
            CorrectCommand(command);

            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            CorrectCommand(command);

            return base.ReaderExecuting(command, eventData, result);
        }

        private static void CorrectCommand(DbCommand command)
        {
            var newQuery = command.CommandText.Replace(RecompileComment, "");

            // if query was changed, we have to append RECOMPILE option
            if (!ReferenceEquals(newQuery, command.CommandText))
            {
                // remove rest of the comment
                if (newQuery.StartsWith("\r\n"))
                    newQuery = newQuery.Substring(2);

                newQuery += "\r\nOPTION(RECOMPILE)";
                command.CommandText = newQuery;
            }
        }
    }
}

UPDATE

Starting from SQL Server 2016 it is possible to disable Parameter Sniffing via ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

Upvotes: 7

Muhammad Faiq
Muhammad Faiq

Reputation: 69

Recompile is not recommended, it may harm the good running application. For large-scale data , I personally recommended using DBQuery or Simple ADO.net

Upvotes: 0

Related Questions