Performance Tuning of Entity Framework Core with Azure SQL Server

Introduction

Entity Framework Core allows you to use the navigation properties in your model to load related entities. There are three common O/RM patterns used to load related data.

• Eager loading means that the related data is loaded from the database as part of the initial query.
• Explicit loading means that the related data is explicitly loaded from the database at a later time.
• Lazy loading means that the related data is transparently loaded from the database when the navigation property is accessed.

Eager loading is the default loading of EF Core.

Understanding the query of Eager Loading

var matters = ctx.Matters.Where(x => x.MatterMatter == projectId)

                .Include(x => x.MatterDates)

                .Include(x => x.PropertyTitles)

                .Include(x => x.MatterContacts)

                .Include(x => x.MatterClients);

What’s the query for the above code? Use Sql Profiler can get the real query executed on the database. How to use Sql Profiler is a big topic, we don’t discuss here.

The corresponding SQL query is,

SELECT [m].[Id], [m].[BalanceDeposit], [m].[BalanceDepositDatePaid], [m].[BalanceDue], [m].[CaveatOnTitle], [m].[Created], [m].[CreatedBy], [m].[DeactivateCriticalDatesTasks], [m].[DepositOutstanding], [m].[FirstHomeOwner], [m].[ForeignPurchaser], [m].[HiddenLastModified], [m].[IncludeGST], [m].[InitialDeposit], [m].[InitialDepositDatePaid], [m].[InterestOnDeposit], [m].[Investment], [m].[isCGTCleared], [m].[isCombinedSalutationUpdated], [m].[IsCopy], [m].[IsDepositAmountRequired], [m].[IsMasterMatter], [m].[IsPexa], [m].[isReferenceNoEditable], [m].[IsSetteled], [m].[IsShared], [m].[LoanProceeds], [m].[LockReferrer], [m].[LotLiabilityTotal], [m].[LotLiabilityUnit], [m].[Matter_AcquisitionOverlay], [m].[MatterDescription], [m].[Matter_Employee], [m].[Matter_Firm], [m].[Matter_Matter], [m].[Matter_MatterReferrer], [m].[Matter_State], [m].[Matter_StateAcquisitionCode], [m].[Matter_Suburb], [m].[MatterType], [m].[Matter_Workflow], [m].[Matter_Zone1], [m].[Modified], [m].[ModifiedBy], [m].[MortgageOnTitle], [m].[MortgagePayoutAmount], [m].[NameForMail], [m].[Notes], [m].[OffPlanDutyConcession], [m].[Other], [m].[PensionerExemption], [m].[PexaworkspaceID], [m].[Price], [m].[PrincipalPlaceOfResidence], [m].[Project], [m].[PropertyPostCode], [m].[PropertyState], [m].[PropertyStreetName], [m].[PropertyStreetNo], [m].[PropertyStreetType], [m].[PropertySubdivisionVersion], [m].[PropertyUnitNo], [m].[PropertyUnitType], [m].[ReferenceNo], [m].[Referrer], [m].[RegistrationFees], [m].[RowVersion], [m].[Salutation], [m].[SettlementDate], [m].[SettlementLocation_Matter], [m].[SettlementTime], [m].[StampDuty], [m].[Status], [m].[TotalCashDeposit], [m].[TotalDeposit], [m].[TotalNonCashDeposit], [m].[TotalOptionalExtras]

      FROM [Matters] AS [m]

      WHERE [m].[Matter_Matter] = @__masterMatterId_0

      ORDER BY [m].[Id]

Executed DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT [x.MatterDates].[Id], [x.MatterDates].[Created], [x.MatterDates].[CreatedBy], [x.MatterDates].[DateName], [x.MatterDates].[EventDate], [x.MatterDates].[MatterDate_MasterDate], [x.MatterDates].[MatterDate_Matter], [x.MatterDates].[Modified], [x.MatterDates].[ModifiedBy], [x.MatterDates].[RowVersion]

      FROM [MatterDates] AS [x.MatterDates]


