Results 1 to 2 of 2
  1. #1
    eagerlearner is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    3

    Unhappy Query Runs Extremely Slow

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It WILL be slow if you are doing many calculations in a query.
    instead, try writing the data to a 'report' table, then update fields with calcs that way.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Missing records when query runs
    By SydB in forum Queries
    Replies: 6
    Last Post: 06-28-2014, 01:43 PM
  2. Replies: 2
    Last Post: 03-28-2014, 11:25 AM
  3. Date() in query runs extremely slow on Windows 7
    By TagYoureIt in forum Access
    Replies: 4
    Last Post: 03-20-2013, 01:24 PM
  4. Query runs fine but report doesnt work
    By endri81 in forum Queries
    Replies: 4
    Last Post: 04-28-2012, 02:35 PM
  5. Form that runs query and creates reports
    By Valeda in forum Forms
    Replies: 2
    Last Post: 05-04-2006, 07:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums