If statements are making query very slow

[Guest]

It seems the general consensus is that Iif statements aren't very efficient.
I'm having a hard time, though, trying to figure out what to use instead.
I've seen some posts on Switch, but I'm still uncertain how to use Switch.
Can someone enlighten me please? I have several Iif in an append query and
it is taking about 45 minutes to run. (took the same amt. of time when it
was a make table query)

Thanks you so much!

A couple of examples of my Iif's statements are:
SystemDoc:
nz(IIf([ST]="6","",IIf([M_B]="A",dhSubtractWorkDaysA([LTA1],[LaunchDate]),IIf([M_B]="W",dhSubtractWorkDaysA([LTW2],[MatDueDateCalc]),""))))

and

MCS_TP: IIf([ST]<>"6",[MCSOne],Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartOne] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartTwo] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartThree] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartFour] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartFive] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSix] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSeven] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartEight] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartNine] & "' AND [ST] = '1'"),[MCSOne]))))))))))

 

[John Spencer (MVP)]

IIF and SWITCH probably will have roughly the same impact on performance.
HOWEVER, the impact depends on WHERE you are using the IIF (or Switch). If used
in a where clause or order by clause the impact is probably going to be more
significant, since no indexes can be used to speed up these operations. IIF
statements are native to SQL, while Switch is a VBA function - there is a VBA
IIF also.

SWITCH is generally easier to read (as a human) than multiple (nested) IIF
statements. As far as your example goes, the DLookUp function is probably
affecting the query performance more than the IIF function.

 

[Guest]

Thanks John. Is it unheard of for a query to take 45 minutes? I feel like
I"m writing the whole database incorrectly.

IIF and SWITCH probably will have roughly the same impact on performance.
HOWEVER, the impact depends on WHERE you are using the IIF (or Switch). If used
in a where clause or order by clause the impact is probably going to be more
significant, since no indexes can be used to speed up these operations. IIF
statements are native to SQL, while Switch is a VBA function - there is a VBA
IIF also.

SWITCH is generally easier to read (as a human) than multiple (nested) IIF
statements. As far as your example goes, the DLookUp function is probably
affecting the query performance more than the IIF function.

It seems the general consensus is that Iif statements aren't very efficient.
I'm having a hard time, though, trying to figure out what to use instead.
I've seen some posts on Switch, but I'm still uncertain how to use Switch.
Can someone enlighten me please? I have several Iif in an append query and
it is taking about 45 minutes to run. (took the same amt. of time when it
was a make table query)

Thanks you so much!

A couple of examples of my Iif's statements are:
SystemDoc:
nz(IIf([ST]="6","",IIf([M_B]="A",dhSubtractWorkDaysA([LTA1],[LaunchDate]),IIf([M_B]="W",dhSubtractWorkDaysA([LTW2],[MatDueDateCalc]),""))))

and

MCS_TP: IIf([ST]<>"6",[MCSOne],Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartOne] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartTwo] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartThree] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartFour] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartFive] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSix] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSeven] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartEight] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartNine] & "' AND [ST] = '1'"),[MCSOne]))))))))))

 

[tina]

using a domain aggregate function, such as DLookup(), is a lot like running
a query that returns one value. in your query, you're running the each
DLookup repeatedly - once for each record that the query returns. if your
query is appending multiple records at one time, that's a lot of function
executions going on.

can you, instead, write some queries that return the values for the group of
records you're ultimately going to append? then build your append query by
linking those "base" queries. that may prove quicker than finding and
assigning each value record-by-record.

hth

Thanks John. Is it unheard of for a query to take 45 minutes? I feel like
I"m writing the whole database incorrectly.

IIF and SWITCH probably will have roughly the same impact on performance.
HOWEVER, the impact depends on WHERE you are using the IIF (or Switch). If used
in a where clause or order by clause the impact is probably going to be more
significant, since no indexes can be used to speed up these operations. IIF
statements are native to SQL, while Switch is a VBA function - there is a VBA
IIF also.

