Query Taking Too Long!

[hhzyousafi]

I have written the following query and it is taking too long to respond:

SELECT DISTINCT (AssignedTo) AS EmployeeName, Workdays(Forms![frmDashboardReports]!txtStartDateAndTime,Forms![frmDashboardReports]!txtEndDateAndTime) AS ScheduledWorkdays, IIf(IsNull(ScheduledWorkdays),0,Round(ScheduledWorkDays*8,2)) AS ScheduledCapacity, IIf(IsNull((SELECT SUM(NumericValue) FROM qryAbsenteeismAndLeaves WHERE EmployeeName = Tasks.AssignedTo AND AttendanceDate >= Forms![frmDashboardReports]!txtStartDateAndTime AND AttendanceDate <= Forms![frmDashboardReports]!txtEndDateAndTime AND NumericValueType = "Addition")),0,(SELECT SUM(NumericValue) FROM qryAbsenteeismAndLeaves WHERE EmployeeName = Tasks.AssignedTo AND AttendanceDate >= Forms![frmDashboardReports]!txtStartDateAndTime AND AttendanceDate <= Forms![frmDashboardReports]!txtEndDateAndTime AND NumericValueType = "Addition")) AS ActualWorkdays, IIf(IsNull([ActualWorkdays]),0,ROUND(ActualWorkdays*8,2)) AS ActualCapacity, (SELECT SUM(ExpectedManHours) FROM tblTasks WHERE AssignedTo = Tasks.AssignedTo AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime AND (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ExpectedManHours, (SELECT SUM(ActualManHours) FROM tblTasks WHERE AssignedTo = Tasks.AssignedTo AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime AND (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ActualManHours, IIf([ExpectedManHours]=0,(0 & "%"),Round((ActualManHours/ExpectedManHours)*100,2) & "%") AS ManHourUtilization, (SELECT SUM(ExpectedCost) FROM tblTasks WHERE AssignedTo = Tasks.AssignedTo AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime AND (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ExpectedCost, (SELECT SUM(ActualCost) FROM tblTasks WHERE AssignedTo = Tasks.AssignedTo AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime AND (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ActualCost, IIf([ExpectedCost]=0,(0 & "%"),Round((ActualCost/ExpectedCost)*100,2) & "%") AS CostUtilization
FROM tblTasks AS Tasks
WHERE ([Department] Like "*" & Forms!frmDashboardReports!cboDepartment & "*") And ((([ExpectedCompletionDateAndTime])>=Forms![frmDashboardReports]!txtStartDateAndTime And ([ExpectedCompletionDateAndTime])<=Forms![frmDashboardReports]!txtEndDateAndTime));​

I have tried running the query several times and for one reason or another it just never responds. It doesn't yield any results. The query is doing the following:

• Calculating the number of workdays an employee has worked based on a function. The report is run when a "Start Date" and "End Date" are entered.
• Calculate the actual number of working days for each employee based on their attendance which can be obtained from another query (i.e. qryAttendance).
• Calculate the total number of expected man hours, actual man hours, expected cost, and actual cost based data entered in the task table (i.e. tblTasks).

Can someone please help me with this? What have I done wrong? The query simply does not respond. The "Running Query" status bar on the bottom right of the main Access window shows that the query is running but it never stops running. I have left the query running for more than an hour and there was no result.

 

[pr2-eugin]

Divide & Conquer ! The best strategy.

 

[namliam]

You are using multiple inline subselects, which is WORSE THAN BAD for any database.

You should start by formatting any code you throw on a forum and using the code wraps...

SELECT DISTINCT 
       (AssignedTo) AS EmployeeName
     , Workdays(Forms![frmDashboardReports]!txtStartDateAndTime,Forms![frmDashboardReports]!txtEndDateAndTime) AS ScheduledWorkdays
     , IIf(IsNull(ScheduledWorkdays),0,Round(ScheduledWor kDays*8,2)) AS ScheduledCapacity
     , IIf(IsNull((SELECT SUM(NumericValue) 
                   FROM qryAbsenteeismAndLeaves 
                   WHERE EmployeeName = Tasks.AssignedTo 
                     AND AttendanceDate >= Forms![frmDashboardReports]!txtStartDateAndTime 
                     AND AttendanceDate <= Forms![frmDashboardReports]!txtEndDateAndTime 
                     AND NumericValueType = "Addition"))
                  ,0
                  ,(SELECT SUM(NumericValue) 
                    FROM qryAbsenteeismAndLeaves 
                    WHERE EmployeeName = Tasks.AssignedTo 
                      AND AttendanceDate >= Forms![frmDashboardReports]!txtStartDateAndTime 
                      AND AttendanceDate <= Forms![frmDashboardReports]!txtEndDateAndTime 
                      AND NumericValueType = "Addition")) AS ActualWorkdays
     , IIf(IsNull([ActualWorkdays]),0
                                   ,ROUND(ActualWorkdays*8,2)) AS ActualCapacity
     , (SELECT SUM(ExpectedManHours) 
        FROM tblTasks 
        WHERE AssignedTo = Tasks.AssignedTo 
          AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime 
          AND   (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ExpectedManHours
     , (SELECT SUM(ActualManHours) 
        FROM tblTasks 
        WHERE AssignedTo = Tasks.AssignedTo 
          AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime 
          AND   (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ActualManHours
     , IIf([ExpectedManHours]=0,(0 & "%")
                               ,Round((ActualManHours/ExpectedManHours)*100,2) & "%") AS ManHourUtilization
     , (SELECT SUM(ExpectedCost) 
        FROM tblTasks 
        WHERE AssignedTo = Tasks.AssignedTo 
          AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime 
          AND   (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ExpectedCost
     , (SELECT SUM(ActualCost) 
        FROM tblTasks 
        WHERE AssignedTo = Tasks.AssignedTo 
          AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime 
          AND   (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ActualCost
     , IIf([ExpectedCost]=0,(0 & "%")
                           ,Round((ActualCost/ExpectedCost)*100,2) & "%") AS CostUtilization
FROM tblTasks AS Tasks
WHERE   ([Department] Like "*" & Forms!frmDashboardReports!cboDepartment & "*") 
  And ((([ExpectedCompletionDateAndTime])>=Forms![frmDashboardReports]!txtStartDateAndTime 
  And   ([ExpectedCompletionDateAndTime])<=Forms![frmDashboardReports]!txtEndDateAndTime));

Your understanding of SQL seems to be somewhat limitted and/or wrong. as well as the design of this database, the "Assigned to" in this table tblTasks should never hold the actual name of any employee.....

Your query where you are (re)using tblTasks can for sure be shortened a lot, from something like:

SELECT DISTINCT 
       (AssignedTo) AS EmployeeName
     , (SELECT SUM(ActualCost) 
        FROM tblTasks 
        WHERE AssignedTo = Tasks.AssignedTo 
          AND (((ExpectedCompletionDateAndTime)>=Forms![frmDashboardReports]!txtStartDateAndTime 
          AND   (ExpectedCompletionDateAndTime)<=Forms![frmDashboardReports]!txtEndDateAndTime))) AS ActualCost
FROM tblTasks AS Tasks
WHERE   ([Department] Like "*" & Forms!frmDashboardReports!cboDepartment & "*") 
  And ((([ExpectedCompletionDateAndTime])>=Forms![frmDashboardReports]!txtStartDateAndTime 
  And   ([ExpectedCompletionDateAndTime])<=Forms![frmDashboardReports]!txtEndDateAndTime));

To something like

SELECT 
       (AssignedTo) AS EmployeeName
     , SUM(ActualCost) AS ActualCost
FROM tblTasks AS Tasks
WHERE   ([Department] Like "*" & Forms!frmDashboardReports!cboDepartment & "*") 
  And ((([ExpectedCompletionDateAndTime])>=Forms![frmDashboardReports]!txtStartDateAndTime 
  And   ([ExpectedCompletionDateAndTime])<=Forms![frmDashboardReports]!txtEndDateAndTime))
Group by AssignedTo;

This will hugely increase already the performace of your query

Any other query you use you should join in the From not utilize a subquery for this.... Using the queries as you do right now, is a VERY rare thing.... Sometimes its actually needed, but most times there are better/faster solutions to it.

 

[namliam]

Divide & Conquer ! The best strategy.

I do much prefer Command & Conquer