I need help with SQL queries

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 2, 2018 at 9:31 am

  #408581

  I need query in following format
  CustNo ,  YTDSales,  PreviousYearSales, MTDSales

  I have query
  Declare @StartDate date = '01-01-2018', @EndDate date = '09-30-2018' 

  Select [CustNo],sum(SalesAmt) YTDSales from SalesTable 
  Where saleDate between @StartDate and @EndDate
  Group by [CustNo]

  How I will do group by using daterange parameter

• below86

  SSChampion

  Points: 11513

  More actions

  October 2, 2018 at 10:36 am

  #2007738

  Looks like you got the YTD numbers.  Use the DATEADD to determine your Previous year start and end dates, same for month to date.

  Start by writing these as 3 separate quires, once you've determine you have the right values for each then think about how to combine them into one report.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 2, 2018 at 10:41 am

  #2007739

  below86 - Tuesday, October 2, 2018 10:36 AM

  Looks like you got the YTD numbers.  Use the DATEADD to determine your Previous year start and end dates, same for month to date.

  Start by writing these as 3 separate quires, once you've determine you have the right values for each then think about how to combine them into one report.

  I just wanted to know how I can write in one query using Group by and Case statement ?

  I am not sure about query performance so.

  Thank you

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  October 2, 2018 at 11:55 am

  #2007757

  Declare @StartDate date = '01-01-2018', @EndDate date = '09-30-2018'

  DECLARE @PrevYearStart DATE = DATEADD(YEAR, -1, @StartDate),
      @PrevYearEnd DATE = DATEADD(YEAR, -1, @EndDate),
      @MonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, @EndDate), 0)

  Select [CustNo],
      SUM(CASE WHEN saleDate >= @StartDate THEN SalesAmt END) AS YTDSales,
      SUM(CASE WHEN saleDate >= @MonthStart THEN SalesAmt END) AS MTDSales,
      SUM(CASE WHEN saleDate <= @PrevYearEnd THEN SalesAmt END) AS PrevYTDSales,
      SUM(CASE WHEN saleDate < @StartDate THEN SalesAmt END) AS PrevYearTotalSales
  Where saleDate between @PrevYearStart and @EndDate  /*  The whole range.  */
  Group by [CustNo]

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• below86

  SSChampion

  Points: 11513

  More actions

  October 2, 2018 at 12:33 pm

  #2007767

  Just added a few tweaks:
  DECLARE @StartDate DATE;
DECLARE @EndDate DATE;
DECLARE @PrevYearStart DATE;
DECLARE @PrevYTDEnd DATE;
DECLARE @MonthStart DATE;

  SET @StartDate = '01-01-2018';
SET @EndDate = DATEADD(DAY, 1, '09-30-2018');
SET @PrevYearStart = DATEADD(YEAR, -1, @StartDate);
SET @PrevYTDEnd = DATEADD(YEAR, -1, @EndDate);
SET @MonthStart = DATEADD(MONTH, -1, @EndDate);

  SELECT CustNo,
  SUM(CASE    WHEN saleDate >= @StartDate
                    THEN SalesAmt
                ELSE
                    0
    END) AS YTDSales,
  SUM(CASE    WHEN saleDate >= @MonthStart
                    THEN SalesAmt
                ELSE
                    0
    END) AS MTDSales,
  SUM(CASE    WHEN saleDate < @PrevYTDEnd
                    THEN SalesAmt
                ELSE
                    0
    END) AS PrevYTDSales,
  SUM(CASE    WHEN saleDate < @StartDate
                    THEN SalesAmt
                ELSE
                    0
    END) AS PrevYearTotalSales
FROM SalesTable
WHERE saleDate >= @PrevYearStart
    AND saleDate < @EndDate /* The whole range. */
