First Query take 8 seconds even with Compiled models #29827

RomeoQNgo · 2022-12-12T05:28:00Z

I'm using EF6 in our AWS lambda. Without Compiled models, the first query usually take around 15 seconds.
With Compiled Models, it reduced to 8 seconds.

The data and query is not the issue, it takes about 100 microsecond for the 2nd/3rd... invocations/requests (with querying different record). AsNoTracking is used so there is no caching either. There are only 9 models for this app.

I'm also using non EF SQL in this app (using SqlConnection and auto generated SqlCommands) to a different database connection string, and it takes about 50 microseconds or less.

How can I improve EF and reduce the time it takes for the 1st query?

https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?tabs=with-di%2Cwith-constant#compiled-models

The text was updated successfully, but these errors were encountered:

ajcvickers · 2022-12-12T10:13:30Z

@RomeoQNgo That doesn't seem right for an application with only nine entity types. Can you post your entity types, configuration, and DbContext code?

roji · 2022-12-12T12:43:14Z

Also, can you please say which version of EF Core are you using? If it's 7.0.0, did you have the same slow behavior with 6.0?

RomeoQNgo · 2022-12-12T18:12:45Z

@roji Sorry, I totally forgot to put in which version I'm using. I'm using EF6. haven't upgraded to EF7 yet. Trying to see if Compiled Model work first and then upgrade if AWS Lambda can handle EF7.

RomeoQNgo · 2022-12-12T18:18:37Z

@RomeoQNgo That doesn't seem right for an application with only nine entity types. Can you post your entity types, configuration, and DbContext code?

@ajcvickers

Thank you for looking into this.

Here's the Query and entities. The query only associated with the 1st 2 classes.

return dbContext.SyncTables
                .Include(x => x.Fields)
                .AsNoTracking()
                .FirstOrDefault(x => x.Id == id);

IOC

            ServiceCollection.AddDbContext<SyncDatabaseContext>((options) =>
            {
                options.UseModel(EFCompiledModels.SyncDatabaseContextModel.Instance);
                var dbConnectionString = GetSecretWithEnvironmentVariableName("SyncDbConnectionStringSecretName"); 
                options.UseSqlServer(dbConnectionString);
            });

