I need help with SQL queries

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  October 3, 2018 at 8:27 am

  #2007872

  A picture of the execution plan is not as helpful as the plan saved as a .sqlplan.  Right click, pick "Save Execution Plan as", save the file and post it.

  What are:
  Catorgroupbyitemno
  Dbo.Itemno
  If these are scalar functions, then they may be causing hidden RBAR.

  Why are you using NOLOCK????  If this is a query to produce invoices for billing, then it may not produce correct results.  
  Is the column shipdate a datetime data type, or a date type?  If it's a datetime, then your query is leaving off any records past midnight of 9/30/2018.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 11:02 am

  #2007903

  Michael L John - Wednesday, October 3, 2018 8:27 AM

  A picture of the execution plan is not as helpful as the plan saved as a .sqlplan.  Right click, pick "Save Execution Plan as", save the file and post it.

  What are:
  Catorgroupbyitemno - function below

  If these are scalar functions, then they may be causing hidden RBAR.

  Why are you using NOLOCK????  If this is a query to produce invoices for billing, then it may not produce correct results.  
  Is the column shipdate a datetime data type, or a date type?  If it's a datetime, then your query is leaving off any records past midnight of 9/30/2018.

  Catorgroupbyitemno - This function returns Product Category Item no by providing Itemno as parameter 
  Dbo.Itemno - This returns Itemno by providing values (docno and itemno)

  functions below
  ALTER FUNCTION [dbo].[Catorgroupbyitemno]
  (
      -- Add the parameters for the function here
      @ItemNo varchar(20),
      @Cate tinyint
  )
  RETURNS varchar(10)
  AS
  BEGIN
      
      DECLARE @Result varchar(10)

      
      IF @Cate = 1 
      BEGIN
          SELECT TOP(1) @Result = [ICatcode] FROM dbo.Item] WITH (NOLOCK) 
          WHERE No_ = @ItemNo
      END
      
      IF @Cate = 0 
      BEGIN
          SELECT TOP(1) @Result = [pgcode] FROM dbo.Item] WITH (NOLOCK) 
          WHERE No_ = @ItemNo
      END

      IF @Cate = 2 
      BEGIN
          SELECT TOP(1) @Result = [gd1code] FROM dbo.Item] WITH (NOLOCK) 
          WHERE No_ = @ItemNo
      END

      RETURN @Result

  END

  Dbo.Itemno - function below
  ALTER FUNCTION [dbo].[GetChargedItemNo]
  (
      -- Add the parameters for the function here
      @DocNo varchar(20),
      @LNo int
  )
  RETURNS varchar(20)
  AS
  BEGIN

      DECLARE @ItemNo varchar(20)

      
      SELECT TOP(1) @ItemNo = [ItemNo] FROM dbo.VEntry WITH (NOLOCK)
      WHERE [DLineNo] = @DocumentNo AND [DLineNo] = @LineNo

      RETURN @ItemNo

  END

  I am using Nolock as its locking tables and this tables used by many processes - The secondary db is readonly db setup with logshipping so seems like it will not do Dirty reads.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 3, 2018 at 11:18 am

  #2007908

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
declare @StartDate date = '01-01-2018'
   , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 1:14 pm

  #2007934

  Here I am attaching query executing plan.

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 1:15 pm

  #2007935

  rjp123 - Wednesday, October 3, 2018 11:02 AM

  Michael L John - Wednesday, October 3, 2018 8:27 AM

  A picture of the execution plan is not as helpful as the plan saved as a .sqlplan.  Right click, pick "Save Execution Plan as", save the file and post it.

  What are:
  Catorgroupbyitemno - function below

  If these are scalar functions, then they may be causing hidden RBAR.

  Why are you using NOLOCK????  If this is a query to produce invoices for billing, then it may not produce correct results.  
  Is the column shipdate a datetime data type, or a date type?  If it's a datetime, then your query is leaving off any records past midnight of 9/30/2018.

  Catorgroupbyitemno - This function returns Product Category Item no by providing Itemno as parameter 
  Dbo.Itemno - This returns Itemno by providing values (docno and itemno)

  functions below
  ALTER FUNCTION [dbo].[Catorgroupbyitemno]
  (
      -- Add the parameters for the function here
      @ItemNo varchar(20),
      @Cate tinyint
  )
  RETURNS varchar(10)
  AS
  BEGIN
      
      DECLARE @Result varchar(10)

      
      IF @Cate = 1 
      BEGIN
          SELECT TOP(1) @Result = [ICatcode] FROM dbo.Item] WITH (NOLOCK) 
          WHERE No_ = @ItemNo
      END
      
      IF @Cate = 0 
      BEGIN
          SELECT TOP(1) @Result = [pgcode] FROM dbo.Item] WITH (NOLOCK) 
          WHERE No_ = @ItemNo
      END

      IF @Cate = 2 
      BEGIN
          SELECT TOP(1) @Result = [gd1code] FROM dbo.Item] WITH (NOLOCK) 
          WHERE No_ = @ItemNo
      END

      RETURN @Result

  END

  Dbo.Itemno - function below
  ALTER FUNCTION [dbo].[GetChargedItemNo]
  (
      -- Add the parameters for the function here
      @DocNo varchar(20),
      @LNo int
  )
  RETURNS varchar(20)
  AS
  BEGIN

      DECLARE @ItemNo varchar(20)

      
      SELECT TOP(1) @ItemNo = [ItemNo] FROM dbo.VEntry WITH (NOLOCK)
      WHERE [DLineNo] = @DocumentNo AND [DLineNo] = @LineNo

      RETURN @ItemNo

  END

  I am using Nolock as its locking tables and this tables used by many processes - The secondary db is readonly db setup with logshipping so seems like it will not do Dirty reads.

  My query execution plan you need.

  Thanks

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 1:20 pm

  #2007936

  Lynn Pettis - Wednesday, October 3, 2018 11:18 AM

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
  declare @StartDate date = '01-01-2018'
     , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


  I Appreciate Lynn for taking a look, Actually I did it similarly by using temporary tables and then drop table at last.
  Still there is performance issue, Using without (Nolock) running query for long time can cause table lock and other process to stop correct?
  Please advise. I also attached query plan if that helps.

  Thanks

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  October 3, 2018 at 1:29 pm

  #2007937

  rjp123 - Wednesday, October 3, 2018 1:20 PM

  Lynn Pettis - Wednesday, October 3, 2018 11:18 AM

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
  declare @StartDate date = '01-01-2018'
     , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


  I Appreciate Lynn for taking a look, Actually I did it similarly by using temporary tables and then drop table at last.
  Still there is performance issue, Using without (Nolock) running query for long time can cause table lock and other process to stop correct?
  Please advise. I also attached query plan if that helps.

  Thanks

  Did you run Lynn's query?  Did it work, and did it work faster?

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 3, 2018 at 1:45 pm

  #2007940

  Michael L John - Wednesday, October 3, 2018 1:29 PM

  rjp123 - Wednesday, October 3, 2018 1:20 PM

  Lynn Pettis - Wednesday, October 3, 2018 11:18 AM

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
  declare @StartDate date = '01-01-2018'
     , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


  I Appreciate Lynn for taking a look, Actually I did it similarly by using temporary tables and then drop table at last.
  Still there is performance issue, Using without (Nolock) running query for long time can cause table lock and other process to stop correct?
  Please advise. I also attached query plan if that helps.

  Thanks

  Did you run Lynn's query?  Did it work, and did it work faster?

  And I would like to see an execution plan for it as well if possible, actual preferred but I will take an estimated.  There may be a way to speed it up but I need to see what SQL Server thinks first.

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 1:55 pm

  #2007945

  Michael L John - Wednesday, October 3, 2018 1:29 PM

  rjp123 - Wednesday, October 3, 2018 1:20 PM

  Lynn Pettis - Wednesday, October 3, 2018 11:18 AM

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
  declare @StartDate date = '01-01-2018'
     , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


  I Appreciate Lynn for taking a look, Actually I did it similarly by using temporary tables and then drop table at last.
  Still there is performance issue, Using without (Nolock) running query for long time can cause table lock and other process to stop correct?
  Please advise. I also attached query plan if that helps.

  Thanks

  Did you run Lynn's query?  Did it work, and did it work faster?

  Still have issues I did below.
  declare @StartDate date = '01-01-2018'
    , @EndDate date = '09-30-2018' ;

  SELECT  C.No_ as [No_],C.Name, C.Spcode,C.Address, C.City,C.[PostCode], C.County,D.dcValue into #Tmp
  FROM customer C
  LEFT JOIN vwCustdim D ON C.No_ = D.No_
  WHERE   (C.Dealer = 1) AND (C.MC = 1) OR (C.Dealer = 1) AND (C.FC = 1)

  Select billtocust,sum(SalesAmount) YTDSales,SUM(SalesAmount - ItemAVGCost * Quantity) YTDGP into #TmpYTD from vwSalesInvoice I Where I.ShipDate between @StartDate and @EndDate
  Group by billtocust

  Select billtocust,sum(SalesAmount) PrevYTDSales,SUM(SalesAmount - ItemAVGCost * Quantity) PrevYTDGP into #TmpLASTYTD from vwSalesInvoice I Where ShipDate between DATEADD(year, - 1, @StartDate) AND DATEADD(year, - 1, @EndDate)
  Group by billtocust

  Select billtocust,sum(SalesAmount) PrevEOYSales,SUM(SalesAmount - ItemAVGCost * Quantity) PrevEOYGP into #TmpPrevious from vwSalesInvoice I Where (YEAR(ShipDate) = YEAR(@StartDate) - 1)
  Group by billtocust

  Select T.*,Y.YTDSales,Y.YTDGP,L.PrevYTDSales,L.PrevYTDGP,P.PrevEOYSales,P.PrevEOYGP from #Tmp T
  Left Join #TmpYTD Y on Y.billtocust = T.[No_]
  Left Join #TmpLASTYTD L on L.billtocust = T.[No_]
  Left Join #TmpPrevious P on P.billtocust = T.[No_]

  Drop table #Tmp
  Drop table #TmpYTD
  Drop table #TmpLASTYTD
  Drop table #TmpPrevious

