# Does anyone want to help optimize my query code?

#### ggranger007

##### Board Regular
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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

Replies
0
Views
416
Replies
7
Views
976
Replies
1
Views
474
Replies
0
Views
350
Replies
1
Views
2K

1,221,418
Messages
6,159,793
Members
451,589
Latest member
Harold14

### 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.

### Which adblocker are you using?

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

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