SELECT [m1].[Id], [m1].[BalanceDeposit], [m1].[BalanceDepositDatePaid], [m1].[BalanceDue], [m1].[CaveatOnTitle], [m1].[Created], [m1].[CreatedBy], [m1].[DeactivateCriticalDatesTasks], [m1].[DepositOutstanding], [m1].[FirstHomeOwner], [m1].[ForeignPurchaser], [m1].[HiddenLastModified], [m1].[IncludeGST], [m1].[InitialDeposit], [m1].[InitialDepositDatePaid], [m1].[InterestOnDeposit], [m1].[Investment], [m1].[isCGTCleared], [m1].[isCombinedSalutationUpdated], [m1].[IsCopy], [m1].[IsDepositAmountRequired], [m1].[IsMasterMatter], [m1].[IsPexa], [m1].[isReferenceNoEditable], [m1].[IsSetteled], [m1].[IsShared], [m1].[LoanProceeds], [m1].[LockReferrer], [m1].[LotLiabilityTotal], [m1].[LotLiabilityUnit], [m1].[Matter_AcquisitionOverlay], [m1].[MatterDescription], [m1].[Matter_Employee], [m1].[Matter_Firm], [m1].[Matter_Matter], [m1].[Matter_MatterReferrer], [m1].[Matter_State], [m1].[Matter_StateAcquisitionCode], [m1].[Matter_Suburb], [m1].[MatterType], [m1].[Matter_Workflow], [m1].[Matter_Zone1], [m1].[Modified], [m1].[ModifiedBy], [m1].[MortgageOnTitle], [m1].[MortgagePayoutAmount], [m1].[NameForMail], [m1].[Notes], [m1].[OffPlanDutyConcession], [m1].[Other], [m1].[PensionerExemption], [m1].[PexaworkspaceID], [m1].[Price], [m1].[PrincipalPlaceOfResidence], [m1].[Project], [m1].[PropertyPostCode], [m1].[PropertyState], [m1].[PropertyStreetName], [m1].[PropertyStreetNo], [m1].[PropertyStreetType], [m1].[PropertySubdivisionVersion], [m1].[PropertyUnitNo], [m1].[PropertyUnitType], [m1].[ReferenceNo], [m1].[Referrer], [m1].[RegistrationFees], [m1].[RowVersion], [m1].[Salutation], [m1].[SettlementDate], [m1].[SettlementLocation_Matter], [m1].[SettlementTime], [m1].[StampDuty], [m1].[Status], [m1].[TotalCashDeposit], [m1].[TotalDeposit], [m1].[TotalNonCashDeposit], [m1].[TotalOptionalExtras]

      FROM [Matters] AS [m1]

      WHERE [m1].[Matter_Matter] = @__masterMatterId_0


SELECT [x.PropertyTitles].[Id], [x.PropertyTitles].[Created], [x.PropertyTitles].[CreatedBy], [x.PropertyTitles].[Folio], [x.PropertyTitles].[LastVerified], [x.PropertyTitles].[Lot], [x.PropertyTitles].[LotType], [x.PropertyTitles].[Modified], [x.PropertyTitles].[ModifiedBy], [x.PropertyTitles].[PlanNo], [x.PropertyTitles].[PropertyTitle_Matter], [x.PropertyTitles].[PropertyTitle_PlanType], [x.PropertyTitles].[RowVersion], [x.PropertyTitles].[TitleReference], [x.PropertyTitles].[Valid], [x.PropertyTitles].[Volume]

      FROM [PropertyTitles] AS [x.PropertyTitles]


