Fast way to delete a catalog

Sergiu Seling
Member since: 2014
 

We have a catalog with almost 20.000 products and 60.000 variants with about 30 metafields each. 

Also we have 600 types of properties for the products that we decided to store separatelly from the EPi metafields in order to speed the queries (a product has only about 10 properties of those 600)

Now we want to delete the catalog and reimport the data on the integration server, but deleting from Catalog UI takes a long time. Is there a way to make it faster or do you have another suggested approach?

Thank you

#184996
Nov 08, 2017 10:56
Mark Hall
Member since: 2011
 
ServiceLocator.Current.GetInstance<ICatalogSystem>().DeleteCatalog(1)
#185048
Nov 08, 2017 19:32
Sergiu Seling
Member since: 2014
 

Thank you, I tried that approach

Unfortunately it has the error below. I'll look further int that CatalogSystem. 

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)

at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)

at Mediachase.Data.Provider.SqlDataProvider.SaveRows(DataCommand command)

at Mediachase.Commerce.Storage.DataHelper.SaveDataSetSimple(DataCommand cmd, DataSet set, String[] tables)

at Mediachase.Commerce.Catalog.Data.CatalogEntryAdmin.Save()

at Mediachase.Commerce.Catalog.Managers.CatalogEntryManager.SaveCatalogEntry(CatalogEntryDto dataset)

at Mediachase.Commerce.Catalog.Managers.CatalogEntryManager.DeleteCatalogEntries(IEnumerable`1 catalogEntryIds)

at Mediachase.Commerce.Catalog.Managers.CatalogNodeManager.DeleteCatalogNodeAndEntries(Int32 catalogNodeId, Int32 catalogId)

at Mediachase.Commerce.Catalog.Managers.CatalogManager.DeleteCatalog(Int32 catalogId)

at Murdochs.Application.Jobs.DeleteCatalogJob.Execute()



#185102
Nov 09, 2017 13:34
Mark Hall
Member since: 2011
 
<dataService defaultProvider="SqlDataProvider">
      <providers>
        <add name="SqlDataProvider" type="Mediachase.Data.Provider.SqlDataProvider, Mediachase.SqlDataProvider" connectionStringName="EcfSqlConnection" applicationName="ECApplication" commandTimeout="60" />
      </providers>
    </dataService>

You can add a larger timeout in the web.config

#185117
Nov 09, 2017 18:29
Drew Douglas
Member since: 2013
 

The CatalogSystem Delete method is perhaps not so fast if you have a large catalog or many versions of your catalog entries? It took me over 6 hours with 90k entries.

Instead, these (unsupported) database commands worked to delete all my variations. Then, deleting the nodes was much quicker.

DELETE [CatalogEntry]
DELETE [variation]
DELETE [ecfVersion]

I also deleted the assets for these products ahead of time by moving folders of Media in the Catalog UI to the trash and running the "Remove Abandoned BLOBs" Scheduled Job.

This was on Commerce version 12.9.

#201234
Feb 11, 2019 5:42
Quan Mai
Member since: 2011
 

I would advise against direct manipulation. With your approach many things are left behind, and also cache is not invalidated.

#201236
Feb 11, 2019 7:38
Quan Mai
Member since: 2011
 

I am looking into this and I might make some improvement to the catalog deletion. Will keep you posted 

#201255
Feb 11, 2019 14:40
Quan Mai
Member since: 2011
 

An improvement is under review. It will certainly not make your catalog deletion lightning fast, but in some cases, it can speed up your deletion to 10x faster (based on my very non scientific test) 

it'll most likely be available in Commerce 13 

#201260
Feb 11, 2019 16:27
Drew Douglas
Member since: 2013
 

@QuanMai

I agree that deleting rows out of the tables is absolutely a bad idea. However, we are working on refining our catalog import, and for testing in our dev integration environment it's just not feasible to do a db backup and restore, as CMS editors are working on content while we work on the data import. We are far enough along that we need to test the import on the whole data set to make sure we're doing it right. And, unfortunately, we still find bugs that require a full delete and reload.

With the database commands I gave above, foreign keys caused deletion of other data. So, after deleting the entries, I did row counts for these tables and they all came up empty, or only with the rows for nodes and catalogs:

select count(*) from CatalogAsset
select count(*) from CatalogContentAccess
select count(*) from CatalogContentEx
select count(*) from CatalogContentProperty
select count(*) from CatalogEntryAsset
select count(*) from CatalogEntryAssociation
select count(*) from CatalogEntryRelation
select count(*) from CatalogLanguage
select count(*) from ecfVersionAsset
select count(*) from ecfVersionProperty
select count(*) from ecfVersionVariation

Is there content in the CMS database that could still be lurking from these deleted rows?

Thanks for looking at the catalog delete process. I added indexes to some of the tables based on SSMS query analyzer suggestions, but they only marginally increased the speed of ICatalogSystem.Delete. The indexes I added are below.

/*
Missing Index Details from ExecutionPlan3.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 78.8099%.
*/

CREATE NONCLUSTERED INDEX [IX_CatalgoEntryAssociation_CatalogEntryId]
ON [dbo].[CatalogEntryAssociation] ([CatalogEntryId])
INCLUDE ([CatalogAssociationId])
GO


/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 98.3637%.
*/

CREATE NONCLUSTERED INDEX [IX_CatalogItemAsset_AssetKey]
ON [dbo].[CatalogItemAsset] ([AssetKey])

GO

/*
Missing Index Details from ExecutionPlan2.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 98.3655%.
*/
CREATE NONCLUSTERED INDEX [IX_CatalogAssociation_CatalogEntryId]
ON [dbo].[CatalogAssociation] ([CatalogEntryId])

GO

#201262
Edited, Feb 11, 2019 17:33
Drew Douglas
Member since: 2013
 

@QuanMai:

One other consideration: is it possible that the find index is slowing this down? Is there any way to disable removing content from the index during deletion? Then we could just re-index all the content at the end.

#201264
Feb 11, 2019 17:42
Quan Mai
Member since: 2011
 

We are aware of the indexes, and two of them are already in later version. AssetKey does not qualify , for now. Thanks for sharing your finding anyway.

There is no way to turn on and off the indexing on fly. 

#201265
Feb 11, 2019 18:09
Drew Douglas
Member since: 2013
 

Ok, perhaps we can add an appconfig to turn indexing off for the catalog, and restart the site after changing it. I'll see if that makes a noticible difference.

Obviously, once we get to DXC preprod and production, we won't have this fine-grained control over the app, and would have to do a full redeploy. However, for dev testing this hack might be good enough.

Again, thanks for your attention to this.

#201267
Feb 11, 2019 18:22