If you query the SQL DB directly, with a "fancy" search, is it any faster ?
check that an index exists for the search fields you use
> Try to do any fancy searches though, and it becomes uselessly frozen
As mentioned, make sure that the ESS/SQL field(s) you are searching in are indexed on the SQL side.
You can get potentially dramatic speed increases by using the "field contents match" operator in FM. By this I mean when in find mode in FM, put ==searchValue into the ESS field. In my case, the SQL table has 300,000+ records in it and the difference in searching one of the main fields is significant.
If you can, create one or more views in the SQL dB that will cut down the amount of data that you are linking to via ESS. Use the view as the basis for your ESS table instead of the raw table. This is assuming that your raw tables have a lot of records. If the tables you need to use aren't too big (in record count and number of fields), then can probably skip creating special views.
Interestingly, if you use GTRR in FM to get a found set of records in your ESS table, it works pretty fast as well. Apparently, GTRR uses the same kind of "field match" logic as searching with the "==" operator.
Credit these tips to Steven Blackwell. He had an article about this quite some time ago.
Thanks for both of your responses. Digging around in SQL, it appears I'm hitting a non-indexed View, and cannot index it in its current form. (It's not schemabound and has too many joins.) It has half a million records in it as it pulls product data (10K records), and multiple related records from another table (~50 per product). It's the ERP's structure for allowing the addition of 'infinite' optional fields to the product.
So to answer your follow-ups: no, querying in SQL isn't any faster. Either I do some fancy footwork with the view, or go straight to the tables which can be indexed and pull the data over to FMP, in which I can make the relationship and build from there.
Thx for the pointers - I've got some work to do!