Poor performance in ecf_CatalogEntrySearch_Init

Description

ecf_CatalogEntrySearch_Init can perform badly in case there are many changed entries. The SP can be improved with some changes described below.

Reported by Partner:

Our customer has a quite big amount of data in the catalog (1225004 entries in 8 catalogs). This leads to a very slow performance when there have been a lot of changes in the catalog, which leads to timeout-errors when calling the Mediachase.Commerce.Catalog.ICatalogSystem.StartFindItemsForIndexing method.

The problem appears when there are a lot of CatalogEntryIds selected in the @ModifiedCondition. For some reason, SQL Server can’t manage this very well in the form the SQL is written, using the subquery in the @ModifiedCondition.

To solve the problem, a table-variable was introduced and CatalogEntryIds were inserted into it. Then, using the table-variable, in the where IN clause instead. After making this change, the procedure runs in approx 5-8 seconds when having 220000 modified records.

We also made changes using a join instead if “where CatalogEntryId IN ( SELECT…)”, which gave similar performance improvements.