Which function is more eficient: "Case" or "If"?

[niktemadur]

Hello there.

I have a database that currently has more than 350 fields and 200 records, with 20 layouts. For calculations, I've been using mainly the "If" function, and some of these calculations are quite massive. I suspect that this causes my database to take up to a minute to update all records when I start up, and this situation will only get worse with time.

Recently, I've been coming under the impression that, for the type of calculations that I'm doing, the "Case" function will achieve the same result as the "If" function, but faster and cleaner.

Therefore, my question is: If I completely redo the calculations, changing from "Ifs" to "Cases", will it speed up my database?

Thank you very much for your time.

[Ender]

Changing from "If" to "Case" is unlikely to have much effect on performance. I'd suggest looking at other things to improve performance.

You say it takes "up to a minute to update all records when I start up". The only thing I can think of that would require calcs to update on opening the file is the use of the Today function. Or do you have an opening script updating your fields?

The Today function can and should be avoided, with the Status(CurrentDate) function used instead. Though if you're using the Today function on the match side of a relationship key, you'll need to change your relationships around a bit to put the Status(CurrentDate) part on the parent side of the relationship instead.

You may also benefit from a more normalized structure: Splitting the data into separate files for each entity. A simple example is a Contact database, where a flat file might have 200 records representing the Companies, with fields for each of 10 possible Contacts and three Phone Number fields each. The normalized structure might have a Company file, a Contact file, and a Phone Number file, where a Company can have many related Contacts, and each Contact can have many related Phone Numbers.

[Lee Smith]

Hi Nik,

I remember this coming up in the past, so I did a search using your keywords of [color:"blue"] +speed +Case +If +test and came up with a few hits. l think it was this one that I remembered. Click here

HTH

Lee

[niktemadur]

Ender: Yes! I'm using the "Today" function, so that will be going out the window as of (ahem) today, or maybe I should say, as of Status(CurrentDate). Also, so far I've only created separate files for simple Relationships with no dates involved, so that won't be a problem.

However, splitting the data sounds like a scary proposition in my case, since it's a Human Resources database, with many separate layouts listing reports, such as anniversaries, birthdays, driver's licence expiration dates, vacations, contract statuses, as well statistics by department (age, time as employee, schooling, that sort of thing). It's pretty comprehensive, and changes every day.

Lee: Thank you for the link to the thread, it was buried deep in the archives. I'll keep your search technique in mind. The thread makes it crystal clear that "Case" is better than "If". Many layouts in my database have several "If" calc fields, some of them containing up to FORTY statements. Yuck.

Just as a side note, the most important items in a Human Resources database, in my opinion, are blood types and allergies to specific medicines. Would you believe that when I began, fully 30% of employees didn't know what type of blood they had? Now they know.

Thanks to both of you, I've got a nice way to keep myself busy for the foreseeable future. Gonna roll the sleeves up...

[Ender]

Another thing to know about swapping status(CurrentDate) for the Today function: If the field is used in reports or find screens, the search will become an unindexed find. These are much slower than finds on indexed fields. If it's too slow, you can change the search around so that the date range is searched instead.

Say you had a calc:

CPR Expired? (calculation, number result) = case(Today > CPR Date + 365, 1)

This flags when the CPR Date is over a year old. With the Today function, this can be indexed. With the Status(CurrentDate) function, it cannot. So instead, you search for <4/21/2004 in the CPR Date field. This search criteria can be built dynamically by an Insert Calculated Result[] script step:

...

Enter Find Mode[]

Insert Calculated Result [ CPR Date , "<" & Status(CurrentDate) - 365 ]

Perform Find[]

...

Just make sure to be on a layout that has the CPR Date field visible when you run this script step.