Category Product Count Performance

[dc8johnson]

We have a site that is checked daily for problems by McAfee Secure (http://www.mcafeesecure.com). We've noticed a large spike in SQL activity during a portion of their scan. Our database administrator ran a trace during one of these episodes and found the following query is responsible for 33% of the CPU usage and 94% of all page reads. This looks to be the query that is called to provide the Narrow by Category list with the product count.

Code: Select all

exec sp_executesql N'SELECT COUNT(DISTINCT P.ProductId) As TotalProducts FROM  (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE P.StoreId = @storeId AND PC.CatalogNodeTypeId = 1 AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId) AND P.VisibilityId = 0)',N'@storeId int,@categoryId int',@storeId=1,@categoryId=14

Our database administrator has reviewed this query and suggested that the following change would have no affect on the data output but would reduce the cost of the query by 2.9 times.

Code: Select all

exec sp_executesql N'SELECT COUNT(DISTINCT P.ProductId) As TotalProducts FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE P.StoreId = @storeId AND PC.CatalogNodeTypeId = 1 AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId) AND P.VisibilityId = 0',N'@storeId int,@categoryId int',@storeId=1,@categoryId=14

Since this query is part of the CommerceBuilder code, I am passing this on to you.

I hope this is useful.

[Kalamazoo]

Hi David,

I am having server trouble with an AC7.03 store with this very issue. Can you let me know what version you are running by chance?

Thanks for the information.

Phil Chrisman

[jmestep]

We found that out with a site with many products and it narrowed down to this in a category page
_SearchResultCount = ProductDataSource.NarrowSearchCount(_Keywords, this.CategoryId, _ManufacturerId, 0, 0, SortResults.SelectedValue.StartsWith("IsFeatured"));
or this
int totalProducts = ProductDataSource.NarrowSearchCount(_Keywords, category.CategoryId, _ManufacturerId, 0, 0);

[dc8johnson]

We are using 7.0.4 but I think it is the same query being used in 7.0.3.

Our site has about 20,000 active products and it is the total products that is the issue. We understand that the query isn't going to be fast. Our DBA reviewed the query and believes it can be optimized by changing as suggested.

I hope that clarifies things.