Has anyone seen the order of the keys defined in a multi-key relationship have an impact on performance?
I have not seen any impact, but would like to know if anyone else has.
Specifically, in the case of a relationship where there's two "=" and one "!=" keys.
Thanks!
I think you can find an Under the Hood from DevCon on Youtube that said there is no advantage. Sorry that I can't remember more, like which year, which session.
I still order the keys in a fashion that I think would be the fastest algorithm if I had to do the filtering manually.
1 Like
I haven't ever noticed a difference. One caveat is that I stay away from relationships using inequalities, e.g. > < ≥ ≤. So, if there were a nuance related to those constraint operators, I would not have seen it.
Nice to see you here @ByteTheBullet
I could be wrong but someone many years ago claimed that it is relevant and that FileMaker is kind of filtering the table top down for every criteria one after the other. According to that, on top and following should be criteria which narrows down the results the most first.
Does anybody remember an article with the topic "Cartesian and comparable operators are performance killer!"?
At this point, I'ld like to use a joker:
@HOnza ?
Have to agree. I try to stick to do as much as possible with single key equality relationships that generate 1:1 or 1:n relationships.
There are always exceptions because life is more complicated. When those situations arise I try to isolate them as much as possible. I love the fact that I can use them but they are for special occasions 
1 Like
This keeps appearing and disappearing. Comparable (shouldn't it be rather comparative?) operators are killer for sure. Here's a my oldest example: FileMaker Script Execution Time Cut From 5 Hours To 6 Seconds | 24USoftware
Cartesian is another story. If you only access the first record, it has almost no cost because there is no query performed at all. When cartesian becomes killer is when you try to pull all related records for any reason (i.e. for sorting them).
3 Likes
Predicate order can definitely impact behavior, though I'm not sure about performance.
Here's an instance where I was able to show that different predicate orders produce different outcomes. I found it when messing around with Dave Graham's example file for his DevCon presentation. You can find the file here.
After reversing the predicate order in the relationships he defined and attempting to create two related records in one transaction, the second record data would actually just update the first related record. It's a bit difficult to articulate, so watch the video I sent Dave showing the behavior change and try it yourself if you like. Dave responded that he'd experienced the issue before but just scripted around it to avoid wasting time diagnosing. I'm still not sure that I understand what is happening under the hood.
3 Likes