Possibly indexing if you have a large number of content records

Thanks for response.

Any suggestion which tables should I put indexes on?

HI Mateusz,

How are you doing this. It could be something in your code that is causing this slow down.

Dave

Thanks for your answer Dave.

The trick is I'm running exactly the same process using exactly the same code and input data but on different databases: small and large one.

I measured execution times for all my methods used to perform my process and it showed me clearly that the only difference is on ContentService::SaveAndPublish method.

Hi Mateusz,

It could be that you have a mistake in your code which causes the SaveAndPublish to be slower on a larger content set.

Dave

That is interesting.

So in a nutshell: I'm getting the content using IContentService::GetById method. Than I update the property values using IContent::SetValue and finally I'm saving it using SaveAndPublish method. All done in accordance to the documentation.

Could you please specify what kind of mistake is possible to make in this process, where should I look for?

Hi Mateusz,

Could your post your code. That would make it easier to check if something where things are going wrong.

Dave

As the whole process is spread over many services and classes, I had to simplify the algorithm a bit, but managed to include everything in concise methods and the most important - the issue still occur.

So there is UpdateSimple method:

    private void UpdateSimple()
    {
        var item = _contentRepositoryWrapper.GetContentById(243059);

        if (item != null)
        {
            item.SetValue(Season.GetModelPropertyType(x => x.Title).Alias, "New Title");

            _contentRepositoryWrapper.SaveAndPublish(item, ImporterConfiguration.ImporterUserId);
        }
    }

_contentRepositoryWrapper is a simple wrapper on Umbraco built-in services:

    public IContent GetContentById(int id)
    {
        using (var cref = _umbracoContext.EnsureUmbracoContext())
        {
            var content = _contentService.GetById(id);

            return content;
        }
    }

    public PublishResult SaveAndPublish(IContent content, int userId)
    {
        using (var scope = _scopeProvider.CreateScope(autoComplete: true))
        {
            var result = _contentService.SaveAndPublish(content, userId: userId);

            return result;
        }
    }

And now please see the execution times. First one comes from method run on a small db:

As you can see execution time is less than 3 seconds.

Now the large db:

Over 10 second slower.

Hi Mateusz,

First of all you can get rid of ensuring that you have a UmbracoContext. As far as I can see you are not making use of it.

 public IContent GetContentById(int id)
    {
       var content = _contentService.GetById(id);

            return content;
    }

Than I see that you wrap each publish action in a new scope. I have checked a import on large site and I see that I wrap all my publish actions in a single scope.

So something like this :

using (var scope = _scopeProvider.CreateScope())
        {
         // do all you import work here

scope.Complete();
        }

Dave

Thank you Dave for your advises, I will definitely try to implement them in my code.

However, back to my case, we can treat my given example as a atomic/single use case. It's probably quite weird that the size of the content in the database affects the execution time of the code?

We can even stop the conversation about the code, as the issue is visible even from the Umbraco UI level.

When Save and Publish is clicked on the particular document, there is significant difference in terms of how long the operation took:

Small DB:

Large DB:

HI Mateusz,

Do you have any packages installed or do you hook into the published events of the content service ?

Or maybe you have some custom indexing logic.

Dave

I do not have any packages installed and I disabled all the event handlers I was subscribed to.

I have an integration with elastic search, but the reindexing is triggered manually, so no automated hooks are implemented.

I replaced Umbrace.Core nuget package with Umbraco.Core project to be able to debug it.

I added two timers: one to measure just a Save operation and second to measure entire method execution time:

As you can see the CommitDocumentChangesInternal took only ~8 seconds (which still is way longer than executing entire metohd on small DB), but completing the scope took additional ~5seconds.

Based on above findings the conclusion is: everything what is taking so much time is inside Umbraco code.

Hi Mateusz

Every time you save and publish an Umbraco Content item it creates a version in the database.

This enables editors to roll back to an earlier version.

But if you are manipulating content items via code on a daily schedule these versions can build up and choke database performance...

So if you go to one of these content items in back office and click rollback... Do you have a ton of versions available to roll back to?

Or are you tidying up the versions after import?

Apologies if you are already on the case with the version thing, just thought worth chipping in in case it was relevent for you in this context!

Regards

Marc

Hi Mark,

thank you very much for your answer.

I am aware of content versioning and we have clean up policies implemented. In addition that was my first thought: to clean up old versions and empty the recycle bin. Unfortunately it did not affect the save time at all.

Great Mateusz, that is good to hear you are on top of the versioning!

Then I only have 9ne further thought, and appreciate its irritating if you have already thought this!

But how is the % defragmentation of your various SQL indexes??

The following gist gives a set of SQL statements that would give you a report

https://gist.github.com/marcemarc/9d6553e881a7cf536c8bd98f8c35fd92

And whether any need rebuilding or reorganising.

Regards

Marc

Thank you for this idea, I even was not aware about this.

Here are the results for my smaller DB:

And the results for the big one:

It looks like small db has worse fragmentation metrics but is way faster.

I'd like only to mention that in my large database I have:

• 7 445 360 rows in UmbracoPropertyDataTable
• 206 216 rows in UmbracoNode table

Hi Mateusz,

Maybe rebuilding or reorganizing your indexes can result in better performance.

Dave

Hi Dave,

I've rebuilt/reorganized the indexes, with no luck - unfortunately..

Thank you Marc, I will read it for sure.

For today I plan to play a little bit with sql profiler to check if/where there are differences between databases.

I'll post the findings.

Have you tried disabling your Examine indexing on publish (temporarily) to see if that might be the issue? https://our.umbraco.com/forum/using-umbraco-and-getting-started/109579-disabled-examine

is working on a reply...