Does anyone want to help optimize my query code?

ggranger007

Board Regular
Joined
Jan 21, 2007
Messages
107
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:
Code:
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:
Code:
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,181,649
Messages
5,931,216
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top