In serveral publications, FileMaker Inc actively discourages the use of FM strictly as a front-end and using the ESS feature to store all the data in MS SQL, Oracle or MySQL.
Don't expect things to be faster than native FM data storage when you do use it in that fashion, because that ESS feature was not designed for that purpose.
Finding 100 records out of 250,000 should be very fast in FM, how slow is it in your solution?
Thanks , well I actually havent yet got to the stage of testing it and really I am trying to "think ahead".
On paper and in my mind it seems that it would be much slower returning all records then finding than running a view but then I dont understand how ESS works under the hood (nor filemaker finds).
I guess the only way is to test it fro myself but from what you say it sounds like things may not be as I think with SQL backend. im all for keeping it simple if I can.
Filemaker includes an indexing option of the fields.
In the manage database, you can go field options, select storage and choose the indexing status you want.
If your searched field is already indexed, the searching time will be very fast.
But for sure don't index all your fields because this will increase the size of your file.