SELECT [m3].[Id], [m3].[BalanceDeposit], [m3].[BalanceDepositDatePaid], [m3].[BalanceDue], [m3].[CaveatOnTitle], [m3].[Created], [m3].[CreatedBy], [m3].[DeactivateCriticalDatesTasks], [m3].[DepositOutstanding], [m3].[FirstHomeOwner], [m3].[ForeignPurchaser], [m3].[HiddenLastModified], [m3].[IncludeGST], [m3].[InitialDeposit], [m3].[InitialDepositDatePaid], [m3].[InterestOnDeposit], [m3].[Investment], [m3].[isCGTCleared], [m3].[isCombinedSalutationUpdated], [m3].[IsCopy], [m3].[IsDepositAmountRequired], [m3].[IsMasterMatter], [m3].[IsPexa], [m3].[isReferenceNoEditable], [m3].[IsSetteled], [m3].[IsShared], [m3].[LoanProceeds], [m3].[LockReferrer], [m3].[LotLiabilityTotal], [m3].[LotLiabilityUnit], [m3].[Matter_AcquisitionOverlay], [m3].[MatterDescription], [m3].[Matter_Employee], [m3].[Matter_Firm], [m3].[Matter_Matter], [m3].[Matter_MatterReferrer], [m3].[Matter_State], [m3].[Matter_StateAcquisitionCode], [m3].[Matter_Suburb], [m3].[MatterType], [m3].[Matter_Workflow], [m3].[Matter_Zone1], [m3].[Modified], [m3].[ModifiedBy], [m3].[MortgageOnTitle], [m3].[MortgagePayoutAmount], [m3].[NameForMail], [m3].[Notes], [m3].[OffPlanDutyConcession], [m3].[Other], [m3].[PensionerExemption], [m3].[PexaworkspaceID], [m3].[Price], [m3].[PrincipalPlaceOfResidence], [m3].[Project], [m3].[PropertyPostCode], [m3].[PropertyState], [m3].[PropertyStreetName], [m3].[PropertyStreetNo], [m3].[PropertyStreetType], [m3].[PropertySubdivisionVersion], [m3].[PropertyUnitNo], [m3].[PropertyUnitType], [m3].[ReferenceNo], [m3].[Referrer], [m3].[RegistrationFees], [m3].[RowVersion], [m3].[Salutation], [m3].[SettlementDate], [m3].[SettlementLocation_Matter], [m3].[SettlementTime], [m3].[StampDuty], [m3].[Status], [m3].[TotalCashDeposit], [m3].[TotalDeposit], [m3].[TotalNonCashDeposit], [m3].[TotalOptionalExtras]

      FROM [Matters] AS [m3]

      WHERE [m3].[Matter_Matter] = @__masterMatterId_0


SELECT [x.MatterContacts].[Id], [x.MatterContacts].[Created], [x.MatterContacts].[CreatedBy], [x.MatterContacts].[IsGuaranteeAndIndemnitySigned], [x.MatterContacts].[IsReferrer], [x.MatterContacts].[LoanNumber], [x.MatterContacts].[MatterCategory], [x.MatterContacts].[MatterContact_BranchContact], [x.MatterContacts].[MatterContact_Matter], [x.MatterContacts].[MatterContact_OtherContact], [x.MatterContacts].[MatterContact_OtherContactBranch], [x.MatterContacts].[Modified], [x.MatterContacts].[ModifiedBy], [x.MatterContacts].[Reference], [x.MatterContacts].[RowVersion], [x.MatterContacts].[UpdateIndicatorDate]

      FROM [MatterContacts] AS [x.MatterContacts]

