AbleCommerce Shopping Cart Software

Posted: **Tue Jul 14, 2015 6:56 am**

We are consistently seeing page timeouts trying to run the Sales By Product report against a large store database.

Using SQL Trace, I was able to determine the query that totals up coupons and discounts takes significantly longer than any other queries.

SELECT T1.ProductId, T1.CouponsTotal, T2.DiscountsTotal
FROM
(
SELECT ProductId = @p0 , SUM(oi.Price * oi.Quantity) AS CouponsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = @p0) AND OrderItemTypeId = @p2
) AS T1
FULL OUTER JOIN
(
SELECT ProductId = @p0, SUM(oi.Price * oi.Quantity) AS DiscountsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = @p0) AND OrderItemTypeId = @p5
) AS T2
on T1.productid = T2.productid

This query is horribly slow compared to the rest of the queries involved in the report.

Is there anything that can be done to improve the report performance? It's an important report for the client and we really need it to be usable.

We have full source code.

Posted: **Fri Jul 17, 2015 3:25 am**

No ideas on this one?

Posted: **Fri Jul 17, 2015 12:34 pm**

This is probably due to FULL OUTER JOIN. I wonder if you replace this with a union and maybe that would make some difference. In CommerceBuilder.Reporting.ReportDataSource.cs file locate following code in GetProductBreakdownSummary function

Code: Select all

// RETRIEVE THE COUPONS AND DISCOUNTS TOTALS
            string sql = @"SELECT T1.ProductId, T1.CouponsTotal, T2.DiscountsTotal
FROM  
(
SELECT ProductId = :productId , SUM(oi.Price * oi.Quantity) AS CouponsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = :productId) AND OrderItemTypeId = :couponTypeId
) AS T1
FULL OUTER JOIN 
(
SELECT ProductId = :productId, SUM(oi.Price * oi.Quantity) AS DiscountsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = :productId) AND OrderItemTypeId = :discountTypeId
) AS T2
 on T1.productid = T2.productid";

and replace it with following code

Code: Select all

// RETRIEVE THE COUPONS AND DISCOUNTS TOTALS
string sql = @"SELECT ProductId, SUM(CouponsTotal), SUM(DiscountsTotal) 
FROM  
(
SELECT ProductId = :productId , SUM(oi.Price * oi.Quantity) AS CouponsTotal, 0 AS DiscountsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = :productId) AND OrderItemTypeId = :couponTypeId

UNION ALL

SELECT ProductId = :productId, 0 AS CouponsTotal, SUM(oi.Price * oi.Quantity) AS DiscountsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = :productId) AND OrderItemTypeId = :discountTypeId
) x
GROUP BY ProductId";

Save the file, build CommerceBuilder project and finally build website and then see if it gives any boost to the query.

Posted: **Fri Jul 17, 2015 12:44 pm**

A much quicker way to test would be to run the query manually by replacing the variables in place. DiscountTypeId will be 4, CouponTypeId will be 5 while ProductId will be the Id of product you are going to calculate the coupons and discounts.

Posted: **Mon Jul 20, 2015 3:34 am**

Thanks a lot Mazhar, I'll give it a try and report back here with the results.

Posted: **Tue Jul 21, 2015 12:36 pm**

Ok tried it. Didn't seem to make much, if any, difference. Sorry.

As it is, a two day report takes 7 minutes to run.

Posted: **Wed Jul 22, 2015 12:42 pm**

I doesn't look like those two sub-queries (that are joined with the union all in the modified code) or the "inner" selects (the ones in the IN clause) are taking into consideration the date range chosen. You could try joining to the ac_Orders table (in each sub-query) and limiting OrderDate in the where clause. Hopefully Mazhar can tell you if I'm off in left field with that suggestion. Also, you could try creating an index on the DiscountTypeId field of the ac_OrderItems table if there isn't one already and see if that helps. Of course, your database size will increase and changes to that table will take a little longer because there's another index to update. I can't test these ideas because I'm not using discounts or coupons, and I haven't updated our site to R10.

Posted: **Fri Nov 20, 2015 1:34 am**

I have updated the query to use the DATE range filter for the inner SELECT. It should improve the query performance and should definitely decrease the processing load for large databases. Here I am posting the updated query, I want you to try it and post feedback.

Code: Select all

SELECT T1.ProductId, T1.CouponsTotal, T2.DiscountsTotal 
FROM  
(
SELECT ProductId = :productId , SUM(oi.Price * oi.Quantity) AS CouponsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN 
(
	SELECT ioi.OrderItemId FROM ac_OrderItems ioi
	Inner Join ac_Orders io ON ioi.OrderId = io.OrderId
	WHERE ioi.ProductId = :productId
	AND io.OrderDate >= :fromDate
	AND io.OrderDate <= :toDate
) 
AND OrderItemTypeId = :couponTypeId
) AS T1
FULL OUTER JOIN 
(
SELECT ProductId = :productId, SUM(oi.Price * oi.Quantity) AS DiscountsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN 
(
	SELECT OrderItemId FROM ac_OrderItems ioi
	Inner Join ac_Orders io ON ioi.OrderId = io.OrderId
	WHERE ProductId = :productId
	AND io.OrderDate >= :fromDate
	AND io.OrderDate <= :toDate	
) 
AND OrderItemTypeId = :discountTypeId	
) AS T2
 on T1.productid = T2.productid

You can replace it in the code, and pass the fromDate and toDate parameters. Or can test on the database manually. Do not forgot to replace the parameters with values:

:couponTypeId = 5
:discountTypeId = 4
:fromDate = your from date (in varchar or string format)
:toDate = your to date
:productId = your product id