• rjp123

  SSC Enthusiast

  Points: 178

  More actions

  October 3, 2018 at 1:56 pm

  #2007947

  rjp123 - Wednesday, October 3, 2018 1:55 PM

  Michael L John - Wednesday, October 3, 2018 1:29 PM

  rjp123 - Wednesday, October 3, 2018 1:20 PM

  Lynn Pettis - Wednesday, October 3, 2018 11:18 AM

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
  declare @StartDate date = '01-01-2018'
     , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


  I Appreciate Lynn for taking a look, Actually I did it similarly by using temporary tables and then drop table at last.
  Still there is performance issue, Using without (Nolock) running query for long time can cause table lock and other process to stop correct?
  Please advise. I also attached query plan if that helps.

  Thanks

  Did you run Lynn's query?  Did it work, and did it work faster?

  Still have issues I did below.
  declare @StartDate date = '01-01-2018'
    , @EndDate date = '09-30-2018' ;

  SELECT  C.No_ as [No_],C.Name, C.Spcode,C.Address, C.City,C.[PostCode], C.County,D.dcValue into #Tmp
  FROM customer C
  LEFT JOIN vwCustdim D ON C.No_ = D.No_
  WHERE   (C.Dealer = 1) AND (C.MC = 1) OR (C.Dealer = 1) AND (C.FC = 1)

  Select billtocust,sum(SalesAmount) YTDSales,SUM(SalesAmount - ItemAVGCost * Quantity) YTDGP into #TmpYTD from vwSalesInvoice I Where I.ShipDate between @StartDate and @EndDate
  Group by billtocust

  Select billtocust,sum(SalesAmount) PrevYTDSales,SUM(SalesAmount - ItemAVGCost * Quantity) PrevYTDGP into #TmpLASTYTD from vwSalesInvoice I Where ShipDate between DATEADD(year, - 1, @StartDate) AND DATEADD(year, - 1, @EndDate)
  Group by billtocust

  Select billtocust,sum(SalesAmount) PrevEOYSales,SUM(SalesAmount - ItemAVGCost * Quantity) PrevEOYGP into #TmpPrevious from vwSalesInvoice I Where (YEAR(ShipDate) = YEAR(@StartDate) - 1)
  Group by billtocust

  Select T.*,Y.YTDSales,Y.YTDGP,L.PrevYTDSales,L.PrevYTDGP,P.PrevEOYSales,P.PrevEOYGP from #Tmp T
  Left Join #TmpYTD Y on Y.billtocust = T.[No_]
  Left Join #TmpLASTYTD L on L.billtocust = T.[No_]
  Left Join #TmpPrevious P on P.billtocust = T.[No_]

  Drop table #Tmp
  Drop table #TmpYTD
  Drop table #TmpLASTYTD
  Drop table #TmpPrevious

  Basically instead of using CTE i used Temporary table.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 3, 2018 at 2:01 pm

  #2007949

  Try adding the following index when running my query:
  