Group by CustNo
;

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  October 2, 2018 at 12:57 pm

  #2007776

  below86 - Tuesday, October 2, 2018 12:33 PM

  Just added a few tweaks:
  DECLARE @StartDate DATE;
  DECLARE @EndDate DATE;
  DECLARE @PrevYearStart DATE;
  DECLARE @PrevYTDEnd DATE;
  DECLARE @MonthStart DATE;

  SET @StartDate = '01-01-2018';
  SET @EndDate = DATEADD(DAY, 1, '09-30-2018');
  SET @PrevYearStart = DATEADD(YEAR, -1, @StartDate);
  SET @PrevYTDEnd = DATEADD(YEAR, -1, @EndDate);
  SET @MonthStart = DATEADD(MONTH, -1, @EndDate);

  SELECT CustNo,
  SUM(CASE    WHEN saleDate >= @StartDate
                    THEN SalesAmt
                ELSE
                    0
    END) AS YTDSales,
  SUM(CASE    WHEN saleDate >= @MonthStart
                    THEN SalesAmt
                ELSE
                    0
    END) AS MTDSales,
  SUM(CASE    WHEN saleDate < @PrevYTDEnd
                    THEN SalesAmt
                ELSE
                    0
    END) AS PrevYTDSales,
  SUM(CASE    WHEN saleDate < @StartDate
                    THEN SalesAmt
                ELSE
                    0
    END) AS PrevYearTotalSales
FROM SalesTable
WHERE saleDate >= @PrevYearStart
    AND saleDate < @EndDate /* The whole range. */
Group by CustNo
;

  Dates is one of the few cases where you can safely use closed intervals.  I chose to do so in this case, because it's both more concise and it matches people's expectations better.

  I also prefer to use NULL instead of 0 to represent data that is missing and inapplicable.  Since NULL is the default when not otherwise specified in a CASE expression, I chose not to include an ELSE clause in order to keep it shorter and simpler.

  The tweak that I think merits the most consideration is computing the @StartDate from the @EndDate.  This ensures both that the @StartDate is <= the @EndDate and that the range is within one year.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• below86

  SSChampion

  Points: 11513

  More actions

  October 2, 2018 at 1:17 pm

  #2007783

  If I know for sure the date field does NOT carry any time values then I agree with using the between.  But if it has time, you could miss records.  Not knowing, I assumed it would have time.
  This would miss the $2,000.00 sale.
  DECLARE @StartDate DATE;
DECLARE @EndDate DATE;
DECLARE @PrevYearStart DATE;
DECLARE @PrevYTDEnd DATE;
DECLARE @MonthStart DATE;

  SET @StartDate = '01-01-2018';
SET @EndDate = '09-30-2018';
--SET @EndDate = DATEADD(DAY, 1, '09-30-2018');
SET @PrevYearStart = DATEADD(YEAR, -1, @StartDate);
SET @PrevYTDEnd = DATEADD(YEAR, -1, @EndDate);
--SET @MonthStart = DATEADD(MONTH, -1, @EndDate);
SET @MonthStart = DATEADD(MONTH, -1, DATEADD(DAY, 1, @EndDate));

  DECLARE @SalesTable TABLE
(
    CustNo        INT,
    saleDate    DATETIME,
    SalesAmt    DECIMAL(13, 2)
)
;

  INSERT INTO @SalesTable
VALUES (1, '02/01/2018', 500.00),
        (1, '09/30/2018', 350.00),
        (1, '09/30/2018 03:56', 2000.00);

  SELECT CustNo,
SUM(CASE  WHEN saleDate >= @StartDate
       THEN SalesAmt
      ELSE
       0
  END) AS YTDSales,
SUM(CASE  WHEN saleDate >= @MonthStart
       THEN SalesAmt
      ELSE
       0
  END) AS MTDSales,
SUM(CASE  WHEN saleDate <= @PrevYTDEnd
       THEN SalesAmt
      ELSE
       0
  END) AS PrevYTDSales,
SUM(CASE  WHEN saleDate < @StartDate
       THEN SalesAmt
      ELSE
       0
  END) AS PrevYearTotalSales
FROM @SalesTable
--WHERE saleDate >= @PrevYearStart
--  AND saleDate < @EndDate /* The whole range. */
WHERE saleDate BETWEEN @PrevYearStart AND @EndDate /* The whole range. */
Group by CustNo
;

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 2, 2018 at 2:04 pm

  #2007790

  below86 - Tuesday, October 2, 2018 1:17 PM

  If I know for sure the date field does NOT carry any time values then I agree with using the between.  But if it has time, you could miss records.  Not knowing, I assumed it would have time.
  This would miss the $2,000.00 sale.
  DECLARE @StartDate DATE;
  DECLARE @EndDate DATE;
  DECLARE @PrevYearStart DATE;
  DECLARE @PrevYTDEnd DATE;
  DECLARE @MonthStart DATE;

  SET @StartDate = '01-01-2018';
  SET @EndDate = '09-30-2018';
  --SET @EndDate = DATEADD(DAY, 1, '09-30-2018');
  SET @PrevYearStart = DATEADD(YEAR, -1, @StartDate);
  SET @PrevYTDEnd = DATEADD(YEAR, -1, @EndDate);
  --SET @MonthStart = DATEADD(MONTH, -1, @EndDate);
  SET @MonthStart = DATEADD(MONTH, -1, DATEADD(DAY, 1, @EndDate));

  DECLARE @SalesTable TABLE
  (
      CustNo        INT,
      saleDate    DATETIME,
      SalesAmt    DECIMAL(13, 2)
  )
  ;

  INSERT INTO @SalesTable
  VALUES (1, '02/01/2018', 500.00),
          (1, '09/30/2018', 350.00),
          (1, '09/30/2018 03:56', 2000.00);

  SELECT CustNo,