SWITCH is generally easier to read (as a human) than multiple (nested) IIF
statements. As far as your example goes, the DLookUp function is probably
affecting the query performance more than the IIF function.

nz(IIf([ST]="6","",IIf([M_B]="A",dhSubtractWorkDaysA([LTA1],[LaunchDate]),II
f([M_B]="W",dhSubtractWorkDaysA([LTW2],[MatDueDateCalc]),""))))

and

MCS_TP:

'" & [PartOne] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartTwo] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartThree] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartFour] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartFive] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSix] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSeven] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartEight] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartNine] & "' AND [ST] = '1'"),[MCSOne]))))))))))

 

[John Spencer (MVP)]

45 minutes is a long time for a query to run. It's hard to tell if it is
unexceptable since I don't know if you are running this over a network or how
many records are involved or even how frequently you need to run the query.

I would say that if you need to do a lot of calculations with IIF (Switch) and
with the domain aggregate functions (DLookup) that you may have a design problem
with your table structure. NOTE: "MAY have" not "DO have".

Using SWITCH for example, you can rewrite the SystemDoc column to

SWITCH([M_B]="A",dhSubtractWorkDaysA([LTA1],[LaunchDate]),
[M_B]="W",dhSubtractWorkDaysA([LTW2],[MatDueDateCalc]),
True,"")

I see that this has a function in it to get number of work days(?). This will
slow things down also. Especially if you have applied criteria against the
column as Access will probably calculate the value for every row in the table so
it can apply the criteria (in the where clause).

I don't understand why you are returning a string value instead of Null,
especially since I would expect dhSubtractWorkDaysA to return a numeric value
which this expression then forces into a string value.

And MCS_TP is really, really hard to follow - as a matter of fact, I am not even
sure what it is doing. I see you are potentially doing 9 DLookup calls in one
column. You would probably be better off writing a VBA function to accomplish
what you are doing here. Especially if you can find a way to run one query to
get back a result set and then extract what you need from that. I can't say
that for sure, you would have to test things to find out.

MCS_TP:
IIf([ST]<>"6",[MCSOne],
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartOne] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartTwo] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartThree] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartFour] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartFive] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSix] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSeven] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartEight] & "' AND [ST] = '1'"),
Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartNine] & "' AND [ST] = '1'"),
[MCSOne]))))))))))

Thanks John. Is it unheard of for a query to take 45 minutes? I feel like
I"m writing the whole database incorrectly.

IIF and SWITCH probably will have roughly the same impact on performance.
HOWEVER, the impact depends on WHERE you are using the IIF (or Switch). If used
in a where clause or order by clause the impact is probably going to be more
significant, since no indexes can be used to speed up these operations. IIF
statements are native to SQL, while Switch is a VBA function - there is a VBA
IIF also.

SWITCH is generally easier to read (as a human) than multiple (nested) IIF
statements. As far as your example goes, the DLookUp function is probably
affecting the query performance more than the IIF function.

It seems the general consensus is that Iif statements aren't very efficient.
I'm having a hard time, though, trying to figure out what to use instead.
I've seen some posts on Switch, but I'm still uncertain how to use Switch.
Can someone enlighten me please? I have several Iif in an append query and
it is taking about 45 minutes to run. (took the same amt. of time when it
was a make table query)

Thanks you so much!

A couple of examples of my Iif's statements are:
SystemDoc:
nz(IIf([ST]="6","",IIf([M_B]="A",dhSubtractWorkDaysA([LTA1],[LaunchDate]),IIf([M_B]="W",dhSubtractWorkDaysA([LTW2],[MatDueDateCalc]),""))))

and

MCS_TP: IIf([ST]<>"6",[MCSOne],Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartOne] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartTwo] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartThree] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartFour] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartFive] & "' AND [ST] =
'1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSix] & "' AND
[ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" & [PartSeven] &
"' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] = '" &
[PartEight] & "' AND [ST] = '1'"),Nz(DLookUp("[MCSOne]","SASQueryOne","[CP] =
'" & [PartNine] & "' AND [ST] = '1'"),[MCSOne]))))))))))