Posted: **Mon Nov 23, 2015 4:20 am**

Naveed I'm not sure how to test your query. Hard-coding the criteria for a specific product id isn't going to give me any way to compare against the original amount of time.

Do you have this as an updated GetSalesByProduct() routine that I can paste into the code? Then I could run the report before, recompile with your change and then run it again. With that I could give you an accurate report on the difference.

Posted: **Thu Nov 26, 2015 1:57 am**

You can replace the query as suggested by Mazhar, here I repeat the steps:

In CommerceBuilder.Reporting.ReportDataSource.cs file locate following code in GetProductBreakdownSummary function

Code: Select all

// RETRIEVE THE COUPONS AND DISCOUNTS TOTALS
            string sql = @"SELECT T1.ProductId, T1.CouponsTotal, T2.DiscountsTotal
FROM  
(
SELECT ProductId = :productId , SUM(oi.Price * oi.Quantity) AS CouponsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = :productId) AND OrderItemTypeId = :couponTypeId
) AS T1
FULL OUTER JOIN 
(
SELECT ProductId = :productId, SUM(oi.Price * oi.Quantity) AS DiscountsTotal FROM ac_OrderItems oi
WHERE oi.ParentItemId IN (SELECT OrderItemId FROM ac_OrderItems WHERE ProductId = :productId) AND OrderItemTypeId = :discountTypeId
) AS T2
 on T1.productid = T2.productid";

and replace it with the above mentioned query:

Code: Select all

    SELECT T1.ProductId, T1.CouponsTotal, T2.DiscountsTotal
    FROM 
    (
    SELECT ProductId = :productId , SUM(oi.Price * oi.Quantity) AS CouponsTotal FROM ac_OrderItems oi
    WHERE oi.ParentItemId IN
    (
       SELECT ioi.OrderItemId FROM ac_OrderItems ioi
       Inner Join ac_Orders io ON ioi.OrderId = io.OrderId
       WHERE ioi.ProductId = :productId
       AND io.OrderDate >= :fromDate
       AND io.OrderDate <= :toDate
    )
    AND OrderItemTypeId = :couponTypeId
    ) AS T1
    FULL OUTER JOIN
    (
    SELECT ProductId = :productId, SUM(oi.Price * oi.Quantity) AS DiscountsTotal FROM ac_OrderItems oi
    WHERE oi.ParentItemId IN
    (
       SELECT OrderItemId FROM ac_OrderItems ioi
       Inner Join ac_Orders io ON ioi.OrderId = io.OrderId
       WHERE ProductId = :productId
       AND io.OrderDate >= :fromDate
       AND io.OrderDate <= :toDate   
    )
    AND OrderItemTypeId = :discountTypeId   
    ) AS T2
    on T1.productid = T2.productid

We also need to pass two new parameter values, so locate the following codes lines below the query:

Code: Select all

// query the product coupon and discount totals
                sqlQuery.SetParameter("productId", product.Id);
                sqlQuery.SetParameter("couponTypeId", (short)OrderItemType.Coupon);
                sqlQuery.SetParameter("discountTypeId", (short)OrderItemType.Discount);

And replace with:

Code: Select all

// query the product coupon and discount totals
                sqlQuery.SetParameter("productId", product.Id);
                sqlQuery.SetParameter("couponTypeId", (short)OrderItemType.Coupon);
                sqlQuery.SetParameter("discountTypeId", (short)OrderItemType.Discount);
                sqlQuery.SetParameter("fromDate", fromDate);
                sqlQuery.SetParameter("toDate", toDate);

Save the file, build CommerceBuilder project and finally build website and then see if it gives any boost to the query.

Posted: **Mon Nov 30, 2015 7:24 am**

Ok, tried it. Didn't help any, sorry - report still times out and no results are displayed.

Posted: **Thu Dec 17, 2015 2:18 am**

Joe I was wondering if you could provide some idea about data size to reproduce the issue. How many orders are there approximately when this happens plus how many products are showing up on report? What is the largest quantity for products on report?

Posted: **Fri Dec 18, 2015 7:20 am**

I couldn't even get it run on my last attempt, so I can't tell you total products.

What I can tell is the record totals for all tables involved in the report. I'm hoping that'll be enough to help you.

ac_OrderStatuses = 21 records
ac_OrderItems = 3,445,292 ( LOL !!)
ac_Products = 2,897

Hope that helps.

Posted: **Wed Mar 09, 2016 12:30 pm**

The new updated version in R11 still will not load without timing out.

Posted: **Thu Mar 10, 2016 3:55 am**

compunerdy wrote:The new updated version in R11 still will not load without timing out.

How much data you have ? In our tests the improvements made significant difference over huge data set of 20K orders. We also removed the default binding allowing merchant to choose the desired time span first before automatically binding over page load. How many products are there on report? How many orders in total?

Posted: **Thu Mar 10, 2016 6:42 am**

I have tracked this down to a evidently corrupt inventory item.

I noticed it was only giving me a error when I tried to load a certain vendor. I then narrowed it down further to a certain item that was under that vendor. I then copied that item and put it under the vendor and created a sale for it and it loaded fine. So evidently something is bugged out in the system with the history of the item. What is weird is the item loads find when I select ALL vendors but not when I selected the vendor it is under.

Is there a SQL statement or something I can run to find a corrupt link or something in the database for that item? I would rather not loose the sales history for it by just replacing it.

AbleCommerce Shopping Cart Software

Gold R10 Sales By Product report timeout

Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout

Re: Gold R10 Sales By Product report timeout