Entities

    [Table("SyncTable")]
    public class SyncTable
    {
        public int Id { get; set; }
        public string? QuickBaseTokenSecretManagerName { get; set; }
        public string? QuickBaseTableId { get; set; }
        public string? CoaTableName { get; set; }
        public string? CoaDbConnectionSecretManagerName { get; set; }
        public int QuickBaseDateModifiedFieldId { get; set; }
        public string? DeveloperName { get; set; }
        public string? RecordIdFieldName { get; set; }
        public string? DateModifiedFieldName { get; set; }

        public List<SyncTableField>? Fields { get; set; }
        public List<SyncTableSchedule>? SyncSchedules { get; set; }
        public List<BusinessEventTriggerTable>? BusinessEventTriggerTables { get; set; }
    }

    [Table("SyncTableField")]
    public class SyncTableField
    {
        public int Id { get; set; }
        public int SyncTableId { get; set; }
        public int QuickBaseFieldId { get; set; }
        public string? CoaTableFieldName { get; set; }
        public string? ExpectedDataType { get; set; }
        public string? DeveloperName { get; set; }
        public bool? IsPassthroughField { get; set; }
        public int Size { get; set; }
        public int Precision { get; set; }

        public SyncTable? SyncTable { get; set; }
    }

    [Table("SyncTableSchedule")]
    public class SyncTableSchedule
    {
        public int Id { get; set; }
        public int SyncTableId { get; set; }
        public TimeSpan? DailyStartTime { get; set; }
        public TimeSpan? DailyStopTime { get; set; }
        public int SyncIntervalMinutes { get; set; }
        public bool IsActive { get; set; }
        public DateTime StartDateTime { get; set; }
        public int DataModifiedIntervalMinutes { get; set; }

        public SyncTable? SyncTable { get; set; }
    }

    [Table("SyncHistory")]
    public class SyncHistory
    {
        public long Id { get; set; }
        public int SyncTableScheduleId { get; set; }
        public DateTime StartDateTime { get; set; }
        public DateTime EndDateTime { get; set; }
        public int SyncRecordCount { get; set; } = 0;
        public int ErrorRecordCount { get; set; } = 0;
    }

    [Table("SyncError")]
    public class SyncError
    {
        public long Id { get; set; }
        public long SyncHistoryId { get; set; }
        public int RecordId { get; set; }
        public string? ErrorMessage { get; set; }
    }

    [Table("BusinessEvent")]
    public class BusinessEvent
    {
        public int Id { get; set; }
        public bool Enabled { get; set; }
        public string? TriggerDestinationUrl { get; set; }
        public string? DeveloperName { get; set; }
    }

    [Table("BusinessEventTriggerField")]
    public class BusinessEventTriggerField
    {
        public int Id { get; set; }
        public int BusinessEventTriggerTableId { get; set; }
        public int SyncTableFieldId { get; set; }
        public bool TriggerIfValueChanged { get; set; }
        public bool TriggerIfValueEqualTo { get; set; }
        public string? ValueEqualTo { get; set; }

        public BusinessEventTriggerTable? BusinessEventTriggerTable { get; set; }
        public SyncTableField? SyncTableField { get; set; }
    }


    [Table("BusinessEventTriggerTable")]
    public class BusinessEventTriggerTable
    {
        public int Id { get; set; }
        public int BusinessEventId { get; set; }
        public int SyncTableId { get; set; }
        public bool TriggerWhenRecordUpdated { get; set; }
        public bool TriggerWhenRecordAdded { get; set; }

        public BusinessEvent? BusinessEvent { get; set; }
    }


    [Table("BusinessEventTriggerEvent")]
    public class BusinessEventTriggerRecord
    {
        [Key]
        public Int64 Id { get; set; }

        [NotMapped]
        public string? TableDeveloperName { get; set; }

        [JsonIgnore]
        public int? SyncTableId { get; set; }

        [NotMapped]
        public string? BusinessEventDeveloperName { get; set; }

        [JsonIgnore]
        public int? BusinessEventId { get; set; }

        public DateTime TriggerDatetime { get; set; }
        public int TriggerRecordId { get; set; }
        public bool IsNewRecord { get; set; }

        [NotMapped]
        public Dictionary<string, object> OldData { get; set; }

        [Column("OldData")]
        [JsonIgnore]
        public string? OldDataJsonString
        {
            get
            {
                return OldData == null ? null : JsonSerializer.Serialize(OldData);
            }
            set
            {
                OldData = value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(value);
            }
        }

        [NotMapped]
        public Dictionary<string, object> NewData { get; set; }

        [Column("NewData")]
        [JsonIgnore]
        public string? NewDataJsonString
        {
            get
            {
                return NewData == null ? null : JsonSerializer.Serialize(NewData);
            }
            set
            {
                NewData = value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(value);
            }
        }

        [JsonIgnore]
        [NotMapped]
        public DynamicSqlRecord? Record { get; private set; }

        public BusinessEventTriggerRecord() { }

        public BusinessEventTriggerRecord(SyncTable syncTable, BusinessEvent businessEvent, DynamicSqlRecord? modifyingRecord, DynamicSqlRecord? dbRecord)
        {
            ArgumentNullException.ThrowIfNull(nameof(modifyingRecord));
            Record = modifyingRecord;
            TableDeveloperName = syncTable?.DeveloperName;
            SyncTableId = syncTable?.Id;
            BusinessEventDeveloperName = businessEvent?.DeveloperName;
            BusinessEventId = businessEvent?.Id;
            TriggerDatetime = DateTime.Now;
            TriggerRecordId = modifyingRecord.RecordId.Value;
            IsNewRecord = modifyingRecord.IsNew;
            OldData = BuildObjectDictionary(dbRecord);
            NewData = BuildObjectDictionary(modifyingRecord);
        }

        public static Dictionary<string, object> BuildObjectDictionary(DynamicSqlRecord? record)
        {
            var objectDictionary = new Dictionary<string, object>();
            if (record == null || record.Fields == null || !record.Fields.Any())
            {
                return objectDictionary;
            }
            
            foreach (var field in record.Fields)
            {
                if (field.Value != null)
                {
                    objectDictionary.Add(field?.Field.DeveloperName, field.Value);
                }
            }

            return objectDictionary;
        }
    }

ajcvickers · 2022-12-13T11:19:05Z

