Extremely high database load after upgrading to 3.8 - did i do something wrong? Need help ASAP!

Hi.

My azure database is kneeling. The following query is running constantly, and for a long time.

INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
          SELECT DISTINCT [psam].SpecificationAttributeOptionId
          FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
              WHERE [psam].[AllowFiltering] = 1
              AND [psam].[ProductId] IN (
  SELECT p.Id
  FROM
    Product p with (NOLOCK)
    LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId
  WHERE
    p.Deleted = 0
    AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)
    AND pcm.IsFeaturedProduct = 0
    AND p.VisibleIndividually = 1
      AND p.Published = 1
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, '1/1/1900') and ISNULL(p.AvailableEndDateTimeUtc, '1/1/2999')))

As far as i know this is a temp table created by a stored procedure. But, is it my plugin, or is this code in nopcommerce? The problems started with 4 of my sites after upgrading to 3.8.


Does anyone know what this is? Could it be a problem with my nop templates ajax filter?

Hi hkreklame,

This is the default nopCommerce stored procedure. Please note that the Ajax Filters stored procedure is used only when you filter the products.
For example when a category page is loaded the default nopCommerce stored procedure is used to load the products and only when you select a filter then the Ajax Filters stored procedure is used.
Please check if you are using the latest version of nopCommerce 3.80 since there was an update.

I do have 3.8 of all plugins. Was there any updates which is critical after 3.8 came?

Hi ,

Yes, there was. You can read more here. The problem was in the loading of the products in nopCommerce in general but affected all plugins that were using some of the core libraries.
Anyway if you update to the latest version of nopCommerce and our products and check again (not sure if the issues you experience now are related to the update though).

I did the changes, but im still having serious problems with 3 databases.

We have 8 webstores, 3 are struggeling.

The ones with problems are the ones with most products. Have you guys noticed any problem with the 3.8 version yet?

Microsoft has been investigating the problems, and they have the following hypothesis:

Our current hypothesis is that the issue is related to compilations of the query you already identified and that you shared in the nopCommerce forum.

I had a look at nopCommerce publicly code repository in order to speed up the analysis and found that this query seems to be called by stored procedure ProductLoadAllPaged (https://github.com/nopSolutions/nopCommerce/blob/develop/src/Presentation/Nop.Web/App_Data/Install/SqlServer.StoredProcedures.sql).
Please note this may or may not be the code you are running; I am assuming you are using the latest code base published on the repository.

The relevant code section is:

--prepare filterable specification attribute option identifier (if requested)
IF @LoadFilterableSpecificationAttributeOptionIds = 1
    BEGIN        
         CREATE TABLE #FilterableSpecs
         (
              [SpecificationAttributeOptionId] int NOT NULL
         )
    DECLARE @sql_filterableSpecs nvarchar(max)
    SET @sql_filterableSpecs = '
             INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
             SELECT DISTINCT [psam].SpecificationAttributeOptionId
             FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
                 WHERE [psam].[AllowFiltering] = 1
                 AND [psam].[ProductId] IN (' + @sql + ')'

    EXEC sp_executesql @sql_filterableSpecs

According to our hypothesis (which we are still working on and which is not confirmed yet) the issue arises when the table #FilterableSpecs is created in the stored procedure scope and is then referenced in the scope of the dynamic query.
The dynamic query is performing a single statement to retrieve a result set and put it into the #FilterableSpecs temporary table.
There is an alternative form that allows to achieve the same result and avoids referencing the temporary table in the dynamic code.

The alternative form would be something like this (changes are highlighted):

--prepare filterable specification attribute option identifier (if requested)
IF @LoadFilterableSpecificationAttributeOptionIds = 1
    BEGIN        
         CREATE TABLE #FilterableSpecs
         (
              [SpecificationAttributeOptionId] int NOT NULL
         )
    DECLARE @sql_filterableSpecs nvarchar(max)
    SET @sql_filterableSpecs = '
             SELECT DISTINCT [psam].SpecificationAttributeOptionId
             FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
                 WHERE [psam].[AllowFiltering] = 1
                 AND [psam].[ProductId] IN (' + @sql + ')'

    INSERT #FilterableSpecs ([SpecificationAttributeOptionId]) EXEC(@sql_filterableSpecs)

Can you please check with nopCommerce if this may be a valid alternative and see if there is any way to test it?

As a further note, you may disable forced parameterization.




Do you guys have any input that could help me?

Hi hkreklame,

No, we haven't noticed any problems with nopCommerce 3.80.
I noticed that you have asked the same question in the nopCommerce forums.
We better keep the conversation on a single place so if we find anything I will write in the nopCommerce forum (since it does not seem to be a problem with the Ajax Filters plugin).

Thank you!