Getting duplicate data on server. Indexing issue?

Question

Using Sitecore 9.0.1 and DEF 2.0.1, I have a pipeline which reads from an API and then resolves matching Sitecore items using the standard ResolveSitecoreItemStepProcessor and ResolveSitecoreItemStepConverter

Locally, this works fine, and if the Sitecore item exists, it is updated rather than a new one created. On our server, duplicate records are created intermittently when the pipeline is run. The issue appears to happen more often when importing large data sets of 1000 records and then sometimes, only for the last 500 records.

ManagedPoolThread #7 05:41:46 DEBUG [Data Exchange] Value converted for search. (pipeline: Experience from Source to Item Sync Pipeline, pipeline step: Resolve Experience Item, pipeline step identifier: 4b865ee9-78f5-49fe-a34e-b595d5d8b426, field: Uuid, original value: a109b95a-8135-4335-a20d-7edef2321a56, converted value: a109b95a-8135-4335-a20d-7edef2321a56)
ManagedPoolThread #7 05:41:46 DEBUG [Data Exchange] Starting search for item. (pipeline: Experience from Source to Item Sync Pipeline, pipeline step: Resolve Experience Item, pipeline step identifier: 4b865ee9-78f5-49fe-a34e-b595d5d8b426, field: Uuid, value: a109b95a-8135-4335-a20d-7edef2321a56)

i.e. ResolveSitecoreItemStepProcessor is searching for an item with a Uuid (our custom field) of { a109b95a-8135-4335-a20d-7edef2321a56}.

We've decompiled ResolveSitecoreItemStepProcessor and added our own debug. From that, we can see that the search isn't able to find the item in the index even when it exists. Also, when we have a duplicate, if we search in the Content Editor for some content which is duplicated across duplicate items, only one result is found until we re-index via the index manager. It therefore seems to be an indexing issue.

"Iterate Rows from Source and Run Pipeline" in turn calls the " Experience from Source to Item Sync Pipeline" pipeline. We added the "Rebuild Index Pipeline" step to try to address this issue but we still get this issue ..

We are considering a number of approaches to fix this including 1) Using our own implementation of ResolveSitecoreItemStepProcessor that doesn't use the index but looks at the Sitecore tree (might be complex and have unforseen consequences) 2) Batching the import into smaller chunks (of say 100 records) as this happens only with large data sets 3) Possibility of turning off indexing during an import (needs research)

Ideally, we would identify the root cause of this issue, rather than one of these workarounds ..

Answer 1

Still think the issue in the indexing.

The resolver uses Search method from InProcItemModelRepository.

public virtual IEnumerable<ItemModel> Search(ItemSearchSettings settings)

Search tries to get indexname by databasename

protected virtual string GetSearchIndexNameForDatabase(string databaseName)
    {
        return string.Format("sitecore_{0}_index", databaseName);
    }

Here is configuration for IItemModelRepository:

     <dataExchange>
        ...
        <itemModelRepository type="Sitecore.DataExchange.Local.Repositories.InProcItemModelRepository, Sitecore.DataExchange.Local">
            <databaseName>master</databaseName>
        </itemModelRepository>
        ...
    </dataExchange>

Answer 2

This is the answer I received from Sitecore support. In the end, we implemented our own version of ItemModel repository as they suggested:

As you probably know, the ResolveSitecoreItemStepProcessor, as well as the other processors of the Sitecore provider, uses InProcItemModelRepository for any item related operations. The InProcItemModelRepository under the hood uses the ItemService API. Whenever your pipeline creates or updates an item InProcItemModelRepository sends the appropriate HTTP request against the ItemService. The problem is that each separate ItemService request gets served in a separate thread on the Sitecore side: same as it works for the other regular HTTP requests. Your sitecore_master_index is configured to use syncMaster strategy, which executes index updates from the corresponding DataEngine events. This all together leads to a situation when a number of parallel ItemService threads may initiate index updates for the performed item operations. But Sitecore does not allow concurrent updates for the same index, thus it queues the index update jobs. And in case of a large amount of content changes the number of jobs queued may grow very quickly.

For example, in order to perform 1000 create/update operations, DEF will execute 1000 separate parallel ItemService requests. As a result, you will get 1000 queued update jobs only for the sitecore_master_index. Processing all these jobs may take some time, and thus the last item updates may be delayed.

