Try our conversational search powered by Generative AI!
AI OnAI Off
Performance issue with version sync SQL
Hi,
We're aware of that issue and are working to solve it. In the next release of Commerce, the version sync issue should be gone and the performance gain should be very significant.
Stay tuned.
Regards.
/Q
Jun 17, 2015 9:19
Sounds good! Is there any bug or story connected to this, so we can track the proceedings? When is next release planned?
Jun 17, 2015 9:28
I don't think we have public stories - so you might not have access to that. I can't say for sure about the timeframe, but it should be no later than this fall.
Regards.
/Q
Jun 17, 2015 9:31
Thank you very much for your fast responses. Is there anything we can do in the meantime? Applying index to that column for BigTable did not work after site restart. We enabled the "DisableVersionSync", but just to make sure, this is not completely removing the sync jobs?
Jun 17, 2015 9:36
Enabling DisableVersionSync will skip the version sync if you're using other APIs then Content API. For example, if you use ICatalogSystem to save CatalogEntryDto or when you import the catalog. When you use Content API, there still be some slowness due to DDS.
Regards.
/Q
Jun 17, 2015 9:45
How did you import products from other system? Did you use Content API or using catalog import export? If you are using Content API then you might want to save the content with the extended flag ExtendedSaveAction.ClearVersions.
In the meantime, since you don't want catalog version at all, you can clean up the big table by executing these: DELETE FROM [tblBigTable] WHERE StoreName='EPiServer.Commerce.Catalog.Provider.CatalogContentDraft' and DELETE FROM tblBigTableReference WHERE PropertyName='ContentReferenceToSoftlinkMapping'. As always, please backup your database first.
Jun 22, 2015 11:21
I will answer instead of Rasmus. We are importing products using via ICatalogSystem (e.g. ICatalogSystem.SaveCatalogEntry(catalogEntryDto)). Is it safe for us to do clean up of big tables since we don't care about products/nodes versions because all changes comes from external system. It shouldn't affect page versions in CMS or anything else, right?
Any other ideas are appreciated too. Thank you.
Jul 02, 2015 18:08
Yes, it would be safe for having no catalog version at all.
EDIT: I want to note that tblBigtable is home of other data also, so be careful with the store you work with (CatalogContentDraftStore)
Regards.
/Q
Edited,
Jul 02, 2015 20:18
We have tried to add the corresponding index the SQL performance monitor suggest:
/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 94.7742%.
*/
/*
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblBigTable] ([Row])
INCLUDE ([pkId],[String01])
GO
*/
This will cause this error. Can we solve that following problem somehow? After removing the index again the application starts without problems.
Jul 03, 2015 6:57
Previously we had issues with a bad performing SQL that is in top in Microsoft SQL Server Management Studio (Activity monitor). It was found out that this was used by a version sync that ensure that the content versions used by the Content Repository and the Catalog UI are up to date with the change. As we import all product data from other systems, we don't care about this, and in Commerce 8.9 this new feauture was added to disable this job (http://world.episerver.com/blogs/Magnus-Rahl/2015/3/optional-performance-tweaks-in-episerver-commerce-8-9/).
After upgrade to 8.9 and defining this in Web.config, we still see this SQL running very often causing a lot of trouble for the SQL environment:
Have we missed anything?