Adding new catalogue nodes is very slow in a large catalogue

Jim Thurston

Vote:

Hi,

EPiServer Commerce version 8.71

We're finding that adding new nodes to our EPiServer catalogue is very slow, and often times out, though all other catalogue modification operations seem fairly speedy. In particular, the method SaveCatalogNode in ICatalogSystem can take around 30 seconds to complete for each node being added.

I've run SQL Profiler to see what's going on, and the culprit appears to be this call:

declare @p1 dbo.udttCatalogNodeList
insert into @p1 values(0)

exec ecf_CatalogNode_GetAllChildEntries @catalogNodeIds=@p1

Running this in SSMS tells me that this proc will retrieve ALL entries in our entire catalogue - but I have no idea what the SaveCatalogNode method might do with this information. Can anyone enlighten as to why our entire catalogue of entries (250,000+) has to be retrieved every time we want to add a new node? Any hints on how we might be able to speed this up?

Thanks,

Jim

#117891

Feb 23, 2015 17:06

Quan Mai

Vote:

Hi,

It was filed as a bug and has been fixed in upcoming version (possibly 8.9): http://world.episerver.com/support/Bug-list/bug/121067

A temporary fix for you is to change ecf_CatalogNode_GetAllChildEntries to this:

create procedure ecf_CatalogNode_GetAllChildEntries
    @catalogNodeIds udttCatalogNodeList readonly
as
begin
    with all_node_relations as 
    (
        select ParentNodeId, CatalogNodeId as ChildNodeId from CatalogNode
        where ParentNodeId > 0
        union
        select ParentNodeId, ChildNodeId from CatalogNodeRelation
    ),
    hierarchy as
    (
        select 
            n.CatalogNodeId,
            '|' + CAST(n.CatalogNodeId as nvarchar(4000)) + '|' as CyclePrevention
        from @catalogNodeIds n
        union all
        select
            children.ChildNodeId as CatalogNodeId,
            parent.CyclePrevention + CAST(children.ChildNodeId as nvarchar(4000)) + '|' as CyclePrevention
        from hierarchy parent
        join all_node_relations children on parent.CatalogNodeId = children.ParentNodeId
        where CHARINDEX('|' + CAST(children.ChildNodeId as nvarchar(4000)) + '|', parent.CyclePrevention) = 0
    )
    select distinct ce.CatalogEntryId, ce.ApplicationId, ce.Code
    from CatalogEntry ce
    join NodeEntryRelation ner on ce.CatalogEntryId = ner.CatalogEntryId
    where ner.CatalogNodeId in (select CatalogNodeId from hierarchy)
end

Regards.

#117899

Feb 23, 2015 19:06

Jim Thurston

Vote:

Hi Quan,

Thanks, this temporary fix appears to have done the trick for now.

Jim

#117934

Feb 24, 2015 16:27