Performance issue with version sync SQL

Rasmus Hollin

Vote:

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?

#122870

Jun 17, 2015 7:15

Quan Mai

Vote:

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.

#122874

Jun 17, 2015 9:19

Rasmus Hollin

Vote:

Sounds good! Is there any bug or story connected to this, so we can track the proceedings? When is next release planned?

#122875

Jun 17, 2015 9:28

Quan Mai

Vote:

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.

#122876

Jun 17, 2015 9:31

Rasmus Hollin

Vote:

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?

#122877

Jun 17, 2015 9:36

Quan Mai

Vote:

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.

#122880

Jun 17, 2015 9:45

Ton Nguyen

Vote:

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.

#122983

Jun 22, 2015 11:21

Alex Yutsou

Vote:

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.

#123382

Jul 02, 2015 18:08

Quan Mai

Vote:

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.

#123384

Edited, Jul 02, 2015 20:18

Rasmus Hollin

Vote:

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.

#123391

Jul 03, 2015 6:57