Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Paul Dermody 31 posts 110 karma points
    Sep 05, 2016 @ 20:36
    Paul Dermody
    0

    Significant performance bottleneck in RelationService

    Hi,

    I wanted to add this to the Issues site (http://issues.umbraco.org) but I can't figure out how to sign-up. In fact, it looks like signing up might not be working right now since hitting on the Log In link does nothing. In the meantime, I will report it here.

    I am using the RelationService on a new website to link certain pieces of content to others. The site has a large amount of content and consequently the use of this services is increasing everyday. I love the RelationService, it provides an intuitive way for our content editors to create custom 2-way relationships between content.

    However, I found recently that a big performance problem we were having can be traced down to the use of this service. It turns out that enumerating the list of relations and relation types is causing 1000's of unnecessary queries to the database.

    After some research it seems to me that the culprit is this method in the RelationRepository class:

        protected override IEnumerable<IRelation> PerformGetByQuery(IQuery<IRelation> query)
        {
                [...]
                var dtos = Database.Fetch<RelationDto>(sql);
                foreach (var dto in dtos)
                {
                    yield return Get(dto.Id);
                }
            }
        }
    

    In case this is not obvious, the PerformGetByQuery() is getting all the relations and doing a Get() on each one, effectively performing an additional query for each row, instead of just converting to an Relation object. This is the method called when I ask for all the relations of a specific type or for a specific parent.

    Couldn't we add an optimization here for this specific case? (And for the PerformGetAll() too.)

        protected override IEnumerable<IRelation> PerformGetByQuery(IQuery<IRelation> query)
        {
                [...]
                var dtos = Database.Fetch<RelationDto>(sql);
                foreach (var dto in dtos)
                {
                    yield return BuildEntity(dto);
                }
            }
        }
    

    The BuildEntity method would create the Relation object immediately without going back to the database.

    It will need some additional cleverness to avoid unnecessary queries for each RelationType too, since the same problem exists with the equivalent methods in the RelationTypeRepository class.

    I am surprised to see that this problem has been around for quite a while (see Poor Performance when using RelationService v7.0.3).

    For the moment I am working around it by preloading all the relations on application start, but I'd rather not do this, and it may be causing a lot of unnoticed performance hits on other websites too.

    Thanks,

    Paul.

  • Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib
    Sep 06, 2016 @ 06:48
    Dave Woestenborghs
    1

    Hi Paul,

    I noticed this too in the past, but forgot to create a issue for it.

    This is done now : http://issues.umbraco.org/issue/U4-8942

    You can go vote it up.

    This is a query that i execute in my website to bypass the relation service.

    SELECT 
        R.Id, 
        ParentId, 
        ChildId, 
        alias As RelationType 
    FROM umbracoRelation AS R 
        INNER JOIN umbracoRelationType ON R.relType = umbracoRelationType.Id
    

    This is will give you much better performance

    Dave

  • Paul Dermody 31 posts 110 karma points
    Sep 06, 2016 @ 14:54
    Paul Dermody
    0

    Thanks Dave. Oddly, I am now able to register on the issues site, which I wasn't able to do before.

    Regards,

    Paul.

Please Sign in or register to post replies

Write your reply to:

Draft