SUM(CASE  WHEN saleDate >= @StartDate
       THEN SalesAmt
      ELSE
       0
  END) AS YTDSales,
SUM(CASE  WHEN saleDate >= @MonthStart
       THEN SalesAmt
      ELSE
       0
  END) AS MTDSales,
SUM(CASE  WHEN saleDate <= @PrevYTDEnd
       THEN SalesAmt
      ELSE
       0
  END) AS PrevYTDSales,
SUM(CASE  WHEN saleDate < @StartDate
       THEN SalesAmt
      ELSE
       0
  END) AS PrevYearTotalSales
FROM @SalesTable
--WHERE saleDate >= @PrevYearStart
--  AND saleDate < @EndDate /* The whole range. */
WHERE saleDate BETWEEN @PrevYearStart AND @EndDate /* The whole range. */
Group by CustNo
;

  Thank you for your help,
  I have a scenario where I have millons of rows and I am gettting result very slower, So using case conditions in the query is slowing down instead of using sub query for each section YTD MTD.
  I have joins on some tables too and showing values from there.

  Please suggest
  Thanks in Advance.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  October 2, 2018 at 2:21 pm

  #2007793

  rjp123 - Tuesday, October 2, 2018 2:04 PM

  Thank you for your help,
  I have a scenario where I have millons of rows and I am gettting result very slower, So using case conditions in the query is slowing down instead of using sub query for each section YTD MTD.
  I have joins on some tables too and showing values from there.

  Please suggest
  Thanks in Advance.

  The CASE conditions should not be slowing down your query significantly.  We can't really help you much, because you (a) haven't posted the query that you are actually using, and (b) haven't posted the query plan (actual preferred over estimated).  I suspect the actual issue is with your joins.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  October 2, 2018 at 2:24 pm

  #2007795

  below86 - Tuesday, October 2, 2018 1:17 PM

  If I know for sure the date field does NOT carry any time values then I agree with using the between.  But if it has time, you could miss records.  Not knowing, I assumed it would have time.
  This would miss the $2,000.00 sale.
  DECLARE @StartDate DATE;
  DECLARE @EndDate DATE;
  DECLARE @PrevYearStart DATE;
  DECLARE @PrevYTDEnd DATE;
  DECLARE @MonthStart DATE;

  SET @StartDate = '01-01-2018';
  SET @EndDate = '09-30-2018';
  --SET @EndDate = DATEADD(DAY, 1, '09-30-2018');
  SET @PrevYearStart = DATEADD(YEAR, -1, @StartDate);
  SET @PrevYTDEnd = DATEADD(YEAR, -1, @EndDate);
  --SET @MonthStart = DATEADD(MONTH, -1, @EndDate);
  SET @MonthStart = DATEADD(MONTH, -1, DATEADD(DAY, 1, @EndDate));

  DECLARE @SalesTable TABLE
  (
      CustNo        INT,
      saleDate    DATETIME,
      SalesAmt    DECIMAL(13, 2)
  )
  ;

  INSERT INTO @SalesTable
  VALUES (1, '02/01/2018', 500.00),
          (1, '09/30/2018', 350.00),
          (1, '09/30/2018 03:56', 2000.00);

  SELECT CustNo,
SUM(CASE  WHEN saleDate >= @StartDate
       THEN SalesAmt
      ELSE
       0
  END) AS YTDSales,
SUM(CASE  WHEN saleDate >= @MonthStart
       THEN SalesAmt
      ELSE
       0
  END) AS MTDSales,
