Custom Query Field, more efficient to use boolean or string?

[Lord_Vader]

I think this might be a "no duh" question but I have a field in a query:

PorC: IIf([d_Notification]>LastRepDate([txt_Plant]) And [d_Notification]<=RunDate(),"Current","Prior")

It returns whether the notification is "Current" or "Prior" based on some fun stuff. Other queries use this field to filter (it's used pretty heavily)

I'm thinking that it would be faster (by a lot, a little?) to change this to be like.

IsCurrent: IIf([d_Notification]>LastRepDate([txt_Plant]) And [d_Notification]<=RunDate(),True,False)

Am I nuts thinking this would improve performance (My end report takes almost a minute to run with only about 20% of the data entered so I'm trying to shave time wherever)

Penny for your thoughts?

 

[spikepl]

1. Are the fields used as criteria in your query indexed?
2. Your two functions may be the main performance killers - can they be expressed in the SQL? Or can the calculated data be stored, even if violating normalization rules?
3. I guess there might be small improvement in performance by changing to a Boolean, but I would not expect it to matter much. It would be more interesting to see if an index on this field helps or not, further down the road.

 

[Lord_Vader]

So your comment made me break down and eliminate my vba functions in favor of pure SQL queries. I hate doing that because it creates a bunch of queries instead of one neat functions, but I can't argue with the speed - it pretty much halved my opening time.

On my original question, I'm convinced I thought correctley, Booleans will evaluate faster than integers, which will evaluate faster than strings.

...but probably not by much.

I also added an index, because I'm an idiot and forgot to when I made the table (My keys are normally whatcha callits, the opposite of natural, so I never had to worry about manually adding indexes) and I'm sure that helped too.

Thanks for your thoughts!