Hi,
We've been discussing this in a different area of the forum and the issue has been resolved so I wanted to come post the solution here in case anyone stumbles upon this thread and has a similar problem.
The trick is to try to putting limiting conditionals in the joins rather than the where clause when possible. In doing so I got this query, which was taking more than five minutes to run, down to zero seconds. To confirm that the result set is the same for both queries, I took the results from both, put them each in their own Excel worksheet, compared the worksheets and they were exactly the same. Its amazing that both queries (zero seconds vs. 5 minutes & 22 seconds) return the exact same dataset, but one takes sooooo much longer. Just goes to show how important it is to write your queries properly.
Here is the new query if anyone wants to compare the two:
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