constrain found set to avoid big unindexed search

[Constantine]

Hi

in a table with tens of thousands of records, I need to perform a search on two fields: one can be indexed, the other can't (it is a calculation giving a count of the number of records in a related table that match certain criteria).

to speed up the search I thought I could use FM 6's "constrain found set" search (I only recently upgraded from 5.5); my thought was:

1. perform a search on the field that can be indexed, thus reducing the found set to a small number of records

2. perform a second find, this time on the field that cannot be indexed, but constraining the found set rather than replacing it.

I thought that the second search would only have to search the unindexed field in the few hundred records returned by the first search; however, the second search is searching every record in table all over again and so there is no speed advantage.

Is this expected behaviour?

many thanks in adavance for any advice,

Constantine

[Fenton]

It was not expected, but that's the way it works. It's more like a transparent Modify Find than what it sounds like; which is useful for users, but not really for developers.

I've found (pun) that it's much faster to do your first Find, then Loop and Omit the records not matching your criteria in the unindexed field. It depends on the size of the found count.

Freeze Window

View as Form

Go to Record [Last]

Loop

[BobWeaver]

I agree that this constrain found set "feature" is one of the biggest disapointments in FM6. Like Constantine I had hoped to use this to speed up a find on an unindexed field, but ended up using a technique like Fenton's.

[Constantine]

thanks for your posts: yes, what a shame...

Fenton, thanks for your tip

best

Constantine