1 of 1 people found this helpful
You may not have to do as many searches as you expect from your description:
- collect all distinct values from table A and all distinct values from table B (the indexes - different methods for doing this: ValueListItems, a ListOf summary, an ExecuteSQL() with DISTINCT,...)
- do a FilterValues of both to get a list of what both have in common
- do an anti-FilterValues of that common list against the list of table B and you'll have everything that is in B but not in A
My first question would be why you don't simply create a relationship on the relevant fields - what you are describing sounds like exactly the problem relational databases were created to solve.
However, given what you have posed I suspect that process two will end up faster - you will be processing a smaller number of records starting from table B to gather your set of search terms, and provided the field in table A is indexed it will be faster to get a subset of records from that table.
I think option 2 of searching a large table fewer times will result in a faster total execution.
"Q1 Is there any reason why scripting-wise one of the two methods is better?"
The scripting engine is pretty fast (when compared to a human doing the same work), but the calculation engine is usually much faster.
FileMaker Find operations are designed to be fast and to scale well with higher numbers of records.
This is the kind of thing that is fairly easily benchmarked in a prototype database with sample data structured to represent your situation.
The results may also depend on more factors than the two options you described.
Is this a local database or client/server? LAN vs WAN?
There are even potentially faster approaches than what you described.
@Daniel - good question. i have done that (in the real situation), but because of many variances in spelling (IBM Canada, IBM-Canada etc), comma's, and even character sets (these special characters on top of letters etc), I am still left with the described problem.
@Tom - Clear explanation, I will stick to the calculation engine the as much as possible.
@ Wim - that sounds like an intelligent solution. I understand what you are doing roughly, though never used Filter Values - will look into it. Thanks!