When your further DEF processors try to find existing objects then may fail due to the index is not up to date at the moment of processors execution. Unfortunately, this is just how the InProcItemModelRepository was designed. I have registered a feature request to create a new endpoint (ItemModel repository) which does not depend on indexes so that it can be considered for future implementation. To track the future status of this request, please use the reference number 231598. More information about public reference numbers can be found here.

At the moment you can consider developing your own implementation the ItemModel repository. As an option, you can use Sitecore Queries instead of the Content Search queries. Please let me know whether this makes sense for you.

Answer 3

In case if anybody is still struggling to sort out the duplicate item creation issue, this code might be a life saver. Here I'm searching the database directly instead of the index. It might be a little bit slow if you have thousands of items in the target location, but if the number of items is not very, it works like a charm.

using Sitecore.DataExchange.Contexts;
using Sitecore.DataExchange.DataAccess;
using Sitecore.DataExchange.Local.Extensions;
using Sitecore.DataExchange.Providers.Sc.DataAccess.Readers;
using Sitecore.DataExchange.Providers.Sc.Plugins;
using Sitecore.DataExchange.Repositories;
using Sitecore.Services.Core.Diagnostics;
using Sitecore.Services.Core.Model;
using System;
using System.Linq;

namespace Test.Foundation.SitecoreExtensions.PipelineSteps
{
    public class CustomResolveSitecoreItemStepProcessor : Sitecore.DataExchange.Providers.Sc.Processors.PipelineSteps.ResolveSitecoreItemStepProcessor
    {
        private IValueReader GetValueReader(IValueAccessor config) => config?.ValueReader;

        protected override ItemModel DoSearch(object value, ResolveSitecoreItemSettings resolveItemSettings, IItemModelRepository repository, PipelineContext pipelineContext, ILogger logger)
        {
            var result = base.DoSearch(value, resolveItemSettings, repository, pipelineContext, logger);
            Item item;

            if (result == null)
            {
                logger.Warn($"Item not found in {$"sitecore_{repository.DatabaseName}_index"}. Item parent id: {resolveItemSettings.ParentItemIdItem}, Identifier: {value}");

                var valueReader = GetValueReader(resolveItemSettings.MatchingFieldValueAccessor) as SitecoreItemFieldReader;
                if (valueReader == null)
                {
                    return null;
                }
                string str = ConvertValueForSearch(value);
                string FieldName = valueReader.FieldName;
                var database = Sitecore.Configuration.Factory.GetDatabase(repository.DatabaseName);
                var parentFolder = database.GetItem(Sitecore.Data.ID.Parse(resolveItemSettings.ParentItemIdItem));
                if (parentFolder != null)
                {
                    try
                    {
                        item = parentFolder.Children.FirstOrDefault(i => i[FieldName] == str);

                        if (item == null)
                        {
                            logger.Warn($"Item not found in database. Item parent path: { parentFolder.Paths.FullPath}, Identifier: {value}");

                            return null;
                        }

                        Item[] items = { item };
                        Sitecore.DataExchange.Repositories.SearchFilter filter1 = new Sitecore.DataExchange.Repositories.SearchFilter
                        {
                            FieldName = valueReader.FieldName,
                            Value = str
                        };
                        ItemSearchSettings settings = new ItemSearchSettings();
                        settings.SearchFilters.Add(filter1);

                        var itemModel = item.GetItemModel();

                        if (itemModel != null)
                        {
                            logger.Info($"Item found in database. Item id: { item.GetItemModel()["ItemID"]}, Item name: {item.GetItemModel()["ItemName"]}, Item parent path: { parentFolder.Paths.FullPath}, Identifier: {value}");
                        }

                        return itemModel;
                    }
                    catch (Exception e)
                    {
                        logger.Error($"Error searching item. Item parent id: {resolveItemSettings.ParentItemIdItem}, Identifier: {value}, Error message: {e.Message}");
                        throw e;
                    }
                }

            }
            return result;
        }
    }
}

Then use this class reference in Processor Type field of the "Resolve Sitecore Item Pipeline Step" as shown below.

Answer 4

After consulting with Sitecore Support on similar issues, one of my collegues received this patch: https://github.com/SitecoreSupport/Sitecore.Support.227828/releases/tag/2.0.1.0

You might want to try this one out.