cartesian or constant...which is better?

[aharown07]

Suppose I have two tables, one (Table1) has only record and will always have only one record. In a layout based on Table1, I want to see every record in Table2, which is alot of records.

I've done this a couple of ways. One was to take a number field in Table1 (containing a 1) and cartesian join it to a numeric serial id in Table2, which already exists for other purposes.

The other way, is to put a "constant1" field in Table2 and do a simple equijoin between them (Table2 autoenters "1" in that field in that scenario)

So, I'm wondering... which is better? Seems like at some point I ran into some weird and very slow indexing using cartesian join and that was when I started putting all these constant1 fields in. But now I'm wondering...are these fields necessary?

Anybody know what the pros and cons are of one method vs. the other?

[tgilders]

I would say Cartesian join is better, because that's what's it made for. I can't say for certain, but I'm wondering if FM even goes through the effort to look at indexed fields, or "under the hood" relationship data to make the match. Seems like it just does it.

I've definitely had slow performance with the "CommonOne" method in a version 6 database with over 500,000 records, and when Cartesian joins were available in 7 & 8 I just switched to those instead. No problems.

[John Mark Osborne]

Avoid the constant relationship at all costs since it is very slow. I can't tell you how many speed issues I have fixed in FileMaker because of a constant relationship. Always use a cartesian product operator if you have FileMaker 7 or later.

[Stuart Taylor]

sorry guys have never heard the term cartesian can you define it for me...

[comment]

A relationship using the "x" operator creates a Cartesian product of the joined tables.

[jteich]

fm-help on x-relationship

-jens

[Stuart Taylor]

Thank you both ... can't believe i missed this.

know now though wah-hoo... hello slicker relationships... goodbye annoying constants

[The Shadow]

I tried this and notice when a X-product join is used, neither of the fields used needs to be indexed, so apparently it doesn't use the index for anything in this case.

[comment]

You can even delete the fields themselves.

[John Mark Osborne]

Never tried that. Pretty cool! I guess it wouldn't be a best practice but it's always neat to know these things. Thanks for lettig us know.

[David Jondreau]

Can you talk more on that? I've seen a lot of performance issues with that too and can't figure out how to get the solution to run faster.

What do you do in a situation when you want to find all the records where a certain value is '1'? Do you created a regular number field in the other table with an Auto Enter of '1'? Or a global field set as '1' on Open? Or a global calc field = 1?

Thanks

G