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, 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.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)
<add name="SqlDataProvider" type="Mediachase.Data.Provider.SqlDataProvider, Mediachase.SqlDataProvider" connectionStringName="EcfSqlConnection" applicationName="ECApplication" commandTimeout="60" />
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
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 CatalogAssetselect count(*) from CatalogContentAccessselect count(*) from CatalogContentExselect count(*) from CatalogContentPropertyselect count(*) from CatalogEntryAssetselect count(*) from CatalogEntryAssociationselect count(*) from CatalogEntryRelationselect count(*) from CatalogLanguageselect count(*) from ecfVersionAssetselect count(*) from ecfVersionPropertyselect count(*) from ecfVersionVariation
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.sqlplanThe 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
CREATE NONCLUSTERED INDEX [IX_CatalgoEntryAssociation_CatalogEntryId]
ON [dbo].[CatalogEntryAssociation] ([CatalogEntryId])
/*Missing Index Details from ExecutionPlan1.sqlplanThe 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])
CREATE NONCLUSTERED INDEX [IX_CatalogItemAsset_AssetKey]
ON [dbo].[CatalogItemAsset] ([AssetKey])
/*Missing Index Details from ExecutionPlan2.sqlplanThe 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])
CREATE NONCLUSTERED INDEX [IX_CatalogAssociation_CatalogEntryId]
ON [dbo].[CatalogAssociation] ([CatalogEntryId])
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.