@RomeoQNgo Model building on my laptop for that model takes around 7ms in EF Core 6.0.11, so I don't think model building is the issue here. I would suggest maybe trying to profile a little more closely what is going on in that 8 seconds.

Note for triage: On my benchmark, model building seems to be an order of magnitude slower on EF Core 7 than EF Core 6. /cc @AndriySvyryd

EF Core 60.11

Method	Mean	Error	StdDev
NoCachingSyncTableModel	7,056.01 us	71.893 us	63.732 us
CachingSyncTableModel	74.77 us	1.483 us	2.029 us
NoCachingSimpleModel	1,526.29 us	21.224 us	18.814 us
CachingSimpleModel	74.30 us	0.788 us	0.699 us

EF Core 7.0.0

Method	Mean	Error	StdDev
NoCachingSyncTableModel	62,259.76 us	816.102 us	723.454 us
CachingSyncTableModel	79.53 us	1.179 us	1.103 us
NoCachingSimpleModel	7,974.42 us	121.983 us	186.281 us
CachingSimpleModel	78.51 us	0.897 us	0.839 us

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading;
using System.Threading.Tasks;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.Extensions.Logging;

    [Table("SyncTable")]
    public class SyncTable
    {
        public int Id { get; set; }
        public string? QuickBaseTokenSecretManagerName { get; set; }
        public string? QuickBaseTableId { get; set; }
        public string? CoaTableName { get; set; }
        public string? CoaDbConnectionSecretManagerName { get; set; }
        public int QuickBaseDateModifiedFieldId { get; set; }
        public string? DeveloperName { get; set; }
        public string? RecordIdFieldName { get; set; }
        public string? DateModifiedFieldName { get; set; }

        public List<SyncTableField>? Fields { get; set; }
        public List<SyncTableSchedule>? SyncSchedules { get; set; }
        public List<BusinessEventTriggerTable>? BusinessEventTriggerTables { get; set; }
    }

    [Table("SyncTableField")]
    public class SyncTableField
    {
        public int Id { get; set; }
        public int SyncTableId { get; set; }
        public int QuickBaseFieldId { get; set; }
        public string? CoaTableFieldName { get; set; }
        public string? ExpectedDataType { get; set; }
        public string? DeveloperName { get; set; }
        public bool? IsPassthroughField { get; set; }
        public int Size { get; set; }
        public int Precision { get; set; }

        public SyncTable? SyncTable { get; set; }
    }

    [Table("SyncTableSchedule")]
    public class SyncTableSchedule
    {
        public int Id { get; set; }
        public int SyncTableId { get; set; }
        public TimeSpan? DailyStartTime { get; set; }
        public TimeSpan? DailyStopTime { get; set; }
        public int SyncIntervalMinutes { get; set; }
        public bool IsActive { get; set; }
        public DateTime StartDateTime { get; set; }
        public int DataModifiedIntervalMinutes { get; set; }

        public SyncTable? SyncTable { get; set; }
    }

    [Table("SyncHistory")]
    public class SyncHistory
    {
        public long Id { get; set; }
        public int SyncTableScheduleId { get; set; }
        public DateTime StartDateTime { get; set; }
        public DateTime EndDateTime { get; set; }
        public int SyncRecordCount { get; set; } = 0;
        public int ErrorRecordCount { get; set; } = 0;
    }

    [Table("SyncError")]
    public class SyncError
    {
        public long Id { get; set; }
        public long SyncHistoryId { get; set; }
        public int RecordId { get; set; }
        public string? ErrorMessage { get; set; }
    }

    [Table("BusinessEvent")]
    public class BusinessEvent
    {
        public int Id { get; set; }
        public bool Enabled { get; set; }
        public string? TriggerDestinationUrl { get; set; }
        public string? DeveloperName { get; set; }
    }

    [Table("BusinessEventTriggerField")]
    public class BusinessEventTriggerField
    {
        public int Id { get; set; }
        public int BusinessEventTriggerTableId { get; set; }
        public int SyncTableFieldId { get; set; }
        public bool TriggerIfValueChanged { get; set; }
        public bool TriggerIfValueEqualTo { get; set; }
        public string? ValueEqualTo { get; set; }

        public BusinessEventTriggerTable? BusinessEventTriggerTable { get; set; }
        public SyncTableField? SyncTableField { get; set; }
    }


    [Table("BusinessEventTriggerTable")]
    public class BusinessEventTriggerTable
    {
        public int Id { get; set; }
        public int BusinessEventId { get; set; }
        public int SyncTableId { get; set; }
        public bool TriggerWhenRecordUpdated { get; set; }
        public bool TriggerWhenRecordAdded { get; set; }

        public BusinessEvent? BusinessEvent { get; set; }
    }


    [Table("BusinessEventTriggerEvent")]
    public class BusinessEventTriggerRecord
    {
        [Key]
        public Int64 Id { get; set; }

        [NotMapped]
        public string? TableDeveloperName { get; set; }

        [JsonIgnore]
        public int? SyncTableId { get; set; }

        [NotMapped]
        public string? BusinessEventDeveloperName { get; set; }

        [JsonIgnore]
        public int? BusinessEventId { get; set; }

        public DateTime TriggerDatetime { get; set; }
        public int TriggerRecordId { get; set; }
        public bool IsNewRecord { get; set; }

        [NotMapped]
        public Dictionary<string, object> OldData { get; set; }

        [Column("OldData")]
        [JsonIgnore]
        public string? OldDataJsonString
        {
            get
            {
                return OldData == null ? null : JsonSerializer.Serialize(OldData);
            }
            set
            {
                OldData = value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(value);
            }
        }

        [NotMapped]
        public Dictionary<string, object> NewData { get; set; }

        [Column("NewData")]
        [JsonIgnore]
        public string? NewDataJsonString
        {
            get
            {
                return NewData == null ? null : JsonSerializer.Serialize(NewData);
            }
            set
            {
                NewData = value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(value);
            }
        }
        public BusinessEventTriggerRecord() { }
    }

    public class DummySyncTable
    {
        public int Id { get; set; }
    }

    public class DummySyncTableField
    {
        public int Id { get; set; }
    }

    public class DummySyncTableSchedule
    {
        public int Id { get; set; }
    }

    public class DummySyncHistory
    {
        public long Id { get; set; }
    }

    public class DummySyncError
    {
        public long Id { get; set; }
    }

    public class DummyBusinessEvent
    {
        public int Id { get; set; }
    }

    public class DummyBusinessEventTriggerField
    {
        public int Id { get; set; }
    }

    public class DummyBusinessEventTriggerTable
    {
        public int Id { get; set; }
    }

    public class DummyBusinessEventTriggerRecord
    {
        public Int64 Id { get; set; }
    }

