Does anyone want to help optimize my query code?

[ggranger007]

Both of these fields are functional, but due to the massive amount of data I am working with, it literally takes almost an hour to return data on my work computer! Anything I can do to reduce the number of calculations would be appreciated.

Field #1:

Acceptance to Receipt: Int(IIf(Min([Receipt Data].[Receipt Date]-[Acceptance Data].[Acceptance Date])<0,Null,Min([Receipt Data].[Receipt Date]-[Acceptance Data].[Acceptance Date])))

This first field is used to calculate the difference between the time services are rendered versus when they are actually received in our system. If the value is a negative value, it returns nothing. Else it runs the calculation again.

I use a nearly identical calculation in another field, so changes here will affect doubly.

Field #2:

Request to Receipt: IIf(Min(X-Y)<0,Null,Min((5*((X-Y)\7))+IIf(Weekday(X)=7,IIf(Weekday(Y)=1,5,7-Weekday(Y)),IIf(Weekday(Y)=1,Weekday(X)-1,IIf(Weekday(X)>=Weekday(Y),1+Weekday(X)-Weekday(Y),6+Weekday(X)-Weekday(Y)))-1)))

This nasty bugger is probably responsible for slowing everything down. This first portion tests to see if the value is negative and returns a null value. Else if it is not, it calculates the difference between the receipt date and the time it takes for the receiving form to be sent, minus weekends.

I tried to call this from a function, but was unsuccessful.

Any help would be appreciated!

 

[SydneyGeek]

Maybe a change of strategy?

You have 2 tables, presumably linked on ReceiptID. Create the query without the expressions. Group on RecieptID and Acceptance Date, select Min([Receipt Date]) and push into a new table. It could still take some time but I wouldn't think it would take an hour.

Then query on the new table, which will be much quicker than doing it in the first query.

Other things to consider: If the join is not on an indexed field like an ID, it should be. Numbers are much more efficient than text. I have seen 20-fold increases in performance by joining on indexed fields instead of text fields.

Denis