Age Query

[ZMAN2]

Need for speed - Aging Query

I have a database that calculates the workdays between 2 dates and drops the result in the appropriate aging bucket, but it is very slow. The query is looking at around 300K+ records.......Any ideas to speed up the process would be appreciated. (See attached)

 

[ZMAN2]

Anybody out there???????

Could it be my PC, if code is OK? This query takes several hours to run. I didn't think that 300 to 400 K records is that big of a deal to analyze. Thanks again.

 

[llkhoutx]

The multiplie "iif" statements if and join is what makes your query "slow." I trued in on a 400 MHz PC an it took about six (6) seconds to run.

 

[ZMAN2]

Still runs very slow!

I removed the multiply if statements and the problem seems to be isolated to calculating the work days. I ran it yesterday for 400K records and the query took over 8 hours to run.

Any help would be greatly appreciated

 

[llkhoutx]

Dcount is notoriously slow.

I think the Dcount is probably the time grabber. Generate a test cal to see if that's the problem and build a work around. A recordset count might be singnificantly faster. Years ago I generated a DAO recordset search to compare against a dlookup and the recordset was substantially faster.

 

[Jon K]

As pointed out by llkhoutx, IIFs are slow and DCount is a time grabber.

Since your IIFs are used to return only 1 or 0, you can change them into numeric expressions as they run much faster. You can also replace DCount with Select Count("*") as it can run significantly faster. You can optimise Select Count("*") by indexing the dtObservedDate field in table tblHolidays.


To further reduce processing time, you can include the Saturdays and Sundays in tblHolidays and thereby get rid of the need of the function funWorkDaysDifference().

For testing, I added the Saturdays and Sundays of 2004 to tblHolidays in the attached database and indexed the field dtObservedDate. I created 8000 records for 2004 in tblMaster and built a make-table query as follows, calculating the number of Work Days directly in the query:-

SELECT [Field1], [Date1], [Date2], 
[b][date2]-[date1]+1-(Select Count("*") from tblHolidays where dtObservedDate
between  [date1] and [date2]) AS [Work Days], [/b]
-([Work Days]<=0) AS [Current], 
-([Work Days]=1) AS Age1, 
-([Work Days]=2) AS Age2, 
-([Work Days]>2) AS [>Age2] INTO tblAging
FROM tblMaster;

The query took roughly 5.5 seconds to create table tblAging for the 8000 records. Hence for 400K records, this approach would take only 5 minutes to run on my system.