Relationship Stress

[Genx]

I have a table with 200k records. Lets say I have the following data:

[color:red]PK | [color:blue]KeyWord | [color:orange]Data

[color:red]01 | [color:blue]Search Engine | [color:orange]Google

[color:red]04 | [color:blue]Search Engine | [color:orange]Yahoo

[color:red]05 | [color:blue]Foo Bar Foo Foo | [color:orange]Bar|

[color:red]06 | [color:blue]Search Engine | [color:orange]Alta Vista

[color:red]09 | [color:blue]Search Engine | [color:orange]MSN

From each record that matches on the KeyWord, I want the previous matching record to be seen, E.g:

PK 09 would see, PK 06...

PK 06 would see PK 04...

PK 04 would see PK 01

PK 01 would see nothing

Now, that would obviously be just a relationship on:

Table1 to Table2

Keyword = Keyword

PK > PK (Sort By PK Desc)

That's fine, but the PK > PK bit, makes the calculation take forever - it seems it evaluates every single one of the 200k records to work out which has a larger PK...

Now, I've tried moving the relationship one away, like this:

Table1 to Table2

Keyword = KeyWord

Table2 to Table3

PK > PK

I thought that what would happen would be the filter would first reduce the data down to the appropriate keywords from Table1 to Table2, then would do a comparison on the matching 5 or 6 records in T2 to T3 - doesn't work.

Anyone have any ideas on how to optimize this relationship?

Cheers.

Edited August 16, 2007 by Guest

[Genx]

Hmmm, maybe I should retrieve all the matching PK's, via the Table1 to Table2 relationship, then sort them and calculate the next lowest value... relate that and retrieve the value via:

Table1 to Table3

c_nextLowest = PK

... if no one else has any ideas?

[Genx]

Okay, well that worked... Down from 3 seconds to almost instant which is a fair improvement.

What surprises me though, is that FM doesn't take into account the order in which you specify your relational criteria...

[mr_vodka]

What surprises me though, is that FM doesn't take into account the order in which you specify your relational criteria...

Someone at DevCon had asked this question in the Q&A Session. You are correct. FM does not take into account which order your multi-predicate relationships are entered at. No matter how you arrange each criteria of the relationship, it will rearrange the order by what is easier to FM first. IOW, it will try and take care of all the equijoins PRIOR to any theta joins.

[Genx]

Hi John

it will try and take care of all the equijoins PRIOR to any theta joins.

I don't think that's actually the case... at least not in 8.5. If that were the case, then removing the theta portion of the relationship should theoretically have had an almost nill effect (i.e. if it was last to be processed, it would only have had 3-10 records to check and so shouldn't have effected the speed of the relationship as a whole). What was seen instead however, was that the related data would (after the removal of the theta join) be retrieved immediately (vs 2-3 secs with the theta join in place)

Sorry about all the brackets.

Edited August 16, 2007 by Guest

[Genx]

Hmm, does anyone else have any opinions on this and / or learn anything on this at devcon?

Cheers.