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

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?

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?

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.

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!

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

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

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