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.
.com