create index [ix_SalesInvoice_SalesComparison]
on [dbo].[SalesInvoice] ([ShipDate])
include
(
  [BCustNo]
, [SalesAmount]
, [AVGCst]
, [Qty]
);


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 3, 2018 at 2:03 pm

  #2007951

  Lynn Pettis - Wednesday, October 3, 2018 2:01 PM

  Try adding the following index when running my query:
  
create index [ix_SalesInvoice_SalesComparison]
on [dbo].[SalesInvoice] ([ShipDate])
include
(
  [BCustNo]
, [SalesAmount]
, [AVGCst]
, [Qty]
);


  You could also move the column BCustNo into the index after the ShipDate column, just not sure if that will boost performance or hinder it.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 3, 2018 at 2:08 pm

  #2007955

  So, tell us again what problem we are trying to solve?

• below86

  SSChampion

  Points: 11579

  More actions

  October 3, 2018 at 2:23 pm

  #2007959

  rjp123 - Wednesday, October 3, 2018 1:56 PM

  rjp123 - Wednesday, October 3, 2018 1:55 PM

  Michael L John - Wednesday, October 3, 2018 1:29 PM

  rjp123 - Wednesday, October 3, 2018 1:20 PM

  Lynn Pettis - Wednesday, October 3, 2018 11:18 AM

  The following is what I came up with based on your original post.  Please give it a try without adding the NOLOCK hint.
  
  declare @StartDate date = '01-01-2018'
     , @EndDate date = '09-30-2018' ;

  with CurrentYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [YTDSalesAmount] = sum([si].[SalesAmt])