public class ModelCachingContext : SomeDbContext
{
}

public class NonModelCachingContext : SomeDbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<IModelCacheKeyFactory, DefeatingCacheKeyFactory>();

        base.OnConfiguring(optionsBuilder);
    }
}

public abstract class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .EnableSensitiveDataLogging();

    public DbSet<SyncTable> SyncTables { get; set; } = null!;
    public DbSet<SyncTableField> SyncTablesTableFields { get; set; } = null!;
    public DbSet<SyncTableSchedule> SyncTableSchedules { get; set; } = null!;
    public DbSet<SyncHistory> SyncHistories { get; set; } = null!;
    public DbSet<SyncError> SyncErrors { get; set; } = null!;
    public DbSet<BusinessEvent> BusinessEvents { get; set; } = null!;
    public DbSet<BusinessEventTriggerTable> BusinessEventsTriggerTables { get; set; } = null!;
    public DbSet<BusinessEventTriggerRecord> BusinessEventsTriggerRecords { get; set; } = null!;
    public DbSet<BusinessEventTriggerField> BusinessEventsTriggerFields { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }

    protected override void ConfigureConventions(ModelConfigurationBuilder configBuilder)
    {
    }
}

public class ModelCachingSimpleContext : SimpleDbContext
{
}

public class NonModelCachingSimpleContext : SimpleDbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<IModelCacheKeyFactory, DefeatingCacheKeyFactory>();

        base.OnConfiguring(optionsBuilder);
    }
}