SELECT [m5].[Id], [m5].[BalanceDeposit], [m5].[BalanceDepositDatePaid], [m5].[BalanceDue], [m5].[CaveatOnTitle], [m5].[Created], [m5].[CreatedBy], [m5].[DeactivateCriticalDatesTasks], [m5].[DepositOutstanding], [m5].[FirstHomeOwner], [m5].[ForeignPurchaser], [m5].[HiddenLastModified], [m5].[IncludeGST], [m5].[InitialDeposit], [m5].[InitialDepositDatePaid], [m5].[InterestOnDeposit], [m5].[Investment], [m5].[isCGTCleared], [m5].[isCombinedSalutationUpdated], [m5].[IsCopy], [m5].[IsDepositAmountRequired], [m5].[IsMasterMatter], [m5].[IsPexa], [m5].[isReferenceNoEditable], [m5].[IsSetteled], [m5].[IsShared], [m5].[LoanProceeds], [m5].[LockReferrer], [m5].[LotLiabilityTotal], [m5].[LotLiabilityUnit], [m5].[Matter_AcquisitionOverlay], [m5].[MatterDescription], [m5].[Matter_Employee], [m5].[Matter_Firm], [m5].[Matter_Matter], [m5].[Matter_MatterReferrer], [m5].[Matter_State], [m5].[Matter_StateAcquisitionCode], [m5].[Matter_Suburb], [m5].[MatterType], [m5].[Matter_Workflow], [m5].[Matter_Zone1], [m5].[Modified], [m5].[ModifiedBy], [m5].[MortgageOnTitle], [m5].[MortgagePayoutAmount], [m5].[NameForMail], [m5].[Notes], [m5].[OffPlanDutyConcession], [m5].[Other], [m5].[PensionerExemption], [m5].[PexaworkspaceID], [m5].[Price], [m5].[PrincipalPlaceOfResidence], [m5].[Project], [m5].[PropertyPostCode], [m5].[PropertyState], [m5].[PropertyStreetName], [m5].[PropertyStreetNo], [m5].[PropertyStreetType], [m5].[PropertySubdivisionVersion], [m5].[PropertyUnitNo], [m5].[PropertyUnitType], [m5].[ReferenceNo], [m5].[Referrer], [m5].[RegistrationFees], [m5].[RowVersion], [m5].[Salutation], [m5].[SettlementDate], [m5].[SettlementLocation_Matter], [m5].[SettlementTime], [m5].[StampDuty], [m5].[Status], [m5].[TotalCashDeposit], [m5].[TotalDeposit], [m5].[TotalNonCashDeposit], [m5].[TotalOptionalExtras]

      FROM [Matters] AS [m5]

      WHERE [m5].[Matter_Matter] = @__masterMatterId_0

That’s query becomes horrible when matters table have thousands of record. In fact it took 2 minutes to return the result.

Optimize Query

• Create the index properly

Create non-cluster index for [Matters].[Matter_Matter]

After create the index, the query can finish in 30 -45 seconds.

• Optimize the code

var matterIds = ctx.Matters.Where(x => x.MatterMatter == masterMatterId)

        .Select(x => x.Id).ToList();

      var matters = ctx.Matters

        .Where(x => matterIds.Contains(x.Id))

        .Include(x => x.MatterDates)

        .Include(x => x.PropertyTitles)

        .Include(x => x.MatterContacts)

        .Include(x => x.MatterClients);

SELECT [m].[Id], [m].[BalanceDeposit], [m].[BalanceDepositDatePaid], [m].[BalanceDue], [m].[CaveatOnTitle], [m].[Created], [m].[CreatedBy], [m].[DeactivateCriticalDatesTasks], [m].[DepositOutstanding], [m].[FirstHomeOwner], [m].[ForeignPurchaser], [m].[HiddenLastModified], [m].[IncludeGST], [m].[InitialDeposit], [m].[InitialDepositDatePaid], [m].[InterestOnDeposit], [m].[Investment], [m].[isCGTCleared], [m].[isCombinedSalutationUpdated], [m].[IsCopy], [m].[IsDepositAmountRequired], [m].[IsMasterMatter], [m].[IsPexa], [m].[isReferenceNoEditable], [m].[IsSetteled], [m].[IsShared], [m].[LoanProceeds], [m].[LockReferrer], [m].[LotLiabilityTotal], [m].[LotLiabilityUnit], [m].[Matter_AcquisitionOverlay], [m].[MatterDescription], [m].[Matter_Employee], [m].[Matter_Firm], [m].[Matter_Matter], [m].[Matter_MatterReferrer], [m].[Matter_State], [m].[Matter_StateAcquisitionCode], [m].[Matter_Suburb], [m].[MatterType], [m].[Matter_Workflow], [m].[Matter_Zone1], [m].[Modified], [m].[ModifiedBy], [m].[MortgageOnTitle], [m].[MortgagePayoutAmount], [m].[NameForMail], [m].[Notes], [m].[OffPlanDutyConcession], [m].[Other], [m].[PensionerExemption], [m].[PexaworkspaceID], [m].[Price], [m].[PrincipalPlaceOfResidence], [m].[Project], [m].[PropertyPostCode], [m].[PropertyState], [m].[PropertyStreetName], [m].[PropertyStreetNo], [m].[PropertyStreetType], [m].[PropertySubdivisionVersion], [m].[PropertyUnitNo], [m].[PropertyUnitType], [m].[ReferenceNo], [m].[Referrer], [m].[RegistrationFees], [m].[RowVersion], [m].[Salutation], [m].[SettlementDate], [m].[SettlementLocation_Matter], [m].[SettlementTime], [m].[StampDuty], [m].[Status], [m].[TotalCashDeposit], [m].[TotalDeposit], [m].[TotalNonCashDeposit], [m].[TotalOptionalExtras]

      FROM [Matters] AS [m]

      WHERE [m].[Matter_Matter] = @__masterMatterId_0


