R6 anyone recognize this query?

[AbleMods]

During a recent performance analysis of a busy Gold R6 site, I found this query to be the most time consuming.

I'm not entirely sure where it's coming from - almost looks like a category page query but the ORDER BY SUM(Quantity) DESC is throwing me off.

Any thoughts?

SELECT P.ProductId AS PrimaryKey FROM ac_Products P INNER JOIN ac_OrderItems OI ON P.ProductId = OI.ProductId WHERE P.VisibilityId = ? AND P.ProductId IN (SELECT CatalogNodeId FROM ac_CatalogNodes WHERE CatalogNodeTypeId = ? AND CategoryId IN (SELECT CategoryId FROM ac_Categories WHERE CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @p0))) GROUP BY P.ProductId ORDER BY SUM(Quantity) DESC

[Katie]

Hi Joe,

A couple of the Category Grids allow the list to be sorted by price (Low to High or High to Low).

Now, I'm not sure if it's relevant, but we were having a bit of trouble with one of the category pages, "Category Grid (Shallow Item Display) With Category Data". It is supposed to display (sub)categories, products, webpages, and links, and when used with a large number of items, the performance would suffer. I was asked to add a note to the documentation because it wasn't really possible to fix. So, I added this bit of text -

NOTE: This category display page should only be used for a category having small number of direct child objects.

I'm almost sure that this has not been very helpful, because you know this stuff a lot better than I do....

[AbleMods]

I appreciate the response Katie.

Now my curiosity has the best of me. Because it's such a unique result of information, I would think I could find it pretty easily. Yet it eludes me. It's a mystery and here are the clues:

1. It involves orders, so it practically has to be from the back end.
2. It groups by the Product sold, and includes a total of the quantity sold that sorts most sold to least sold.
3. It seems to offer some sort of filter by category.

My first instinct is a sales report.

Or, it could be something custom that I am not aware of....

I so need to get out more

[Katie]

I've asked the dev team to look into this. Judy had recently posted something about performance issues with some of the reports in R6, so we might as well check it out.

[jmestep]

It looks like it's popular products by category. From source code: ProductRepository.cs:

Code: Select all

        private const string POPULAR_PRODUCTS_BY_CATEGORY_SQL = "SELECT P.ProductId AS PrimaryKey FROM ac_Products P INNER JOIN ac_OrderItems OI ON P.ProductId = OI.ProductId WHERE P.VisibilityId = 0 AND P.ProductId IN (SELECT CatalogNodeId FROM ac_CatalogNodes WHERE CatalogNodeTypeId = 1 and CategoryId IN (SELECT CategoryId FROM ac_Categories WHERE CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = :categoryId))) GROUP BY P.ProductId ORDER BY SUM(Quantity) DESC";

There is one other one

Code: Select all

       private const string POPULAR_PRODUCTS_SQL = "SELECT P.ProductId AS PrimaryKey FROM ac_Products P INNER JOIN ac_OrderItems OI ON P.ProductId = OI.ProductId WHERE P.VisibilityId = 0 GROUP BY P.ProductId ORDER BY SUM(Quantity) DESC";

(My best friend is Control-Shift-F (find in solution) in Visual Studio)

[AbleMods]

Nice one Judy.

I've been using the Search Solution Explorer above the solution itself ( Ctrl + ; ) I thought that did the same thing...

But mine doesn't find "SUM(Quantity" when I search for it. Your Find in Solution does on the first try.

How weird.

Now, if I could cache that information by the Key value, I could cut down those queries significantly.

[jmestep]

That's a good idea on something like this. George did it for our category tree to help performance on larger sites. He also did similar to what Able did in Able 5 where the merchant could clear the cache at any time by passing ClearCache=1 in the query string.
I had a site that had a category page with about 1500 links in tabbed sections and I used .net's VaryBy CategoryId to cache that in the conlib because there was nothing in the conlib that was dynamic based on any other parameter, like user price, etc. It improved the performance drastically. That might be an easy way to do it in this conlib.