We found a slow query in our legacy system. What I see in the query is a duplicate fragment. Here's the full query:
DECLARE @SellerId INT;
DECLARE @DateFrom DATETIME;
DECLARE @DateTo DATETIME;
SET @SellerId = 5396884;
SET @DateFrom = '2016-01-05';
SET @DateTo = '2016-10-08';
DECLARE @CurrentDate DATETIME;
SET @CurrentDate = GETDATE();
CREATE TABLE #ReportDate (codes INT, dates DATETIME);
DECLARE @dif as INT;
DECLARE @cont as INT;
DECLARE @currdate as DATETIME;
SET @dif = DATEDIFF(day, @DateFrom, @DateTo);
SET @cont = 1;
SET @currdate = @DateFrom - 1;
WHILE (@cont <= @dif + 1)
BEGIN
SET @currdate = DATEADD(DAY, 1, @currdate);
INSERT INTO #ReportDate VALUES (@cont, @currdate);
SET @cont = @cont + 1;
END
/* HOW TO OPTIMIZE THIS ONE? */
SELECT
#ReportDate.dates as valid_date,
(
SELECT
COUNT(DISTINCT(nonCancelledSales.num_remito)) as actives
FROM
(
SELECT *
FROM salesView
WHERE
salesView.sell_id NOT IN
(
SELECT sell_id
FROM salesStates
WHERE
salesStates.aborted = 1
)
) nonCancelledSales
WHERE
nonCancelledSales.seller_id = @SellerId AND
nonCancelledSales.cancelled = 0 AND
nonCancelledSales.void = 0 AND
nonCancelledSales.hasDiscount = 0 AND
nonCancelledSales.dateOfSale <= #ReportDate.dates AND
nonCancelledSales.currentState = (SELECT MAX(hveest.date)
FROM salesStates hveest
WHERE
hveest.sell_id = nonCancelledSales.sell_id AND
hveest.date <= #ReportDate.dates) AND
nonCancelledSales.lastProductDate = (SELECT MAX(hvepro.date)
FROM productHistory hvepro
WHERE
hvepro.sell_id = nonCancelledSales.sell_id AND
hvepro.date <= #ReportDate.dates)
) total_actives,
(
SELECT
ISNULL(SUM(nonCancelledSales.paymentValue),0) as active
FROM
(
SELECT *
FROM salesView
WHERE
salesView.sell_id NOT IN
(
SELECT sell_id
FROM salesStates
WHERE
salesStates.aborted = 1
)
) nonCancelledSales
WHERE
nonCancelledSales.seller_id = @SellerId AND
nonCancelledSales.cancelled = 0 AND
nonCancelledSales.void = 0 AND
nonCancelledSales.hasDiscount = 0 AND
nonCancelledSales.dateOfSale <= #ReportDate.dates AND
nonCancelledSales.currentState = (SELECT MAX(hveest.date)
FROM salesStates hveest
WHERE
hveest.sell_id = nonCancelledSales.sell_id AND
hveest.date <= #ReportDate.dates) AND
nonCancelledSales.lastProductDate = (SELECT MAX(hvepro.date)
FROM productHistory hvepro
WHERE
hvepro.sell_id = nonCancelledSales.sell_id AND
hvepro.date <= #ReportDate.dates)
) active
FROM
#ReportDate
GROUP BY
#ReportDate.dates
DROP TABLE #ReportDate
Here are the two duplicated fragments I see:
(
SELECT
COUNT(DISTINCT(nonCancelledSales.num_remito)) as actives
FROM
(
SELECT *
FROM salesView
WHERE
salesView.sell_id NOT IN
(
SELECT sell_id
FROM salesStates
WHERE
salesStates.aborted = 1
)
) nonCancelledSales
WHERE
nonCancelledSales.seller_id = @SellerId AND
nonCancelledSales.cancelled = 0 AND
nonCancelledSales.void = 0 AND
nonCancelledSales.hasDiscount = 0 AND
nonCancelledSales.dateOfSale <= #ReportDate.dates AND
nonCancelledSales.currentState = (SELECT MAX(hveest.date)
FROM salesStates hveest
WHERE
hveest.sell_id = nonCancelledSales.sell_id AND
hveest.date <= #ReportDate.dates) AND
nonCancelledSales.lastProductDate = (SELECT MAX(hvepro.date)
FROM productHistory hvepro
WHERE
hvepro.sell_id = nonCancelledSales.sell_id AND
hvepro.date <= #ReportDate.dates)
) total_actives,
(
SELECT
ISNULL(SUM(nonCancelledSales.paymentValue),0) as active
FROM
(
SELECT *
FROM salesView
WHERE
salesView.sell_id NOT IN
(
SELECT sell_id
FROM salesStates
WHERE
salesStates.aborted = 1
)
) nonCancelledSales
WHERE
nonCancelledSales.seller_id = @SellerId AND
nonCancelledSales.cancelled = 0 AND
nonCancelledSales.void = 0 AND
nonCancelledSales.hasDiscount = 0 AND
nonCancelledSales.dateOfSale <= #ReportDate.dates AND
nonCancelledSales.currentState = (SELECT MAX(hveest.date)
FROM salesStates hveest
WHERE
hveest.sell_id = nonCancelledSales.sell_id AND
hveest.date <= #ReportDate.dates) AND
nonCancelledSales.lastProductDate = (SELECT MAX(hvepro.date)
FROM productHistory hvepro
WHERE
hvepro.sell_id = nonCancelledSales.sell_id AND
hvepro.date <= #ReportDate.dates)
) active
Is it fully necessary to duplicate the query ? In the first one he's getting:
COUNT(DISTINCT(nonCancelledSales.num_remito)) as actives
on the second one:
ISNULL(SUM(nonCancelledSales.paymentValue),0) as active
I suppose there has to be some way to rewrite the query but I'm not sure how.
Aucun commentaire:
Enregistrer un commentaire