jeudi 11 août 2016

Query optimization. Duplicate subqueries

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