Upgraded from 2008 to 2014, queries slowed to a crawl

• bwhiteside

  Valued Member

  Points: 54

  More actions

  September 4, 2015 at 10:29 am

  #1824738

  Here is the new query, if you are interested in the changes I made:

  Declare @BuyerName as nvarchar(50);

  Declare @StartDate as date;

  Declare @EndDate as date;

  Set @BuyerName = '' + '%';

  Set @StartDate = '01/01/2014';

  Set @EndDate = '12/31/2014';

  select

  T.VendorID

  ,T.Name

  ,COUNT(T.name) as NumofRel

  ,SUM(T.relvalue) as RelValue

  ,cast(cast((COUNT(T.name) -SUM(T.NumOfLateDue))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as OnTimeScoreD

  ,cast(cast((COUNT(T.name) -SUM(T.NumOfLateProm))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as OnTimeScoreP

  ,SUM(T.NumOfLateDue) as NumofLateDue

  ,SUM(T.NumOfLateProm) as NumofLateProm

  ,AVG(T.DaysLateDue) as AvgDaysLateDue

  ,AVG(T.DaysLateProm) as AvgDaysLateProm

  ,cast(cast((COUNT(T.name) -SUM(T.InspectionFailed))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as QualityScore

  ,sum(T.InspectionFailed) as NumOfRejects

  ,SUM(T.RejectValue) as RejectValue

  from (

  select

  V.VendorID

  ,V.Name

  ,PA.Name as Buyer

  ,(RD.OurQty*RD.OurUnitCost) as RelValue

  ,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateProm

  ,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateDue

  ,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END )as NumOfLateProm

  ,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END ) as NumOfLateDue

  ,(RD.FailedQty*RD.OurUnitCost*(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END)) as RejectValue

  ,(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END )AS InspectionFailed

  from

  Vendor V

  join RcvDtl RD on RD.VendorNum = V.VendorNum and RD.ReceiptDate is not null and cast(RD.ReceiptDate as date) <> cast( GETDATE() as date) and RD.InspectionPending = 0 and RD.ReceiptDate >= @StartDate and RD.ReceiptDate <= @EndDate

  join PORel POR on RD.PONum = POR.PONum and RD.POLine = POR.POLine and RD.PORelNum = POR.PORelNum and POR.DueDate is not null

  join POHeader POH on POR.PONum = POH.PONum

  join Erp.PurAgent PA on PA.BuyerID = POH.BuyerID

  left outer join PartTran PT on PT.PONum = RD.PONum and PT.POLine = RD.POLine and PT.PORelNum = RD.PORelNum and PT.PackSlip = RD.PackSlip and PT.PackLine = RD.PackLine and PT.TranType = 'INS-DMR'

  left outer join Ice.UD14 on UD14.Key1 = PT.DMRNum and UD14.Key5 = '1'

  where 1 = CASE WHEN @BuyerName = '%' THEN 1 ELSE CASE WHEN PA.name like @buyername THEN 1 ELSE 0 END END

  ) as T

  group by T.VendorID, T.Name

  order by T.VendorID