Try our conversational search powered by Generative AI!

Unable to move page to trash because of sql timeout


The stored procedure [ecf_GetContentReferencesInCatalog] is timing out when moving a page to trash.
We have updated CMS from 11.21.x to 12.22.x, I assume that this stored procedure is new?

For your info, we have about 9 000 000 entries in the table CatalogContentProperty, which is used in the stored procedure.

Is there any solution to move forward?

Sep 25, 2023 16:04

This should have been made opt in and turned off my default.  See here.

COM-16996 – Made scanning for references in catalog content when removing CMS content opt-in – With Episerver.Commerce 14.9.0 and fix COM-14060, Commerce added a feature, so you would get a warning message if a block that was referenced by commerce catalog content was removed. This turned out to be heavy for some dataset, so it is now disabled by default and you must proactively opt-in to get the feature. Commerce 14.14.0

Edited, Sep 25, 2023 18:28

This is a really nice feauture that's been on our wishlist for long, so it's very unfortunate that it has to be disabled :/ 

Wouldn't it make sense to rely on the tblContentSoftlink data, instead of scanning all props? Would make the usage lookup a bit faster I guess?

Sep 26, 2023 7:09

Ah, so softlinks in CMS does not contain the refs going from Commerce -> CMS

But it does keep the refs from CMS -> Commerce, which should be the more common scenario. Any plans on implementing this?

Sep 26, 2023 9:49

Unfortunately no, the data is stored entirely in CatalogContentProperty (As you can imagine to make it easier to import/export). Adding the references to tblContentSoftlink is possible, but the migration of data is a nightmare.

I did look into this, and I wish there was a nice, clean, performant way to fix it. I really do - but it doesn't exist. at very least it would be a major effort with some complete rewrites, something we can't afford atm.

Sep 26, 2023 11:11

The most logical solution [IMO] would be to listen to content events on commerce, and haven them update tblContentSoftlinks, just like CMS content events does.

fkOwnerContentID would have to be changed tho, so it could also represent Commerce content as owner, and probably not be a "hard" FK anymore.

It's probably a cheaper solution to register the reference as content are created -  as opposed to trying to find out afterwards.

tblContentSoftlinks is a really useful construct!

Sep 26, 2023 11:24

Still a problem with existing data. as I said, if we wipe the plate clean and start over then yes there are several approaches that might work. but as a framework we have to care about the existing data and migration.

Well, might be an idea for an addon

Sep 26, 2023 13:36

I think you could update the commerce timeout on the default command handler to allow enough time for the query to run

Sep 26, 2023 14:14

I would actually advise against that. as the commerce time out setting is used for all other operations, you run into risks of queries continue to run when they should not. the query can also slow down the entire db and affect overall site performance.

yes if there is a special setting for that query only then it can be used, with reservation. it is unfortunately not this case 

Sep 26, 2023 15:04
* 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.