Also I think Azure database compability_level on 100 is older SQL version.
120 = SQL Server 2014 as I understand, so guess is 100 = SQL 2010?
Extremely high database load after upgrading to 3.8 - did i do something wrong? Need help ASAP
- 230
- 1476
- 6/6/2013
- Norway
[email protected] wrote:
I think i copied that straight from database insight.
I will check tomorrow morning!
The query you mentioned above... is that something custom being done inside a 3rd party extension?
This is what I see in the installation script and 3.7 to 3.8 upgrade script, which looks little different when compared to your posted query.
--prepare filterable specification attribute option identifier (if requested)
IF @LoadFilterableSpecificationAttributeOptionIds = 1
BEGIN
CREATE TABLE #FilterableSpecs
(
[SpecificationAttributeOptionId] int NOT NULL
)
INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
SELECT DISTINCT [psam].SpecificationAttributeOptionId
FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
WHERE [psam].[AllowFiltering] = 1
AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])
--build comma separated list of filterable identifiers
SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
FROM #FilterableSpecs
DROP TABLE #FilterableSpecs
END
This is what I see in the installation script and 3.7 to 3.8 upgrade script, which looks little different when compared to your posted query.
--prepare filterable specification attribute option identifier (if requested)
IF @LoadFilterableSpecificationAttributeOptionIds = 1
BEGIN
CREATE TABLE #FilterableSpecs
(
[SpecificationAttributeOptionId] int NOT NULL
)
INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
SELECT DISTINCT [psam].SpecificationAttributeOptionId
FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
WHERE [psam].[AllowFiltering] = 1
AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])
--build comma separated list of filterable identifiers
SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
FROM #FilterableSpecs
DROP TABLE #FilterableSpecs
END
I think i copied that straight from database insight.
I will check tomorrow morning!
- 37
- 305
- 8/3/2016
- India
a.m. wrote:
When I compared, I could not find any difference between 3.7 & 3.8
Is it recent build of 3.8 you are talking about Andrei?
This stored procedure was modified in the latest version (3.80) to support "Filtering by specification attributes now use "OR" condition"
Try to replace your existing stored procedure with the one from version 3.70. Let me know if something is changed
Try to replace your existing stored procedure with the one from version 3.70. Let me know if something is changed
When I compared, I could not find any difference between 3.7 & 3.8
Is it recent build of 3.8 you are talking about Andrei?
- 230
- 1476
- 6/6/2013
- Norway
Answer from Microsoft
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
Any thoughts on this from the nopcommerce team? Is my current stored procedure wrong in any way?
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
Any thoughts on this from the nopcommerce team? Is my current stored procedure wrong in any way?