Hi all, I have a database which is doing all thecalculations and producing the tabular and graphical results as needed.However, it takes too long to complete calculations and produce the reports.Three queries which do calculations are taking 99% of the time to run. Pleasesee attached chart showing the time performance as we add number of purchase orders.With 320 purchase orders, three queries like one shown below taking combined116 minutes to run. Which is way too long.
Text highlighted in red are the VBA based functions to dodesired calculation. At the end of query, I have mentioned how many times eachfunction is called in.
Your support will be highly appreciated.
SELECT [02tblPeriods].ID, [02tblPeriods].Period, Sum(JO1OOEPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1OOEPP, Sum(JO1OOLPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1OOLPP, Sum(JO1OCEPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1OCEPP, Sum(JO1OCLPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1OCLPP, Sum(JO1COEPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1COEPP, Sum(JO1COLPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1COLPP, Sum(JO1CCEPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1CCEPP, Sum(JO1CCLPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1CCLPP,Val((Val([JO1OOEPP])+Val([JO1OOLPP]))/2) AS JO1OOPP,Val((Val([JO1OCEPP])+Val([JO1OCLPP]))/2) AS JO1OCPP,Val((Val([JO1COEPP])+Val([JO1COLPP]))/2) AS JO1COPP, Val((Val([JO1CCEPP])+Val([JO1CCLPP]))/2)AS JO1CCPP, IIf((JOOwnerOPOs(1)+JOContOPOs(1))>0,IIf((JOBudgetWeight('Contractor','Original',1)+JOBudgetWeight('Owner','Original',1))>0,(Val(Val([JO1OOPP])*JOBudgetWeight('Owner','Original',1))+Val(Val([JO1COPP])*JOBudgetWeight('Contractor','Original',1)))/(JOBudgetWeight('Owner','Original',1)+JOBudgetWeight('Contractor' ,'Original',1)),0),0)AS JO1OTPP, IIf((JOOwnerCPOs(1)+JOContCPOs(1))>0,IIf((JOBudgetWeight('Contractor','Current',1)+JOBudgetWeight('Owner','Current',1))>0,(Val(Val([JO1OCPP])*JOBudgetWeight('Owner','Current',1))+Val(Val([JO1CCPP])*JOBudgetWeight('Contractor','Current',1)))/(JOBudgetWeight('Owner','Current',1)+JOBudgetWeight('Contractor','Current',1)),0),0) ASJO1CTPP, Sum(JO1OwnerActualPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1OWNACTUALPP, Sum(JO1ContractorActualPeriodProgress([JO-Sr],[Purchaser],[ScheduleType],[Date Type],[Milestone])) AS JO1CONTACTUALPP, IIf((JOOwnerOPOs(1)+JOContOPOs(1))>0,IIf((JOBudgetWeight('Contractor','Original',1)+JOBudgetWeight('Owner','Original',1))>0,(Val(Val([JO1OOEPP])*JOBudgetWeight('Owner','Original',1))+Val(Val([JO1COEPP])*JOBudgetWeight('Contractor','Original',1)))/(JOBudgetWeight('Owner','Original',1)+JOBudgetWeight('Contractor','Original',1)),0),0) ASJO1OTEPP, IIf((JOOwnerCPOs(1)+JOContCPOs(1))>0,IIf((JOBudgetWeight('Contractor','Current',1)+JOBudgetWeight('Owner','Current',1))>0,(Val(Val([JO1OCEPP])*JOBudgetWeight('Owner','Current',1))+Val(Val([JO1CCEPP])*JOBudgetWeight('Contractor','Current',1)))/(JOBudgetWeight('Owner','Current',1)+JOBudgetWeight('Contractor','Current',1)),0),0) ASJO1CTEPP, IIf((JOOwnerCPOs(1)+JOContCPOs(1))>0,IIf((JOBudgetWeight('Owner','Current',1)+JOBudgetWeight('Contractor','Current',1))>0,(Val(Val([JO1OWNACTUALPP])*JOBudgetWeight('Owner','Current',1))+Val(Val([JO1CONTACTUALPP])*JOBudgetWeight('Contractor','Current',1)))/(JOBudgetWeight('Owner','Current',1)+JOBudgetWeight('Contractor','Current',1)),0),0) ASJO1ACTUALPP
FROM 00tblJOInfo RIGHT JOIN (01tblPurchaseOrders RIGHT JOIN(02tblPeriods LEFT JOIN 03tblDates ON [02tblPeriods].Period =[03tblDates].[Milestone Date]) ON [01tblPurchaseOrders].PONumber =[03tblDates].PONumber) ON [00tblJOInfo].[JO-Number] = [01tblPurchaseOrders].JONumber
GROUP BY [02tblPeriods].ID, [02tblPeriods].Period;
JO1OOEPeriodProgress – called onetime in query
JO1OOLPeriodProgress – called onetime in query
JO1OCEPeriodProgress – called onetime in query
JO1OCLPeriodProgress – called onetime in query
JO1COEPeriodProgress – called onetime in query
JO1COLPeriodProgress – called onetime in query
JO1CCEPeriodProgress – called onetime in query
JO1CCLPeriodProgress – called onetime in query
JOOwnerOPOs – called three timesin query
JOContOPOs – called three timesin query
JOBudgetWeight – called twentynine times in query
JOOwnerCPOs – called three timesin query
JOContCPOs – called three timesin query
JO1OwnerActualPeriodProgress - calledone time in query
JO1ContractorActualPeriodProgress– called one time in query