SUM(CASE  WHEN saleDate <= @PrevYTDEnd
       THEN SalesAmt
      ELSE
       0
  END) AS PrevYTDSales,
SUM(CASE  WHEN saleDate < @StartDate
       THEN SalesAmt
      ELSE
       0
  END) AS PrevYearTotalSales
FROM @SalesTable
--WHERE saleDate >= @PrevYearStart
--  AND saleDate < @EndDate /* The whole range. */
WHERE saleDate BETWEEN @PrevYearStart AND @EndDate /* The whole range. */
Group by CustNo
;

  I assumed that the fields were DATE, because he defined the variables as DATE.  Obviously, he should be using half-closed intervals if the saleDate contains datetime data.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 2, 2018 at 2:50 pm

  #2007797

  drew.allen - Tuesday, October 2, 2018 2:21 PM

  rjp123 - Tuesday, October 2, 2018 2:04 PM

  Thank you for your help,
  I have a scenario where I have millons of rows and I am gettting result very slower, So using case conditions in the query is slowing down instead of using sub query for each section YTD MTD.
  I have joins on some tables too and showing values from there.

  Please suggest
  Thanks in Advance.

  The CASE conditions should not be slowing down your query significantly.  We can't really help you much, because you (a) haven't posted the query that you are actually using, and (b) haven't posted the query plan (actual preferred over estimated).  I suspect the actual issue is with your joins.

  Drew

  This is my actual query
  Declare @StartDate date = '01-01-2018', @EndDate date = '09-30-2018'

  SELECT C.No_,C.Name, C.[SP], C.Address, C.City, C.[CL], C.[PT], C.[Post Code], C.County,
          ISNULL ((SELECT   SUM(SalesAmount) AS Amount
             FROM    SalesInvoice
             WHERE   (BCustNo = C.No_) AND (ShipDate >= @StartDate) AND (ShipDate <= @EndDate)), 0)
           AS YTDSales, ISNULL
            ((SELECT   SUM(SalesAmt - AVGCst * Qty) AS Amount
             FROM    SalesInvoice AS SalesInvoice_5
             WHERE   (BCustNo = C.No_) AND (ShipDate >= @StartDate) AND (ShipDate <= @EndDate)), 0)
           AS YTDGP, ISNULL
            ((SELECT   SUM(SalesAmount) AS Amount
             FROM    SalesInvoice AS SalesInvoice_4
             WHERE   (BCustNo = C.No_) AND (ShipDate >= DATEADD(year, - 1, @StartDate)) AND
                      (ShipDate <= DATEADD(year, - 1, @EndDate))), 0) AS PrevYTDSales, ISNULL
            ((SELECT   SUM(SalesAmt - AVGCst * Qty) AS Amount
             FROM    SalesInvoice AS SalesInvoice_3
             WHERE   (BCustNo = C.No_) AND (ShipDate >= DATEADD(year, - 1, @StartDate)) AND
                      (ShipDate <= DATEADD(year, - 1, @EndDate))), 0) AS PrevYTDGP, ISNULL
            ((SELECT   SUM(SalesAmount) AS Amount
             FROM    SalesInvoice AS SalesInvoice_2
             WHERE   (BCustNo = C.No_) AND (YEAR(ShipDate) = YEAR(@StartDate) - 1)), 0)
           AS PrevEOYSales, ISNULL
            ((SELECT   SUM(SalesAmt - AVGCst * Qty) AS Amount
             FROM    SalesInvoice AS SalesInvoice_1
             WHERE   (BCustNo = C.No_) AND (YEAR(ShipDate) = YEAR(@StartDate) - 1)), 0) AS PrevEOYGP,D.DVC
  FROM Customer C
  LEFT JOIN CustDim D ON C.No_ = D.No_
  WHERE (C.Dealer = 1) AND (C.[MaCustomer] = 1) OR (C.Dealer = 1) AND (C.[FC] = 1)

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 2, 2018 at 2:51 pm

  #2007798

  rjp123 - Tuesday, October 2, 2018 2:50 PM

  drew.allen - Tuesday, October 2, 2018 2:21 PM

  rjp123 - Tuesday, October 2, 2018 2:04 PM

  Thank you for your help,
  I have a scenario where I have millons of rows and I am gettting result very slower, So using case conditions in the query is slowing down instead of using sub query for each section YTD MTD.
  I have joins on some tables too and showing values from there.

  Please suggest
  Thanks in Advance.

  The CASE conditions should not be slowing down your query significantly.  We can't really help you much, because you (a) haven't posted the query that you are actually using, and (b) haven't posted the query plan (actual preferred over estimated).  I suspect the actual issue is with your joins.

  Drew

  This is my actual query
  Declare @StartDate date = '01-01-2018', @EndDate date = '09-30-2018'

  SELECT C.No_,C.Name, C.[SP], C.Address, C.City, C.[CL], C.[PT], C.[Post Code], C.County,
          ISNULL ((SELECT   SUM(SalesAmount) AS Amount
             FROM    SalesInvoice
             WHERE   (BCustNo = C.No_) AND (ShipDate >= @StartDate) AND (ShipDate <= @EndDate)), 0)
           AS YTDSales, ISNULL
            ((SELECT   SUM(SalesAmt - AVGCst * Qty) AS Amount
             FROM    SalesInvoice AS SalesInvoice_5
             WHERE   (BCustNo = C.No_) AND (ShipDate >= @StartDate) AND (ShipDate <= @EndDate)), 0)
           AS YTDGP, ISNULL
            ((SELECT   SUM(SalesAmount) AS Amount
             FROM    SalesInvoice AS SalesInvoice_4
             WHERE   (BCustNo = C.No_) AND (ShipDate >= DATEADD(year, - 1, @StartDate)) AND
                      (ShipDate <= DATEADD(year, - 1, @EndDate))), 0) AS PrevYTDSales, ISNULL
            ((SELECT   SUM(SalesAmt - AVGCst * Qty) AS Amount
             FROM    SalesInvoice AS SalesInvoice_3
             WHERE   (BCustNo = C.No_) AND (ShipDate >= DATEADD(year, - 1, @StartDate)) AND
                      (ShipDate <= DATEADD(year, - 1, @EndDate))), 0) AS PrevYTDGP, ISNULL
            ((SELECT   SUM(SalesAmount) AS Amount
             FROM    SalesInvoice AS SalesInvoice_2
             WHERE   (BCustNo = C.No_) AND (YEAR(ShipDate) = YEAR(@StartDate) - 1)), 0)
           AS PrevEOYSales, ISNULL
            ((SELECT   SUM(SalesAmt - AVGCst * Qty) AS Amount
             FROM    SalesInvoice AS SalesInvoice_1
             WHERE   (BCustNo = C.No_) AND (YEAR(ShipDate) = YEAR(@StartDate) - 1)), 0) AS PrevEOYGP,D.DVC
  FROM Customer C
  LEFT JOIN CustDim D ON C.No_ = D.No_
  WHERE (C.Dealer = 1) AND (C.[MaCustomer] = 1) OR (C.Dealer = 1) AND (C.[FC] = 1)

  Trying to make this query faster, but using group by using Case condition doesn't make any difference.  SalesInvoice is the view, where Its combination of all.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  October 2, 2018 at 3:28 pm

  #2007801

  If you are using a view, the problem could be in your view. We would also need the view definition.

  You still haven't supplied the query plan.  We really need that if we are going to help with your problem.  I do see some things immediately.

  You are using NON_SARGable criteria, specifically
  (YEAR(ShipDate) = YEAR(@StartDate) - 1)
  This will almost always cause a scan, which performs poorly.

  You are using subqueries when you should be using either a CTE or a CROSS APPLY.  You have three sets of two subqueries with EXACTLY THE SAME CONDITIONS but you are pulling out different calculations.  The means that you are unnecessarily doubling the number of reads that you need.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 7:41 am

  #2007864

  drew.allen - Tuesday, October 2, 2018 3:28 PM

  If you are using a view, the problem could be in your view. We would also need the view definition.

  You still haven't supplied the query plan.  We really need that if we are going to help with your problem.  I do see some things immediately.

  You are using NON_SARGable criteria, specifically
  (YEAR(ShipDate) = YEAR(@StartDate) - 1)
  This will almost always cause a scan, which performs poorly.

  You are using subqueries when you should be using either a CTE or a CROSS APPLY.  You have three sets of two subqueries with EXACTLY THE SAME CONDITIONS but you are pulling out different calculations.  The means that you are unnecessarily doubling the number of reads that you need.

  Drew

  SELECT CASE SL.[Type] WHEN 2 THEN SL.No_ ELSE dbo.ItemNo(SL.DocNo, SL.[LineNo]) END AS ItemNo,
        SL.PostDate AS ShipDate, SL.Amount AS SalesAmount, CASE SL.[Type] WHEN 5 THEN 0 ELSE ISNULL(I.[Unit Cost], 0) END AS AVGCost,
        CASE SL.[Type] WHEN 5 THEN 0 ELSE CAST(SL.QTY AS int) END AS Quantity,
        CASE SL.[Type] WHEN 2 THEN I.ItemCatCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(SL.DocNo,
        SL.[LineNo]), 1) END AS ItemCatCode,
        CASE SL.[Type] WHEN 2 THEN I.PGCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(SL.DocNo,
        SL.[LineNo]), 0) END AS PGCode, SL.[BilltoCustNo], LEFT(SL.[BilltoCustNo], 4) AS MajorAccNo, SH.SPCode,
        SL.StoCustno, SL.DocNo AS DocumentNo, SL.[LineNo] AS DocumentLine, 'Invoice' AS DocumentType, SH.[EDI Order], SH.ExtDocNo,
        LEFT(SH.SPostCode, 5) AS SPostCode, SL.VarCode
  FROM  SalesInvoice SL(NOLOCK) LEFT JOIN
        Item I(NOLOCK) ON SL.No_ = I.No_ LEFT JOIN
        InvHeader(NOLOCK) AS SH ON SH.No_ = SL.DocNo
  WHERE (SL.QTY <> 0) AND (SL.Type = 2 OR
        SL.Type = 5)
  UNION ALL
  SELECT CASE ML.[Type] WHEN 2 THEN ML.No_ ELSE dbo.ItemNo(ML.DocNo, ML.[LineNo]) END AS ItemNo,
        ML.PostDate AS ShipDate, - ML.Amount AS SalesAmount, CASE ML.[Type] WHEN 5 THEN 0 ELSE ISNULL(I1.[Unit Cost], 0) END AS AVGCost,
        CASE ML.[Type] WHEN 5 THEN 0 ELSE - CAST(ML.Quantity AS INT) END AS Quantity,
        CASE ML.[Type] WHEN 2 THEN I1.ItemCatCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(ML.DocNo,
        ML.[LineNo]), 1) END AS ItemCatCode,
        CASE ML.[Type] WHEN 2 THEN I1.PGCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(ML.DocNo,
        ML.[LineNo]), 0) END AS PGCode, ML.[BilltoCustNo], LEFT(ML.[BilltoCustNo], 4) AS MajorAccNo, MH.SPCode,
        ML.StoCustno, ML.DocNo, ML.[LineNo], 'Credit' AS DocumentType, 0 AS [EDI Order], MH.ExtDocNo, LEFT(MH.ShiptoCode, 5)
        AS SPostCode, ML.VarCode
  FROM  DB1.dbo.SalesCrMemoLine ML(NOLOCK) LEFT JOIN
        Item I1(Nolock) ON ML.No_ = I1.No_ LEFT JOIN
        DB1.dbo.SalesCrMemoHeader(Nolock) AS MH ON MH.No_ = ML.DocNo
  WHERE (ML.Type = 2 OR
        ML.Type = 5) AND (ML.Quantity <> 0)

  This is my View, using union all. below section is pretty fast, upper section is taking more time as it has millions of rows. and if I put date range, I can see indexes and all are there, but I am not sure when to rebuilt index or Reorganize, 

  Query plan is pretty big and I can't upload due to security reason, but yes, 20% I can see on all nested loop, I will remove the nested loop for sure.
  but not sure where exactly it is taking more time, not sure how to understand the query execution plan, its suggesting putting some non cluster index but not sure if that will make more difference,
  I am under impression that doing group by with all the columns can slower down my query as I am using so many different columns with sum and other aggregate.
  Appreciated you help
  Thank you very much for looking into it.
  

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 7:48 am

  #2007868

  rjp123 - Wednesday, October 3, 2018 7:41 AM

  drew.allen - Tuesday, October 2, 2018 3:28 PM

  If you are using a view, the problem could be in your view. We would also need the view definition.

  You still haven't supplied the query plan.  We really need that if we are going to help with your problem.  I do see some things immediately.

  You are using NON_SARGable criteria, specifically
  (YEAR(ShipDate) = YEAR(@StartDate) - 1)
  This will almost always cause a scan, which performs poorly.

  You are using subqueries when you should be using either a CTE or a CROSS APPLY.  You have three sets of two subqueries with EXACTLY THE SAME CONDITIONS but you are pulling out different calculations.  The means that you are unnecessarily doubling the number of reads that you need.

  Drew

  SELECT CASE SL.[Type] WHEN 2 THEN SL.No_ ELSE dbo.ItemNo(SL.DocNo, SL.[LineNo]) END AS ItemNo,
        SL.PostDate AS ShipDate, SL.Amount AS SalesAmount, CASE SL.[Type] WHEN 5 THEN 0 ELSE ISNULL(I.[Unit Cost], 0) END AS AVGCost,
        CASE SL.[Type] WHEN 5 THEN 0 ELSE CAST(SL.QTY AS int) END AS Quantity,
        CASE SL.[Type] WHEN 2 THEN I.ItemCatCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(SL.DocNo,
        SL.[LineNo]), 1) END AS ItemCatCode,
        CASE SL.[Type] WHEN 2 THEN I.PGCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(SL.DocNo,
        SL.[LineNo]), 0) END AS PGCode, SL.[BilltoCustNo], LEFT(SL.[BilltoCustNo], 4) AS MajorAccNo, SH.SPCode,
        SL.StoCustno, SL.DocNo AS DocumentNo, SL.[LineNo] AS DocumentLine, 'Invoice' AS DocumentType, SH.[EDI Order], SH.ExtDocNo,
        LEFT(SH.SPostCode, 5) AS SPostCode, SL.VarCode
  FROM  SalesInvoice SL(NOLOCK) LEFT JOIN
        Item I(NOLOCK) ON SL.No_ = I.No_ LEFT JOIN
        InvHeader(NOLOCK) AS SH ON SH.No_ = SL.DocNo
  WHERE (SL.QTY <> 0) AND (SL.Type = 2 OR
        SL.Type = 5)
  UNION ALL
  SELECT CASE ML.[Type] WHEN 2 THEN ML.No_ ELSE dbo.ItemNo(ML.DocNo, ML.[LineNo]) END AS ItemNo,
        ML.PostDate AS ShipDate, - ML.Amount AS SalesAmount, CASE ML.[Type] WHEN 5 THEN 0 ELSE ISNULL(I1.[Unit Cost], 0) END AS AVGCost,
        CASE ML.[Type] WHEN 5 THEN 0 ELSE - CAST(ML.Quantity AS INT) END AS Quantity,
        CASE ML.[Type] WHEN 2 THEN I1.ItemCatCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(ML.DocNo,
        ML.[LineNo]), 1) END AS ItemCatCode,
        CASE ML.[Type] WHEN 2 THEN I1.PGCode ELSE CatOrGroupByItemNo(DB1.dbo.ItemNo(ML.DocNo,
        ML.[LineNo]), 0) END AS PGCode, ML.[BilltoCustNo], LEFT(ML.[BilltoCustNo], 4) AS MajorAccNo, MH.SPCode,
        ML.StoCustno, ML.DocNo, ML.[LineNo], 'Credit' AS DocumentType, 0 AS [EDI Order], MH.ExtDocNo, LEFT(MH.ShiptoCode, 5)
        AS SPostCode, ML.VarCode
  FROM  DB1.dbo.SalesCrMemoLine ML(NOLOCK) LEFT JOIN
        Item I1(Nolock) ON ML.No_ = I1.No_ LEFT JOIN
        DB1.dbo.SalesCrMemoHeader(Nolock) AS MH ON MH.No_ = ML.DocNo
  WHERE (ML.Type = 2 OR
        ML.Type = 5) AND (ML.Quantity <> 0)

  This is my View, using union all. below section is pretty fast, upper section is taking more time as it has millions of rows. and if I put date range, I can see indexes and all are there, but I am not sure when to rebuilt index or Reorganize, 

  Query plan is pretty big and I can't upload due to security reason, but yes, 20% I can see on all nested loop, I will remove the nested loop for sure.
  but not sure where exactly it is taking more time, not sure how to understand the query execution plan, its suggesting putting some non cluster index but not sure if that will make more difference,
  I am under impression that doing group by with all the columns can slower down my query as I am using so many different columns with sum and other aggregate.
  Appreciated you help
  Thank you very much for looking into it.
  

  Plus I am querying the different database on same server. not sure if that can have performance issue, plus the db is read only where I am querying.