public abstract class SimpleDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .EnableSensitiveDataLogging();

    public DbSet<DummySyncTable> SyncTables { get; set; } = null!;
    public DbSet<DummySyncTableField> SyncTablesTableFields { get; set; } = null!;
    public DbSet<DummySyncTableSchedule> SyncTableSchedules { get; set; } = null!;
    public DbSet<DummySyncHistory> SyncHistories { get; set; } = null!;
    public DbSet<DummySyncError> SyncErrors { get; set; } = null!;
    public DbSet<DummyBusinessEvent> BusinessEvents { get; set; } = null!;
    public DbSet<DummyBusinessEventTriggerTable> BusinessEventsTriggerTables { get; set; } = null!;
    public DbSet<DummyBusinessEventTriggerRecord> BusinessEventsTriggerRecords { get; set; } = null!;
    public DbSet<DummyBusinessEventTriggerField> BusinessEventsTriggerFields { get; set; } = null!;
}

public class DefeatingCacheKeyFactory : IModelCacheKeyFactory
{
    private static int _i;
    public object Create(DbContext context, bool designTime)
        => _i++;
}

public class Benchmarks
{
    [Benchmark]
    public void NoCachingSyncTableModel()
    {
        _ = new NonModelCachingContext().Model;
    }

    [Benchmark]
    public void CachingSyncTableModel()
    {
        _ = new ModelCachingContext().Model;
    }

    [Benchmark]
    public void NoCachingSimpleModel()
    {
        _ = new NonModelCachingSimpleContext().Model;
    }

    [Benchmark]
    public void CachingSimpleModel()
    {
        _ = new ModelCachingSimpleContext().Model;
    }
}

public class Program
{
    public static void Main()
    {
        BenchmarkRunner.Run<Benchmarks>();
    }
}

RomeoQNgo · 2022-12-13T15:06:43Z

@RomeoQNgo Model building on my laptop for that model takes around 7ms in EF Core 6.0.11, so I don't think model building is the issue here. I would suggest maybe trying to profile a little more closely what is going on in that 8 seconds.

Note for triage: On my benchmark, model building seems to be an order of magnitude slower on EF Core 7 than EF Core 6. /cc @AndriySvyryd

EF Core 60.11

Method Mean Error StdDev
NoCachingSyncTableModel 7,056.01 us 71.893 us 63.732 us
CachingSyncTableModel 74.77 us 1.483 us 2.029 us
NoCachingSimpleModel 1,526.29 us 21.224 us 18.814 us
CachingSimpleModel 74.30 us 0.788 us 0.699 us

EF Core 7.0.0