, [YTDGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= @StartDate
and [si].[ShipDate] < dateadd(day,1,@EndDate)
group by
[si].[BCustNo]
), PreviousYearSales as (
select
[si].[BCustNo]
-- , [si].[ShipDate]
-- , [si].[SalesAmount]
-- , [si].[AVGCst]
-- , [si].[Qty]
, [PrevYTDSalesAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt]
            else 0
           end)
, [PrevYTDGPAmount] = sum(case when [si].[ShipDate] < dateadd(year,-1,dateadd(day,1,@EndDate))
            then [si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty])
            else 0
           end)
, [PrevEOYSalesAmount] = sum([si].[SalesAmt])
, [PrevEOYGPAmount] = sum([si].[SalesAmt] - ([si].[AVGCst] * [si].[Qty]))
from
[dbo].[SalesInvoice] as [si]
where
[si].[ShipDate] >= dateadd(year,-1,@StartDate)
and [si].[ShipDate] < @StartDate
group by
[si].[BCustNo]
)
select [C].[No_]
  , [C].[Name]
  , [C].[SP]
  , [C].[Address]
  , [C].[City]
  , [C].[CL]
  , [C].[PT]
  , [C].[Post Code]
  , [C].[County]
  , isnull([cys].[YTDSalesAmount],0)
  , isnull([cys].[YTDGPAmount],0)
  , isnull([pys].[PrevYTDSalesAmount],0)
  , isnull([pys].[PrevYTDGPAmount],0)
  , isnull([pys].[PrevEOYSalesAmount],0)
  , isnull([pys].[PrevEOYGPAmount],0)
