Search speed in related TO

[bluearrow]

Hi,

In 3 related TOs I have the option to:

1.- Just copy the foreign keys to the parent tables, then search in whatever fields and related TOs the relationship allows

2.- Copy the foreign keys and copy the field contents. Then search in the source TO fields, not in the related TO fields.

Is there any difference in search speed?

Is this standard practice? (it did not used to be so prior to version 7)

Assuming it is OK. Would reporting speed also not be affected by not having the data in the source TO?

Thank you

[Vaughan]

What about option 4: put the related fields on the layout and type the search criteria directly into them, and let FMP work it all out. Don't copy anything around.

The first find might be a bit slow, but after that all the data will be cached and it'll be pretty fast.

[Fenton]

You might want to look into the List() function (8.5 only), and the FilterValues() function, for getting related IDs, then getting an "intersection" of 2 lists. Also Go To Related Record [ found set ], for going between tables. There is more than one way to put together a Find that involves related fields.

If you use Vaughan's simple method, and there are local fields also involved, a Find on the local fields, then a Constrain Find on the related ones will usually give you a big speed boost.

[bluearrow]

If you use Vaughan's simple method, and there are local fields also involved, a Find on the local fields, then a Constrain Find on the related ones will usually give you a big speed boost.

This option looks like very scripting intensive to me, taking into account that I completely script the search (no use of FM native menus at all). Vaughan describes what I mention in point 1. However, my question was about speed and you seem to point out that searches in related fields are slower than in local fields, then there is a point for copying fields to the related table.

What is the problem with copying the fields? (it seems to me less complex than to script a very complex local and then related search).

And what about reporting: would reporting using a lot of related fields be affected? or that is not an issue anymore in FM 8.5?

[Fenton]

It is not very complex to to a Find on local fields, then do a Constrain Find on the results, using related fields. You should of course trap for errors, which can be as simple as:

If [ Get (LastError) ≠ 0 ]

A local Find is about 100 times (guess) faster than a related Find. So whatever complexity can help speed things up is generally worth the trouble. We're talking about scripted Finds here, not manual ones (which are difficult to predict).

It depends on the situation what method is used. In your original post you said 3 related TOs. But I have no idea what you mean by "copying fields to the related table". I'm sure it means something about passing the criteria, but if you could be more specific about what you're doing we (collectively) could give better advice.

For example, is someone entering the criteria? If so, how? And how do you know which criteria goes with which table? What is the relative proportion of records you expect to Find in each? A few, most, can't tell? Is it an AND Find?, do the final records have to match finds in all tables?

What it comes down to is; if the simple Find is fast enough, then fine, otherwise it needs more work. It may even be that after all the work, it's only a little bit faster.

Reporting is really a different topic. The problem there is not so much Finding the records in each table, it's where and how to get them all on the same layout to print. Yes, you can use portals, but no, they cannot break across pages without getting chopped off (it would be nice if FileMaker could fix this, but I don't think they have; I never even try it anymore).