I absolutely agree that there are probably opportunities to better tune the queries. Part of the problem is that I am a decent .NET programmer but a total newb when it comes to SQL and so was the guy that wrote all the queries I just inherited, which means he may have written them poorly but I'm not skilled enough yet to know how to improve them. Any chance I can share a query with any of you guys and get some pointers on how to improve it? Here it is if anybody wants to take a crack at it. The subquery takes 5 minutes and 25 seconds while the entire query (oddly enough) takes 5 minutes and 21 seconds.
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
RcvDtl RD
join PORel POR on RD.PONum = POR.PONum and RD.POLine = POR.POLine and RD.PORelNum = POR.PORelNum
join Vendor V on RD.VendorNum = V.VendorNum
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 dbo.AGM_isdatenull(RD.ReceiptDate) = 0 and dbo.AGM_isdatenull(POR.DueDate) = 0 and cast(RD.ReceiptDate as date) <> cast( GETDATE() as date)
and RD.InspectionPending = 0 and RD.ReceiptDate >= @StartDate and RD.ReceiptDate <= @EndDate
and 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