I have a self-join relationship in a payroll table, which has the goal of trying to find the most recent salary entry that was greater than zero and from a prior date.

The self-join relationship is as follows:

ID = ID

and

Date <= Date

and

Salary > kcZero (a stored indexed calc)

However, this results in absolutely horrible performance, about one second per record to update.

Removing the two inequality relationships and it's very fast.

Other testing seems to suggest that greater than / less than relationships are not fully indexed (even when all fields are indexed) and suffer a severe performance penalty.

Anyone else seen this?

Update -- I did some more testing, and it seems like the type of field doesn't matter. It appears as if the comparison operators (greater than, less than) are very slow.

I was able to work-around this problem by using a relationship to a different table that only holds non-zero salary values & dates, so I could simply match by ID and sort by Date (descending) to get the most recent value.

However, I see this as a general problem -- anyone found a way to do fast inequality realtionships?

I know that fm7 had a serious problem in that direction ...well LeCates showed us at devcon'05 that they apparently have pinned some of it down ...so it have actually been even worse, and back then was the cure to use the Smartranges in a CF incarnation instead:

http://www.clevelandconsulting.com/support/viewtopic.php?t=299&highlight=smartranges

--sd

Is there any way to do a "*" match in a relationship? I've tried using the not-equals operator on a field that contains "" (blank) but it doesn't seem to work. It seems like "not blank" should be a fast match criteria...

Yes a how about:

Int(Sign(

...with the evaluate eventhough referenced fields are empty option:

http://www.filemaker.com/help/FunctionsRef-333.html

--sd