November Happy Hour will be moved to Thursday December 5th.

AI OnAI Off

RelationRepository.UpdateRelations() very slow

Vote:
 

Hi,

I'm experiencing very slow performance for RelationRepository.UpdateRelations().
The method below is part of a scheduled job, where I try to remap a collection of entries to their respective category and brand. The method is called several times, for each entry.

private bool RemapEntry(ItemVariant item)
{
    try
    {
        var relationRepository = ServiceLocator.Current.GetInstance();
        var relationsToUpdate = new List();
                
        // Add brand relation
        var brandPage = GetBrandPage(item.Brand);
        if (brandPage != null)
        {
            relationsToUpdate.Add(new NodeEntryRelation
            {
                Child = item.Entry.ContentLink,
                Parent = brandPage.ContentLink,
                IsPrimary = true
            });
        }

        // Add category relation
        foreach (var category in item.Categories)
        {
            var categoryPage = GetCategoryPage(category);
            if (categoryPage != null)
            {
                relationsToUpdate.Add(new NodeEntryRelation
                {
                    Child = item.Entry.ContentLink,
                    Parent = categoryPage.ContentLink,
                    IsPrimary = false
                });
            }
        }
                
        if (item.Entry is DolVariant)
        {
            // Variant is already mapped to a product
            var relations = relationRepository.GetParents(item.Entry.ContentLink);
            if (!relations.Any())
            {
                // Map product to variant
                var productLink = _referenceConverter.Service.GetContentLink(item.ItemNo);
                var productPage = _contentRepository.Service.Get(productLink);
                if (productPage != null)
                {
                    relationsToUpdate.Add(new ProductVariation
                    {
                        Child = item.Entry.ContentLink,
                        Parent = productPage.ContentLink,
                    });
                }
            }
        }
                
        if (relationsToUpdate.Any())
        {
            // Remove any previous existing relations for current entry
            var nodeRelations = item.Entry.GetNodeRelations();
            if (nodeRelations.Any())
            {
                relationRepository.RemoveRelations(nodeRelations);
            }

            // Update all new relations
            relationRepository.UpdateRelations(relationsToUpdate);
        }
        return true;
    }
    catch
    {
        return false;
    }
}

Everything is working fine, until I run into this line:

// Update all new relations
relationRepository.UpdateRelations(relationsToUpdate);

This line can take minutes to execute!
What is happening here? Am I doing something terrible wrong that I don't know about?

I am using v11.2.6.0 for Commerce and v10.10.4.0 for Episerver.

Thanks,
Kristoffer

#184730
Nov 02, 2017 15:12
Quan Mai
Quan Mai
Vote:
 

You can use a profiler (which I personally recommend dotTrace), and profile to find the bottleneck and get back with more information - then we will be able to look into the problem 

#184731
Nov 02, 2017 15:15
Magnus Rahl
Magnus Rahl
Vote:
 

I would advice against updating relations this way - removing all and then adding. It is better to take more care to check if any of the existing relations already match what you want, and only add/remove as required. Removing and then re-adding the same relation will cause a lot of unnecessary cache evictions, execution of event handlers (including indexing) etc. One thing that it will do that might be particularily bad (not sure about this one) is that it will (momentarily, but still) leave the entry with no relations at all, which logically means it is a direct child of the catalog. I wouldn't be surprised if that would cause extra many operations around cache/events/indexing.

Also, if you continuously do this as part of some batch job to keep in sync with an external source, it is possible that you eventually cause severe index fragmentation in the database.

#184736
Nov 02, 2017 17:20
Vote:
 

Ok I understand.
I created this job more as a temporary thing, to solve all thousands of products we have uncategorized. So the removal of categories won't affect much in this case.

I have also noticed that the UI for category relations (Under the tab "Belongs to") is very slow for me aswell. When I add a category to a product it takes about a minute for the category to change from "1073742135__CatalogContent" to "Kitchen & Dining". This makes me think that my job isn't really the problem, more something else causing this.

We are also using Episerver Find and when I ran SQL Profiler and I noticed alot of "findIndexQueueSave".
I tried to disable Find but I'm not sure I got rid of it completely. It didn't show up in SQL Profiler anymore, but it was still slow.

#184767
Nov 03, 2017 9:22
Magnus Rahl
Magnus Rahl
Vote:
 

Sounds like there is something wrong. Do you have any idea if it is the DB access for saving or loading relations that is slow, or if it lags somewhere else in the call chain? I suggest some profiling to find the bottle neck, this is definitely not normal.

#184768
Nov 03, 2017 10:03
Vote:
 

I think I found the problem. It has to do with Episerver Find Indexing.
I turned off indexing for entries:

ContentIndexer.Instance.Conventions.ForInstancesOf<EntryContentBase>().ShouldIndex(x => false);


The first time I try to add a category to a product it takes some time, but I guess that has to do with that nothing is cached after my build. But after that, everything is working perfectly!
When I turn on indexing, it's slow all the time. It's like it clears the cache every time the entry is indexed. Maybe that is standard behaviour, but it makes the category relations a pain to work with.

#184774
Nov 03, 2017 11:29
Magnus Rahl
Magnus Rahl
Vote:
 

Clearing the cache when indexing is definitely not the intended behavior, are you sure that is what happens? And even with a completely empty cache, fetching a bunch of relations for display should be a relatively lightweight oeration.

And IIRC the event driven indexing should be done async, so even if the indexing takes some time it shouldn't block the thread of the request doing the update. And you should be able to have indexing active of course. Can you find any more details of how the indexing seems to be delaying everything? Or maybe it is time to open a support ticket with developer support.

#184778
Nov 03, 2017 12:41
Vote:
 

No, that was just speculations, I have no idea what is going on. All I know is that when I turn the indexing off it's working fine.

Do you know if it's possible to turn off indexing of entries just for this scheduled job? If so, I could run the job and then index everything.

#184783
Nov 03, 2017 13:31
Quan Mai
Quan Mai
Vote:
 

As I said, put it under the microscope of a profiler, and we will know what is slow, and possibly, how to fix it

#184790
Nov 03, 2017 14:20
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.