SELECT [x].[Id], [x].[BalanceDeposit], [x].[BalanceDepositDatePaid], [x].[BalanceDue], [x].[CaveatOnTitle], [x].[Created], [x].[CreatedBy], [x].[DeactivateCriticalDatesTasks], [x].[DepositOutstanding], [x].[FirstHomeOwner], [x].[ForeignPurchaser], [x].[HiddenLastModified], [x].[IncludeGST], [x].[InitialDeposit], [x].[InitialDepositDatePaid], [x].[InterestOnDeposit], [x].[Investment], [x].[isCGTCleared], [x].[isCombinedSalutationUpdated], [x].[IsCopy], [x].[IsDepositAmountRequired], [x].[IsMasterMatter], [x].[IsPexa], [x].[isReferenceNoEditable], [x].[IsSetteled], [x].[IsShared], [x].[LoanProceeds], [x].[LockReferrer], [x].[LotLiabilityTotal], [x].[LotLiabilityUnit], [x].[Matter_AcquisitionOverlay], [x].[MatterDescription], [x].[Matter_Employee], [x].[Matter_Firm], [x].[Matter_Matter], [x].[Matter_MatterReferrer], [x].[Matter_State], [x].[Matter_StateAcquisitionCode], [x].[Matter_Suburb], [x].[MatterType], [x].[Matter_Workflow], [x].[Matter_Zone1], [x].[Modified], [x].[ModifiedBy], [x].[MortgageOnTitle], [x].[MortgagePayoutAmount], [x].[NameForMail], [x].[Notes], [x].[OffPlanDutyConcession], [x].[Other], [x].[PensionerExemption], [x].[PexaworkspaceID], [x].[Price], [x].[PrincipalPlaceOfResidence], [x].[Project], [x].[PropertyPostCode], [x].[PropertyState], [x].[PropertyStreetName], [x].[PropertyStreetNo], [x].[PropertyStreetType], [x].[PropertySubdivisionVersion], [x].[PropertyUnitNo], [x].[PropertyUnitType], [x].[ReferenceNo], [x].[Referrer], [x].[RegistrationFees], [x].[RowVersion], [x].[Salutation], [x].[SettlementDate], [x].[SettlementLocation_Matter], [x].[SettlementTime], [x].[StampDuty], [x].[Status], [x].[TotalCashDeposit], [x].[TotalDeposit], [x].[TotalNonCashDeposit], [x].[TotalOptionalExtras]

      FROM [Matters] AS [x]

      WHERE [x].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ORDER BY [x].[Id]


SELECT [x.MatterDates].[Id], [x.MatterDates].[Created], [x.MatterDates].[CreatedBy], [x.MatterDates].[DateName], [x.MatterDates].[EventDate], [x.MatterDates].[MatterDate_MasterDate], [x.MatterDates].[MatterDate_Matter], [x.MatterDates].[Modified], [x.MatterDates].[ModifiedBy], [x.MatterDates].[RowVersion]

      FROM [MatterDates] AS [x.MatterDates]

      INNER JOIN (

          SELECT [x0].[Id]

          FROM [Matters] AS [x0]

          WHERE [x0].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ) AS [t] ON [x.MatterDates].[MatterDate_Matter] = [t].[Id]

      ORDER BY [t].[Id]


