ServiceLocator.Current.GetInstance<ICatalogSystem>().DeleteCatalog(1)

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()

<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

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.

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

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

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 

@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

@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.

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. 

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.

For future visitors, upgrade to latest commerce and the catalog deletion should be much faster. On our test with ~6k entries it was 4 or 5 times faster, IIRC. If you have bigger catalog it is like to be even bigger difference.

We had this issue not too long ago where the client couldn't delete large portions of the catalog using the catalog. We ended up creating a few chained scripts to Service API to query the catalog and delete the nodes in batches.

That being said, sounds like I need to get the client update the latest version if its 5 times faster!

I misremembered. It was like 15 times faster but YMMV

@Quan what version of episerver commerce nuget is the change of your last comment going into ?

Most faster way to delete catalog, (if you have db access)

delete from Catalog

It will delete all dependent data entry, node, relation etc.