Method Mean Error StdDev
NoCachingSyncTableModel 62,259.76 us 816.102 us 723.454 us
CachingSyncTableModel 79.53 us 1.179 us 1.103 us
NoCachingSimpleModel 7,974.42 us 121.983 us 186.281 us
CachingSimpleModel 78.51 us 0.897 us 0.839 us

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading;
using System.Threading.Tasks;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.Extensions.Logging;

    [Table("SyncTable")]
    public class SyncTable
    {
        public int Id { get; set; }
        public string? QuickBaseTokenSecretManagerName { get; set; }
        public string? QuickBaseTableId { get; set; }
        public string? CoaTableName { get; set; }
        public string? CoaDbConnectionSecretManagerName { get; set; }
        public int QuickBaseDateModifiedFieldId { get; set; }
        public string? DeveloperName { get; set; }
        public string? RecordIdFieldName { get; set; }
        public string? DateModifiedFieldName { get; set; }

        public List<SyncTableField>? Fields { get; set; }
        public List<SyncTableSchedule>? SyncSchedules { get; set; }
        public List<BusinessEventTriggerTable>? BusinessEventTriggerTables { get; set; }
    }

    [Table("SyncTableField")]
    public class SyncTableField
    {
        public int Id { get; set; }
        public int SyncTableId { get; set; }
        public int QuickBaseFieldId { get; set; }
        public string? CoaTableFieldName { get; set; }
        public string? ExpectedDataType { get; set; }
        public string? DeveloperName { get; set; }
        public bool? IsPassthroughField { get; set; }
        public int Size { get; set; }
        public int Precision { get; set; }

        public SyncTable? SyncTable { get; set; }
    }

    [Table("SyncTableSchedule")]
    public class SyncTableSchedule
    {
        public int Id { get; set; }
        public int SyncTableId { get; set; }
        public TimeSpan? DailyStartTime { get; set; }
        public TimeSpan? DailyStopTime { get; set; }
        public int SyncIntervalMinutes { get; set; }
        public bool IsActive { get; set; }
        public DateTime StartDateTime { get; set; }
        public int DataModifiedIntervalMinutes { get; set; }

        public SyncTable? SyncTable { get; set; }
    }

    [Table("SyncHistory")]
    public class SyncHistory
    {
        public long Id { get; set; }
        public int SyncTableScheduleId { get; set; }
        public DateTime StartDateTime { get; set; }
        public DateTime EndDateTime { get; set; }
        public int SyncRecordCount { get; set; } = 0;
        public int ErrorRecordCount { get; set; } = 0;
    }

    [Table("SyncError")]
    public class SyncError
    {
        public long Id { get; set; }
        public long SyncHistoryId { get; set; }
        public int RecordId { get; set; }
        public string? ErrorMessage { get; set; }
    }

    [Table("BusinessEvent")]
    public class BusinessEvent
    {
        public int Id { get; set; }
        public bool Enabled { get; set; }
        public string? TriggerDestinationUrl { get; set; }
        public string? DeveloperName { get; set; }
    }

    [Table("BusinessEventTriggerField")]
    public class BusinessEventTriggerField
    {
        public int Id { get; set; }
        public int BusinessEventTriggerTableId { get; set; }
        public int SyncTableFieldId { get; set; }
        public bool TriggerIfValueChanged { get; set; }
        public bool TriggerIfValueEqualTo { get; set; }
        public string? ValueEqualTo { get; set; }

        public BusinessEventTriggerTable? BusinessEventTriggerTable { get; set; }
        public SyncTableField? SyncTableField { get; set; }
    }


    [Table("BusinessEventTriggerTable")]
    public class BusinessEventTriggerTable
    {
        public int Id { get; set; }
        public int BusinessEventId { get; set; }
        public int SyncTableId { get; set; }
        public bool TriggerWhenRecordUpdated { get; set; }
        public bool TriggerWhenRecordAdded { get; set; }

        public BusinessEvent? BusinessEvent { get; set; }
    }


    [Table("BusinessEventTriggerEvent")]
    public class BusinessEventTriggerRecord
    {
        [Key]
        public Int64 Id { get; set; }

        [NotMapped]
        public string? TableDeveloperName { get; set; }

        [JsonIgnore]
        public int? SyncTableId { get; set; }

        [NotMapped]
        public string? BusinessEventDeveloperName { get; set; }

        [JsonIgnore]
        public int? BusinessEventId { get; set; }

        public DateTime TriggerDatetime { get; set; }
        public int TriggerRecordId { get; set; }
        public bool IsNewRecord { get; set; }

        [NotMapped]
        public Dictionary<string, object> OldData { get; set; }

        [Column("OldData")]
        [JsonIgnore]
        public string? OldDataJsonString
        {
            get
            {
                return OldData == null ? null : JsonSerializer.Serialize(OldData);
            }
            set
            {
                OldData = value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(value);
            }
        }

        [NotMapped]
        public Dictionary<string, object> NewData { get; set; }

        [Column("NewData")]
        [JsonIgnore]
        public string? NewDataJsonString
        {
            get
            {
                return NewData == null ? null : JsonSerializer.Serialize(NewData);
            }
            set
            {
                NewData = value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(value);
            }
        }
        public BusinessEventTriggerRecord() { }
    }

    public class DummySyncTable
    {
        public int Id { get; set; }
    }

    public class DummySyncTableField
    {
        public int Id { get; set; }
    }

    public class DummySyncTableSchedule
    {
        public int Id { get; set; }
    }

    public class DummySyncHistory
    {
        public long Id { get; set; }
    }

    public class DummySyncError
    {
        public long Id { get; set; }
    }

    public class DummyBusinessEvent
    {
        public int Id { get; set; }
    }

    public class DummyBusinessEventTriggerField
    {
        public int Id { get; set; }
    }

    public class DummyBusinessEventTriggerTable
    {
        public int Id { get; set; }
    }

    public class DummyBusinessEventTriggerRecord
    {
        public Int64 Id { get; set; }
    }

public class ModelCachingContext : SomeDbContext
{
}

public class NonModelCachingContext : SomeDbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<IModelCacheKeyFactory, DefeatingCacheKeyFactory>();

        base.OnConfiguring(optionsBuilder);
    }
}