SELECT [x.PropertyTitles].[Id], [x.PropertyTitles].[Created], [x.PropertyTitles].[CreatedBy], [x.PropertyTitles].[Folio], [x.PropertyTitles].[LastVerified], [x.PropertyTitles].[Lot], [x.PropertyTitles].[LotType], [x.PropertyTitles].[Modified], [x.PropertyTitles].[ModifiedBy], [x.PropertyTitles].[PlanNo], [x.PropertyTitles].[PropertyTitle_Matter], [x.PropertyTitles].[PropertyTitle_PlanType], [x.PropertyTitles].[RowVersion], [x.PropertyTitles].[TitleReference], [x.PropertyTitles].[Valid], [x.PropertyTitles].[Volume]

      FROM [PropertyTitles] AS [x.PropertyTitles]

      INNER JOIN (

          SELECT [x1].[Id]

          FROM [Matters] AS [x1]

          WHERE [x1].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ) AS [t0] ON [x.PropertyTitles].[PropertyTitle_Matter] = [t0].[Id]

      ORDER BY [t0].[Id]


SELECT [x.MatterContacts].[Id], [x.MatterContacts].[Created], [x.MatterContacts].[CreatedBy], [x.MatterContacts].[IsGuaranteeAndIndemnitySigned], [x.MatterContacts].[IsReferrer], [x.MatterContacts].[LoanNumber], [x.MatterContacts].[MatterCategory], [x.MatterContacts].[MatterContact_BranchContact], [x.MatterContacts].[MatterContact_Matter], [x.MatterContacts].[MatterContact_OtherContact], [x.MatterContacts].[MatterContact_OtherContactBranch], [x.MatterContacts].[Modified], [x.MatterContacts].[ModifiedBy], [x.MatterContacts].[Reference], [x.MatterContacts].[RowVersion], [x.MatterContacts].[UpdateIndicatorDate]

      FROM [MatterContacts] AS [x.MatterContacts]

      INNER JOIN (

          SELECT [x2].[Id]

          FROM [Matters] AS [x2]

          WHERE [x2].[Id] IN (3263, 3283, 3320, 3325, 3384, 3386, 3438)

      ) AS [t1] ON [x.MatterContacts].[MatterContact_Matter] = [t1].[Id]

      ORDER BY [t1].[Id]

SELECT query_stats.query_hash AS "Query Hash",

    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

    MIN(query_stats.statement_text) AS "Statement Text"

FROM

    (SELECT QS.*,

    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

        WHEN -1 THEN DATALENGTH(ST.text)

        ELSE QS.statement_end_offset END

            - QS.statement_start_offset)/2) + 1) AS statement_text

     FROM sys.dm_exec_query_stats AS QS

     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

public void ConfigureServices(IServiceCollection services)

 {

     services.AddLogging(loggingBuilder =>

     loggingBuilder.AddSerilog(dispose: true));


 }

public DbContextService(IOptions<AdminServiceSettings> settings, IUserProfileService userProfileService,

      ILogger logger, ILoggerFactory loggerFactory)

    {

      _settings = settings.Value;

      _logger = logger;

      _userProfileService = userProfileService;

      _loggerFactory = loggerFactory;

      _adminContextOptions =

        new DbContextOptionsBuilder<AdminContext>().UseSqlServer(_settings.AdminConnectionString).Options;

}

DbContextOptions<MatterContext> contextOptions =

          new DbContextOptionsBuilder<MatterContext>()

            .UseLoggerFactory(_loggerFactory)

            .UseSqlServer(connectionString).Options;

public void ConfigureServices(IServiceCollection services)
{

    services.AddEFSecondLevelCache();


    // Add an in-memory cache service provider

    services.AddSingleton(typeof(ICacheManager<>), typeof(BaseCacheManager<>));

    services.AddSingleton(typeof(ICacheManagerConfiguration),

    new CacheManager.Core.ConfigurationBuilder()

           .WithJsonSerializer()

           .WithMicrosoftMemoryCacheHandle()

           .WithExpiration(ExpirationMode.Absolute, TimeSpan.FromMinutes(10))

           .Build());

}

public void Configure(IApplicationBuilder app)
{

     app.UseEFSecondLevelCache();

}

public override int SaveChanges()

{

      this.ChangeTracker.DetectChanges();

      var changedEntityNames = this.GetChangedEntityNames();


      var result = base.SaveChanges();

      this.GetService<IEFCacheServiceProvider>().InvalidateCacheDependencies(changedEntityNames);


      return result;
}

var products = context.Products.Include(x => x.Tags).Cacheable().FirstOrDefault();