Fast way to delete a catalog

Vote:
 

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
Vote:
 
ServiceLocator.Current.GetInstance<ICatalogSystem>().DeleteCatalog(1)
#185048
Nov 08, 2017 19:32
Vote:
 

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
Vote:
 
<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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Per Atle Holvik - Mar 02, 2021 10:00
Sorry to revive a rather old thread, but did this speed-up make it into Commerce 13, Quan?
Quan Mai - Mar 02, 2021 10:25
Yes it is in. I don't recall which version but if you upgrade to latest then yes it should be much faster now.
Vote:
 

@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:32
Vote:
 

@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
Vote:
 

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
Vote:
 

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
Vote:
 

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.

#249464
Mar 02, 2021 10:27
Vote:
 

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!

#249569
Mar 04, 2021 19:21
Vote:
 

I misremembered. It was like 15 times faster but YMMV

#249572
Mar 04, 2021 20:12
Vote:
 

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

#252930
Apr 09, 2021 13:41
Quan Mai - Apr 09, 2021 13:55
13.11 :)
Quan Mai - Apr 09, 2021 13:57
But go for the latest version, as always
Surjit Bharath - Apr 09, 2021 14:09
I JUST found it: https://world.episerver.com/support/bug-list/bug/COM-10173

Thanks.

Client is still having timeout issues deleting the catalogue despite being on 13.29.
I am going to investigate further next week.
Vote:
 

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

delete from Catalog

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

#253548
Apr 19, 2021 10:32
Quan Mai - Apr 19, 2021 10:56
strongly advise AGAINST that. not only the recommendation is to avoid direct data manipulation, doing so will leave some nasty data in your database
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.