public abstract class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .EnableSensitiveDataLogging();

    public DbSet<SyncTable> SyncTables { get; set; } = null!;
    public DbSet<SyncTableField> SyncTablesTableFields { get; set; } = null!;
    public DbSet<SyncTableSchedule> SyncTableSchedules { get; set; } = null!;
    public DbSet<SyncHistory> SyncHistories { get; set; } = null!;
    public DbSet<SyncError> SyncErrors { get; set; } = null!;
    public DbSet<BusinessEvent> BusinessEvents { get; set; } = null!;
    public DbSet<BusinessEventTriggerTable> BusinessEventsTriggerTables { get; set; } = null!;
    public DbSet<BusinessEventTriggerRecord> BusinessEventsTriggerRecords { get; set; } = null!;
    public DbSet<BusinessEventTriggerField> BusinessEventsTriggerFields { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }

    protected override void ConfigureConventions(ModelConfigurationBuilder configBuilder)
    {
    }
}

public class ModelCachingSimpleContext : SimpleDbContext
{
}

public class NonModelCachingSimpleContext : SimpleDbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<IModelCacheKeyFactory, DefeatingCacheKeyFactory>();

        base.OnConfiguring(optionsBuilder);
    }
}

public abstract class SimpleDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .EnableSensitiveDataLogging();

    public DbSet<DummySyncTable> SyncTables { get; set; } = null!;
    public DbSet<DummySyncTableField> SyncTablesTableFields { get; set; } = null!;
    public DbSet<DummySyncTableSchedule> SyncTableSchedules { get; set; } = null!;
    public DbSet<DummySyncHistory> SyncHistories { get; set; } = null!;
    public DbSet<DummySyncError> SyncErrors { get; set; } = null!;
    public DbSet<DummyBusinessEvent> BusinessEvents { get; set; } = null!;
    public DbSet<DummyBusinessEventTriggerTable> BusinessEventsTriggerTables { get; set; } = null!;
    public DbSet<DummyBusinessEventTriggerRecord> BusinessEventsTriggerRecords { get; set; } = null!;
    public DbSet<DummyBusinessEventTriggerField> BusinessEventsTriggerFields { get; set; } = null!;
}

public class DefeatingCacheKeyFactory : IModelCacheKeyFactory
{
    private static int _i;
    public object Create(DbContext context, bool designTime)
        => _i++;
}

public class Benchmarks
{
    [Benchmark]
    public void NoCachingSyncTableModel()
    {
        _ = new NonModelCachingContext().Model;
    }

    [Benchmark]
    public void CachingSyncTableModel()
    {
        _ = new ModelCachingContext().Model;
    }

    [Benchmark]
    public void NoCachingSimpleModel()
    {
        _ = new NonModelCachingSimpleContext().Model;
    }

    [Benchmark]
    public void CachingSimpleModel()
    {
        _ = new ModelCachingSimpleContext().Model;
    }
}

public class Program
{
    public static void Main()
    {
        BenchmarkRunner.Run<Benchmarks>();
    }
}

Thank you very much for testing this.

Maybe it is the connection handshake with the SQL server and the lambda. I'll turn on EF logging to see what's going on. I'll keep you posted.

Thank you very much for testing this. Much appreciated.

roji · 2022-12-13T15:41:22Z

Note for triage: On my benchmark, model building seems to be an order of magnitude slower on EF Core 7 than EF Core 6.

This could possibly be #29642?

ajcvickers · 2022-12-13T16:24:49Z

@roji The simple model here is nine entity types with only a primary key property each and no relationships. Would #29642 happen for such a model?

roji · 2022-12-13T16:29:37Z

I'm not sure (would probably have less of an impacT)... It may be good to measure with @AndriySvyryd's fix to make sure...

stevendarby · 2022-12-13T17:57:08Z

The model building perf regression in EF 7 has been known for a while... #28129

AndriySvyryd · 2022-12-13T18:00:31Z

@roji The simple model here is nine entity types with only a primary key property each and no relationships. Would #29642 happen for such a model?

Yes. That bug specifically is triggered when there are no FKs on the given property

AndriySvyryd · 2022-12-13T19:11:54Z

Confirmed that most of the regression is gone after #29807

The rest will be handled by #9329 and #25488

RomeoQNgo · 2022-12-13T19:50:12Z

@AndriySvyryd
I turn on full debug and please see the log below.

