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.