from
[Customer] as [C]
left outer join [CustDim] as [D]
  on [C].[No_] = [D].[No_]
left outer join CurrentYearSales as [cys]
  on [C].[No_] = [cys].[BCustNo]
left outer join PreviousYearSales as [pys]
  on [C].[No_] = [pys].[BCustNo]
where
([C].[Dealer] = 1)
 and ([C].[MaCustomer] = 1)
or ([C].[Dealer] = 1)
  and ([C].[FC] = 1);


  I Appreciate Lynn for taking a look, Actually I did it similarly by using temporary tables and then drop table at last.
  Still there is performance issue, Using without (Nolock) running query for long time can cause table lock and other process to stop correct?
  Please advise. I also attached query plan if that helps.

  Thanks

  Did you run Lynn's query?  Did it work, and did it work faster?

  Still have issues I did below.
  declare @StartDate date = '01-01-2018'
    , @EndDate date = '09-30-2018' ;

  SELECT  C.No_ as [No_],C.Name, C.Spcode,C.Address, C.City,C.[PostCode], C.County,D.dcValue into #Tmp
  FROM customer C
  LEFT JOIN vwCustdim D ON C.No_ = D.No_
  WHERE   (C.Dealer = 1) AND (C.MC = 1) OR (C.Dealer = 1) AND (C.FC = 1)

  Select billtocust,sum(SalesAmount) YTDSales,SUM(SalesAmount - ItemAVGCost * Quantity) YTDGP into #TmpYTD from vwSalesInvoice I Where I.ShipDate between @StartDate and @EndDate
  Group by billtocust

  Select billtocust,sum(SalesAmount) PrevYTDSales,SUM(SalesAmount - ItemAVGCost * Quantity) PrevYTDGP into #TmpLASTYTD from vwSalesInvoice I Where ShipDate between DATEADD(year, - 1, @StartDate) AND DATEADD(year, - 1, @EndDate)
  Group by billtocust

  Select billtocust,sum(SalesAmount) PrevEOYSales,SUM(SalesAmount - ItemAVGCost * Quantity) PrevEOYGP into #TmpPrevious from vwSalesInvoice I Where (YEAR(ShipDate) = YEAR(@StartDate) - 1)
  Group by billtocust

  Select T.*,Y.YTDSales,Y.YTDGP,L.PrevYTDSales,L.PrevYTDGP,P.PrevEOYSales,P.PrevEOYGP from #Tmp T
  Left Join #TmpYTD Y on Y.billtocust = T.[No_]
  Left Join #TmpLASTYTD L on L.billtocust = T.[No_]
  Left Join #TmpPrevious P on P.billtocust = T.[No_]

  Drop table #Tmp
  Drop table #TmpYTD
  Drop table #TmpLASTYTD
  Drop table #TmpPrevious

  Basically instead of using CTE i used Temporary table.

  And how did each one of these perform?  Are they all long running?  Or is it just the final join?
  I could assume the last temp table was slow, try this instead:
  SELECT billtocust,
    SUM(SalesAmount) AS PrevEOYSales,
    SUM(SalesAmount - ItemAVGCost * Quantity) AS PrevEOYGP
INTO #TmpPrevious
FROM vwSalesInvoice AS I
WHERE ShipDate BETWEEN DATEADD(YY, -1, @StartDate) AND DATEADD(DD, -1, @StartDate)
GROUP BY billtocust
;

  -------------------------------------------------------------
  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 3, 2018 at 2:24 pm

  #2007960

  Lynn Pettis - Wednesday, October 3, 2018 2:08 PM

  So, tell us again what problem we are trying to solve?

  Hi Lynn, 
  Its about performance, checking what is the best possible way to achieve this.
  its around 2M row I am trying to deal with, how long ideally it should take?
  To query 9 months of data @startdate and @Enddate for 1787 rows it is taking 4 min 9 sec to return results to aggregate and do above calculations. 
  sometimes if I run again its little faster but its too long time !!!