Excesive custom entities loadding time

[ernestoSerra]

Hi Joel, I created custom entities for my site. After I tried to get all these custom enties but it takes a long time to load. This is the used code to get all custom entities data:

  var query = new GetCustomEntityRenderSummariesByDefinitionCodeQuery(MyCustomEntityDefinition.DefinitionCode, PublishStatusQuery.Published);
  var entities = await _customEntityRepository.GetCustomEntityRenderSummariesByDefinitionCodeAsync(query);

I think this is because custom entities are very large. For this reason, I want to know if custom entities data are cached or if there is any way to make it cacheable.

Thanks

[HeyJoel]

Hi, can you please clarify a few things for me:

• How many custom entities are you loading?
• How long is it taking to load on average?
• Is this production/dev and where is the SQLServer located?

Custom entity summaries are not cached, only the routing data is (to keep the cache size small). If you can get away with just the simple routing data you can use _customEntityRepository.GetCustomEntityRoutesByDefinitionCodeAsync(MyCustomEntityDefinition.DefinitionCode), but that might not be sufficient for your needs.

If your data set is large you can use the search query instead e.g. _customEntityRepository.SearchCustomEntityRenderSummariesAsync(query) and page the data.

Caching is a complex subject and strategies will depend on your data so we don't have "cache everything" option. What we should have at some point is a configurable caching layer, but we don't have that yet (see #253). You can of course add your own cache wrapper (see caching docs, or the comments in #253.

[ernestoSerra]

Hi, Thanks for all, I tried to load 25 custom entities and it takes 7 seconds to load in production and development enviroments. Data base is located in azure (production) and local (dev). We need the custom entities data set, so custom entity route will not be suficient.

[HeyJoel]

Something's not right there. We see much faster performance over much larger data sets. Some things to think about:

• Have you isolated the problem to Cofoundry or could it be something else? E.g. are you talking about 7s page load?
• Is this first load or also 7s on subsequent fetches?
• This shouldn't make a difference, but what other custom entity data do you have in there - do you have 1000s of other custom entity records of a different type?
• Is this a problem that has only just started occurring? If so, what changed?

[ernestoSerra]

Hi, 7s is the time that need to load page when I call an Contoller API to get a custom entities JSON . I tried to to find the part in controller that caused the problem and it is given by the two lines that I posted at the first comment. These custom entities have a field that represents a group of coordinates and it is very large. I think that it could be the probleme.

Thanks

[HeyJoel]

The custom entity model data is simply serialized JSON, so I wouldn't expect that to have such an impact but I suppose it would depend on how large "very large" was. I'll send some code over in a bit that you can use to debug query handler and see which part is causing the slowdown.

[ernestoSerra]

Thanks for all.

[HeyJoel]

Here's some code you can use to debug your issue, I've overridden both the query handler and model mapper so you can debug both. Note you can use this technique to debug other injected services and Cofoundry packages also support source link so if you have that enabled you should be able to step into code:

using Cofoundry.Core;
using Cofoundry.Core.DependencyInjection;
using Cofoundry.Domain;
using Cofoundry.Domain.CQS;
using Cofoundry.Domain.Data;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TestCode
{
    public class TestDependencyRegistration : IDependencyRegistration
    {
        public void Register(IContainerRegister container)
        {
            var overrideOptions = RegistrationOptions.Override();

            // Here we can override the Cofoundry implementation with our own code which we can use to debug
            container.Register<IAsyncQueryHandler<GetCustomEntityRenderSummariesByDefinitionCodeQuery, ICollection<CustomEntityRenderSummary>>, TestGetCustomEntityRenderSummariesByDefinitionCodeQueryHandler>(overrideOptions);
            container.Register<ICustomEntityRenderSummaryMapper, TestCustomEntityRenderSummaryMapper>(overrideOptions);
        }
    }

    /// <summary>
    /// Code copied from Cofoundry source: GetCustomEntityRenderSummariesByDefinitionCodeQueryHandler.cs
    /// </summary>
    public class TestGetCustomEntityRenderSummariesByDefinitionCodeQueryHandler
        : IAsyncQueryHandler<GetCustomEntityRenderSummariesByDefinitionCodeQuery, ICollection<CustomEntityRenderSummary>>
        , IPermissionRestrictedQueryHandler<GetCustomEntityRenderSummariesByDefinitionCodeQuery, ICollection<CustomEntityRenderSummary>>
    {
        private readonly CofoundryDbContext _dbContext;
        private readonly ICustomEntityRenderSummaryMapper _customEntityRenderSummaryMapper;
        private readonly ICustomEntityDefinitionRepository _customEntityDefinitionRepository;

        public TestGetCustomEntityRenderSummariesByDefinitionCodeQueryHandler(
            CofoundryDbContext dbContext,
            ICustomEntityRenderSummaryMapper customEntityRenderSummaryMapper,
            ICustomEntityDefinitionRepository customEntityDefinitionRepository
            )
        {
            _dbContext = dbContext;
            _customEntityRenderSummaryMapper = customEntityRenderSummaryMapper;
            _customEntityDefinitionRepository = customEntityDefinitionRepository;
        }

        public async Task<ICollection<CustomEntityRenderSummary>> ExecuteAsync(GetCustomEntityRenderSummariesByDefinitionCodeQuery query, IExecutionContext executionContext)
        {
            var dbResults = await QueryAsync(query, executionContext);
            var results = await _customEntityRenderSummaryMapper.MapAsync(dbResults, executionContext);

            return results;
        }

        private async Task<List<CustomEntityVersion>> QueryAsync(GetCustomEntityRenderSummariesByDefinitionCodeQuery query, IExecutionContext executionContext)
        {
            if (query.PublishStatus == PublishStatusQuery.SpecificVersion)
            {
                throw new InvalidOperationException("PublishStatusQuery.SpecificVersion not supported in GetCustomEntityRenderSummariesByDefinitionCodeQuery");
            }

            var dbResults = await _dbContext
                .CustomEntityPublishStatusQueries
                .AsNoTracking()
                .Include(e => e.CustomEntityVersion)
                .ThenInclude(e => e.CustomEntity)
                .FilterActive()
                .FilterByCustomEntityDefinitionCode(query.CustomEntityDefinitionCode)
                .FilterByStatus(query.PublishStatus, executionContext.ExecutionDate)
                .ToListAsync();

            // EF doesn't allow includes after selects, so re-filter the results.

            return dbResults
                .Select(e => e.CustomEntityVersion)
                .ToList();
        }

        #region Permission

        public IEnumerable<IPermissionApplication> GetPermissions(GetCustomEntityRenderSummariesByDefinitionCodeQuery query)
        {
            var definition = _customEntityDefinitionRepository.GetByCode(query.CustomEntityDefinitionCode);
            yield return new CustomEntityReadPermission(definition);
        }

        #endregion
    }

    /// <summary>
    /// Code copied from Cofoundry source: CustomEntityRenderSummaryMapper.cs
    /// </summary>
    public class TestCustomEntityRenderSummaryMapper : ICustomEntityRenderSummaryMapper
    {
        #region constructor

        private readonly CofoundryDbContext _dbContext;
        private readonly ICustomEntityDataModelMapper _customEntityDataModelMapper;
        private readonly IQueryExecutor _queryExecutor;
        private readonly ICustomEntityDefinitionRepository _customEntityDefinitionRepository;

        public TestCustomEntityRenderSummaryMapper(
            CofoundryDbContext dbContext,
            IQueryExecutor queryExecutor,
            ICustomEntityDataModelMapper customEntityDataModelMapper,
            ICustomEntityDefinitionRepository customEntityDefinitionRepository
            )
        {
            _dbContext = dbContext;
            _customEntityDataModelMapper = customEntityDataModelMapper;
            _queryExecutor = queryExecutor;
            _customEntityDefinitionRepository = customEntityDefinitionRepository;
        }

        #endregion

        /// <summary>
        /// Maps an EF CustomEntityVersion record from the db into a CustomEntityRenderSummary 
        /// object. If the db record is null then null is returned.
        /// </summary>
        /// <param name="dbResult">CustomEntityVersion record from the database.</param>
        /// <param name="executionContext">Context to run any sub queries under.</param>
        public async Task<CustomEntityRenderSummary> MapAsync(
            CustomEntityVersion dbResult,
            IExecutionContext executionContext
            )
        {
            if (dbResult == null) return null;

            var routingQuery = GetPageRoutingQuery(dbResult);
            var routing = await _queryExecutor.ExecuteAsync(routingQuery, executionContext);

            ActiveLocale locale = null;
            if (dbResult.CustomEntity.LocaleId.HasValue)
            {
                var getLocaleQuery = new GetActiveLocaleByIdQuery(dbResult.CustomEntity.LocaleId.Value);
                locale = await _queryExecutor.ExecuteAsync(getLocaleQuery, executionContext);
            }

            return MapSingle(dbResult, routing, locale);
        }

        /// <summary>
        /// Maps a collection of EF CustomEntityVersion record from the db into CustomEntityRenderSummary 
        /// objects.
        /// </summary>
        /// <param name="dbResult">CustomEntityVersion records from the database.</param>
        /// <param name="executionContext">Context to run any sub queries under.</param>
        public async Task<ICollection<CustomEntityRenderSummary>> MapAsync(
            ICollection<CustomEntityVersion> dbResults,
            IExecutionContext executionContext
            )
        {
            var routingsQuery = GetPageRoutingQuery(dbResults);
            var allRoutings = await _queryExecutor.ExecuteAsync(routingsQuery, executionContext);
            var allLocales = await _queryExecutor.ExecuteAsync(new GetAllActiveLocalesQuery(), executionContext);

            return Map(dbResults, allRoutings, allLocales);
        }

        #region helpers

        private static GetPageRoutingInfoByCustomEntityIdRangeQuery GetPageRoutingQuery(ICollection<CustomEntityVersion> dbResults)
        {
            return new GetPageRoutingInfoByCustomEntityIdRangeQuery(dbResults.Select(e => e.CustomEntityId));
        }

        private static GetPageRoutingInfoByCustomEntityIdQuery GetPageRoutingQuery(CustomEntityVersion dbResult)
        {
            if (dbResult == null) throw new ArgumentNullException(nameof(dbResult));
            if (dbResult.CustomEntity == null) throw new ArgumentNullException(nameof(dbResult.CustomEntity));

            return new GetPageRoutingInfoByCustomEntityIdQuery(dbResult.CustomEntityId);
        }

        private ICollection<CustomEntityRenderSummary> Map(
            ICollection<CustomEntityVersion> dbResults,
            IDictionary<int, ICollection<PageRoutingInfo>> allRoutings,
            ICollection<ActiveLocale> allLocalesAsEnumerable
            )
        {
            var results = new List<CustomEntityRenderSummary>(dbResults.Count);
            var allLocales = allLocalesAsEnumerable.ToDictionary(l => l.LocaleId);

            foreach (var dbResult in dbResults)
            {
                var entity = MapCore(dbResult);

                if (dbResult.CustomEntity.LocaleId.HasValue)
                {
                    entity.Locale = allLocales.GetOrDefault(dbResult.CustomEntity.LocaleId.Value);
                    EntityNotFoundException.ThrowIfNull(entity.Locale, dbResult.CustomEntity.LocaleId.Value);
                }

                entity.PageUrls = MapPageRoutings(allRoutings.GetOrDefault(dbResult.CustomEntityId), dbResult);

                results.Add(entity);
            }

            return results;
        }

        private CustomEntityRenderSummary MapSingle(
            CustomEntityVersion dbResult,
            ICollection<PageRoutingInfo> allRoutings,
            ActiveLocale locale
            )
        {
            var entity = MapCore(dbResult);
            entity.Locale = locale;
            entity.PageUrls = MapPageRoutings(allRoutings, dbResult);

            return entity;
        }

        private CustomEntityRenderSummary MapCore(CustomEntityVersion dbResult)
        {
            var entity = new CustomEntityRenderSummary()
            {
                CreateDate = DbDateTimeMapper.AsUtc(dbResult.CreateDate),
                CustomEntityDefinitionCode = dbResult.CustomEntity.CustomEntityDefinitionCode,
                CustomEntityId = dbResult.CustomEntityId,
                CustomEntityVersionId = dbResult.CustomEntityVersionId,
                Ordering = dbResult.CustomEntity.Ordering,
                Title = dbResult.Title,
                UrlSlug = dbResult.CustomEntity.UrlSlug,
                WorkFlowStatus = (WorkFlowStatus)dbResult.WorkFlowStatusId,
                PublishDate = DbDateTimeMapper.AsUtc(dbResult.CustomEntity.PublishDate)
            };

            entity.PublishStatus = PublishStatusMapper.FromCode(dbResult.CustomEntity.PublishStatusCode);
            entity.Model = _customEntityDataModelMapper.Map(dbResult.CustomEntity.CustomEntityDefinitionCode, dbResult.SerializedData);

            return entity;
        }

        private ICollection<string> MapPageRoutings(
            ICollection<PageRoutingInfo> allRoutings,
            CustomEntityVersion dbResult)
        {
            if (allRoutings == null) return Array.Empty<string>();

            var urls = new List<string>(allRoutings.Count());

            foreach (var detailsRouting in allRoutings
                .Where(r => r.CustomEntityRouteRule != null))
            {
                var detailsUrl = detailsRouting
                    .CustomEntityRouteRule
                    .MakeUrl(detailsRouting.PageRoute, detailsRouting.CustomEntityRoute);

                urls.Add(detailsUrl);
            }

            return urls;
        }

        #endregion
    }
}

[ernestoSerra]

Hello, thanks for this code. We debug it and find the problem. It was that the query was executed into a loop. If we extract it at a higher level, the load times decrease.

Thanks for help us.