Entity Framework Core - SQL Update Cache Plan Issue

Question

I have a simple application that listen to events from service bus. Based on incoming events, it will update records in the database using Entity Framework Core. The application will batch incoming messages before it queries the database and update the entity properties.

Following is just sample code:

var dbResults = _context.SampleDbSet
    .Where(s => s.Active)
    .ToListAsync();

var dbResultsLookup = dbResults
    .ToDictionary(
        entity => $"{entity.Id}_{entity.Name}",
        v => v
    );

foreach (var message in incomingMessages)
{
    if (dbResultsLookup.TryGetValue($"{message.Id}_{message.Name}", out var dbEntity))
    {
        dbEntity.PropertyOne = message.PropertyOne;
        dbEntity.LastChangeTime = DateTimeOffset.Now;
    }
}

await _context.SaveChangesAsync(cancel);

What happen in SQL cache plan is:

Each of the batch update, end up with one unique Prepared plan in dm_exec_cached_plans this is because some time PropertyOne is not modified and Entity Framework will not include it as part of the parameterized update. Example below: @p76 decimal(9,2) only get added when PropertyOne get updated.

(
    @p1 int,@p2 int,@p0 datetimeoffset(7),@p4 int,@p5 int,@p3 datetimeoffset(7),
    @p7 int,@p8 int,@p6 datetimeoffset(7),@p10 int,
    @p11 int,@p9 datetimeoffset(7),@p13 int,@p14 int,@p12 datetimeoffset(7),
    @p16 int,@p17 int,@p15 datetimeoffset(7),@p19 int,@p20 int,@p18 datetimeoffset(7),@p22 int,@p23 int,@p21 datetimeoffset(7),
    @p25 int,@p26 int,@p24 datetimeoffset(7),@p28 int,@p29 int,@p27 datetimeoffset(7),@p31 int,@p32 int,@p30 datetimeoffset(7),
    @p34 int,@p35 int,@p33 datetimeoffset(7),@p37 int,@p38 int,@p36 datetimeoffset(7),@p40 int,@p41 int,@p39 datetimeoffset(7),
    @p43 int,@p44 int,@p42 datetimeoffset(7),@p46 int,@p47 int,@p45 datetimeoffset(7),

@p77 int,@p78 int,@p75 datetimeoffset(7),@p76 decimal(9,2)

)SET NOCOUNT ON;

UPDATE [Sample_Table] SET [LastChangeTime] = @p0  WHERE [Id] = @p1 AND [Name] = @p2;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p3  WHERE [Id] = @p4 AND [Name] = @p5;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p6  WHERE [Id] = @p7 AND [Name] = @p8;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p9  WHERE [Id] = @p10 AND [Name] = @p11;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p12  WHERE [Id] = @p13 AND [Name] = @p14;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p15  WHERE [Id] = @p16 AND [Name] = @p17;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p18  WHERE [Id] = @p19 AND [Name] = @p20;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p21  WHERE [Id] = @p22 AND [Name] = @p23;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p24  WHERE [Id] = @p25 AND [Name] = @p26;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p27  WHERE [Id] = @p28 AND [Name] = @p29;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p30  WHERE [Id] = @p31 AND [Name] = @p32;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p33  WHERE [Id] = @p34 AND [Name] = @p35;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p36  WHERE [Id] = @p37 AND [Name] = @p38;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p39  WHERE [Id] = @p40 AND [Name] = @p41;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p42  WHERE [Id] = @p43 AND [Name] = @p44;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p45  WHERE [Id] = @p46 AND [Name] = @p47;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p75, [PropertyOne] = @p76  WHERE [Id] = @p77 AND [Name] = @p78;
SELECT @@ROWCOUNT;

Because each batch execution could create a unique parameterized query, it blows up the SQL server query plan which resulted in some other query plan get clear out to make space for this random query plan.

I have not look into how to use stored procedure + TVP, and without stored procedure, is there away to

Even though the PropertyOne is not updated, we still want EF Core to include it as part of the parameterized query?
Not to store such update plan into the SQL query cache? (Since EF Core will generate random query) Can this be done whether it is from EF Core library or SQL Database itself?

dani herrera · Accepted Answer · 2023-06-27 07:19:04Z

You can switch off AutoDetectChanges to always get the same SQL statement.

On the other hand, this will improve the performance of SaveChanges because it won't have to traverse the graph looking for changes:

// Switch off autodetect changes:
_context.ChangeTracker.AutoDetectChangesEnabled = false;
foreach (var message in incomingMessages)
{
    if (dbResultsLookup.TryGetValue($"{message.Id}_{message.Name}", out var dbEntity))
    {
        dbEntity.PropertyOne = message.PropertyOne;
        dbEntity.LastChangeTime = DateTimeOffset.Now;
        // Set fields to be updated:
        _context.Entry(dbEntity).Property(x => x.PropertyOne).IsModified = true;
        _context.Entry(dbEntity).Property(x => x.LastChangeTime).IsModified = true;
    }
}
await _context.SaveChangesAsync(cancel);
// Restore autodetect changes:
_context.ChangeTracker.AutoDetectChangesEnabled = true;

Collectives™ on Stack Overflow

Entity Framework Core - SQL Update Cache Plan Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related