MSAccess query taking too long to execute.

Question

Hi,

I have Access 2003 with SQL backend.

This query takes 10 minutes to execute and if I take out the Exists part it takes 10 sec.

I can't put it in SQL as a passthrough query because I need it in a Access subreport.

Any ideas how to improve execution time??

SELECT DailyLeaveLedger.dldEmployeeID, 
       Sum(IIf([tolAnnualLeaveType]<>0,1,0)) AS ALDays, 
       Sum(IIf([tolPersonalLeaveType]<>0,1,0)) AS PLDays, 
       Sum(IIf([tolPublicHolidayType]<>0,1,0)) AS PHDays, 
       DailyLeaveLedger.dldPHID

FROM (DailyLeaveLedger 
      INNER JOIN Employees ON DailyLeaveLedger.dldEmployeeID = Employees.EmployeeID)   
      INNER JOIN TypeOfLesterLeave ON DailyLeaveLedger.dldLeaveType = TypeOfLesterLeave.tolID

WHERE (((DailyLeaveLedger.dldAuthDecline)=1) AND 
       ((DailyLeaveLedger.dldAuthorisedBy) Is Not Null) AND 
       ((DailyLeaveLedger.dldReleaseToPayroll)=True) AND 
       ((IIf([forms]![TransferTBRToPayrollForm]![chkOnlyPayLeave]=True,
                Exists (SELECT DISTINCT InvoiceHeader.InvDate 
                        FROM (InvoiceHeader 
                         INNER JOIN ReceiptDetail ON InvoiceHeader.InvNumber = ReceiptDetail.RDInvNumber) 
                         INNER JOIN [Work Codes] ON ReceiptDetail.RDWorkCodeID = [Work Codes].WorkCodeID 
                        WHERE ((ReceiptDetail.RDPayRun=0 Or 
                                ReceiptDetail.RDPayRun Is Null) AND 
                               (ReceiptDetail.RDRctToPayrollFlag=0) AND 
                               ([Work Codes].WorkCodePayrollAccount1<>0) AND 
                               (InvoiceHeader.EmployeeID = DailyLeaveLedger.dldEmployeeID) AND 
                              (DailyLeaveLedger.dldLeaveDate > DateAdd("ww", -InvoiceHeader.InvWeeksOfPay, InvoiceHeader.InvBeginDate)) AND 
                              (DailyLeaveLedger.dldLeaveDate <= InvoiceHeader.InvBeginDate)))
             ,True))=True))
GROUP BY DailyLeaveLedger.dldEmployeeID, DailyLeaveLedger.dldPHID
HAVING (((DailyLeaveLedger.dldPHID) Is Null));

Answer 1

Let's do this a little at a time. I'll update the answer as I find more:

First the easy one, get rid of the iif statements in the select as follows.

Replace

SELECT DailyLeaveLedger.dldEmployeeID, 
       Sum(IIf([tolAnnualLeaveType]<>0,1,0)) AS ALDays, 
       Sum(IIf([tolPersonalLeaveType]<>0,1,0)) AS PLDays, 
       Sum(IIf([tolPublicHolidayType]<>0,1,0)) AS PHDays, 
       DailyLeaveLedger.dldPHID

with

SELECT DailyLeaveLedger.dldEmployeeID, 
       Sum([tolAnnualLeaveType] *-1) AS ALDays, 
       Sum([tolPersonalLeaveType] *-1) AS PLDays, 
       Sum([tolPublicHolidayType] *-1) AS PHDays, 
       DailyLeaveLedger.dldPHID

This will give you minimum improvement. Once I untangle the rest I'll give you some more impactful ones.

The rest of this is such an unholy mess, I'm giving up on trying to unravel it, especially given your low answer accept rate. However, let me give you some guidance about what you need to do in a general sense instead of exact code.

Essentially what you need to do is pull this part into the joins on the main query instead of using a subquery.

(IIf([forms]![TransferTBRToPayrollForm]![chkOnlyPayLeave]=True,
                Exists (SELECT DISTINCT InvoiceHeader.InvDate 
                        FROM (InvoiceHeader 
                            INNER JOIN ReceiptDetail ON InvoiceHeader.InvNumber = ReceiptDetail.RDInvNumber) 
                            INNER JOIN [Work Codes] ON ReceiptDetail.RDWorkCodeID = [Work Codes].WorkCodeID 
                        WHERE ((ReceiptDetail.RDPayRun=0 Or ReceiptDetail.RDPayRun Is Null) AND 
                               (ReceiptDetail.RDRctToPayrollFlag=0) AND 
                               ([Work Codes].WorkCodePayrollAccount1<>0) AND 
                              (InvoiceHeader.EmployeeID = DailyLeaveLedger.dldEmployeeID) AND 
                              (DailyLeaveLedger.dldLeaveDate > DateAdd("ww", -InvoiceHeader.InvWeeksOfPay, InvoiceHeader.InvBeginDate)) AND 
                              (DailyLeaveLedger.dldLeaveDate <= InvoiceHeader.InvBeginDate)))
             ,True))=True))

Does that make sense? I'm frankly a little worried you are going to be able to pull this off if you aren't understanding why it is complaining about changing this to a pass-through. It has nothing to do with it being called from a sub-report and more to do with the fact that you are using VBA functions.

Answer 2

Please give us the formatted query for the better understanding.. I have gone through the query and found the reason may be due to EXIST statement where you are using Distinct clause for InvoiceHeader.InvDate

i dont know your exact scenario if you are using Exist clause only for record existance then use count(InvoiceHeader.InvDate) in place of distinct InvoiceHeader.InvDate i think then performance will increase..

Answer 3

Not sure about what Access is doing underneath, but subreports are going to execute your query for each record in the main report, so I would take this query and dump it to a table before running the report. Then use the table for the subreport recordsource.

I've used this in a quarterly statement report that had several sub reports and it cut the time down from several hours to under 20 minutes.