U4-6801 - RelationService.GetByParentId(memberId) slow. Fires two queries per item

Created by Sam 08 Jul 2015, 07:42:15 Updated by Sam 02 Jun 2017, 03:27:26

I'm currently trying to get all relation items for one parent by calling: var items = ApplicationContext.Current.Services.RelationService.GetByParentId(members.GetCurrentMemberId()).ToArray(); It all works fine. Except when I look at the MiniProfiler, I can see it's firing off two queries per item, which is causing some major performance issues with larger amounts of items.

I looked at the UmbracoCMS code and I found this piece of code: protected override IEnumerable PerformGetByQuery(IQuery query) { var sqlClause = GetBaseQuery(false); var translator = new SqlTranslator(sqlClause, query); var sql = translator.Translate();

    var dtos = Database.Fetch<RelationDto>(sql);

    foreach (var dto in dtos) // query to umbracoRelationType for each item here
    {
        yield return Get(dto.Id); // query to umbracoRelation for each item here
    }
}

The foreach on the dtos is sending a query for each MoveNext() to the umbracoRelationType table. The Get() method actually queries the individual item from the umbracoRelation table. I think it's supposed to utilize the cache there, but evidently it's not.

Anyway. How do I improve performance here? Can I somehow get to the content in just a single query without completely bypassing Umbraco?

I'm using Umbraco 7.2.6 related item on stackoverflow: http://stackoverflow.com/posts/31282575

1 Attachments

Comments