Time -- Action
14:47.255 -- right before using EF
14:48.734 -- Entity Framework Core 6.0.4 initialized
14:49.355 -- Compiling query expression
14:49.954 -- Including navigation: 'SyncTable.Fields'
14:52.113 -- Generated query execution expression
14:52.482 -- Creating DbCommand for 'ExecuteReader'
14:52.735 -- Created DbCommand for 'ExecuteReader'
14:52.833 -- Opening connection to database
14:54.473 -- Opened connection to database
14:54.553 -- Executing DbCommand
14:54.955 -- Executed DbCommand
14:55.155 -- A data reader was disposed
14:55.174 -- Closing connection to database
14:55.215 -- Closed connection to database
14:55.216 -- end using EF

It takes about 5.5 seconds to compile query expression and create DbCommand, 1.5 seconds to open connection. This is using Lambda of 256MB. Total duration: 8 seconds.

When I up the lambda to use 1024MB, this process takes about 1.75 second.

I guess I need to up the lambda memory size. I'm sure it is a tradeoff between speed and memory size which is part of the cost. Is there a recommended Memory Size for EF?

Also, I am using the compiled models incorrectly? why does it need to compile the query if we have compiled models? I do see significant improvement after I use the compiled models though.

AndriySvyryd · 2022-12-13T19:53:49Z

Is there a recommended Memory Size for EF?

It really depends on your model and application, measuring performance under real-world conditions is the best way of determining this.

RomeoQNgo · 2022-12-13T19:54:44Z

@AndriySvyryd

I am using the compiled models incorrectly? why does it need to compile the query if we have compiled models? I do see significant improvement after I use the compiled models though.

AndriySvyryd · 2022-12-13T20:28:58Z

@RomeoQNgo Compiled model only speeds up model building. Query compilation is unaffected when you use it.

RomeoQNgo · 2022-12-13T20:42:04Z

@AndriySvyryd gotcha. Is there anyway we can build compiled queries like compiled models? In my case, LINQ statements are used to turn into SQLCommand. I'm using a data Service layers that use DbContext. Is there anyway I can set EF to use a compiled query in Data Service Layer?

AndriySvyryd · 2022-12-13T20:56:19Z

Is there anyway I can set EF to use a compiled query in Data Service Layer?

Not yet: #29764

RomeoQNgo · 2022-12-13T21:04:59Z

@AndriySvyryd Good to know. thank you very much for your helps.

I'm looking forward to that feature. It will be awesome for lambda.

AndriySvyryd added the closed-duplicate label Dec 13, 2022

AndriySvyryd closed this as not planned Won't fix, can't repro, duplicate, stale Dec 13, 2022

ajcvickers added the customer-reported label Dec 13, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

First Query take 8 seconds even with Compiled models #29827

First Query take 8 seconds even with Compiled models #29827

RomeoQNgo commented Dec 12, 2022

ajcvickers commented Dec 12, 2022

roji commented Dec 12, 2022

RomeoQNgo commented Dec 12, 2022

RomeoQNgo commented Dec 12, 2022 •

edited

Loading

ajcvickers commented Dec 13, 2022 •

edited

Loading

RomeoQNgo commented Dec 13, 2022

EF Core 60.11

EF Core 7.0.0

roji commented Dec 13, 2022

ajcvickers commented Dec 13, 2022

roji commented Dec 13, 2022

stevendarby commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022 •

edited

Loading

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022

First Query take 8 seconds even with Compiled models #29827

First Query take 8 seconds even with Compiled models #29827

Comments

RomeoQNgo commented Dec 12, 2022

ajcvickers commented Dec 12, 2022

roji commented Dec 12, 2022

RomeoQNgo commented Dec 12, 2022

RomeoQNgo commented Dec 12, 2022 • edited Loading

ajcvickers commented Dec 13, 2022 • edited Loading

EF Core 60.11

EF Core 7.0.0

RomeoQNgo commented Dec 13, 2022

EF Core 60.11

EF Core 7.0.0

roji commented Dec 13, 2022

ajcvickers commented Dec 13, 2022

roji commented Dec 13, 2022

stevendarby commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022 • edited Loading

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022

AndriySvyryd commented Dec 13, 2022

RomeoQNgo commented Dec 13, 2022

RomeoQNgo commented Dec 12, 2022 •

edited

Loading

ajcvickers commented Dec 13, 2022 •

edited

Loading

RomeoQNgo commented Dec 13, 2022 •

edited

Loading