ESS Search Queries from FM Prohibitively Slow With Large Tables
I've run into somewhat of a roadblock using ESS with FM Server 11 Advanced (clients using FM 10 or FM 11). The issue concerns a server schedule that executes a script from one of the hosted files.
The solution is a back-office application that handles data for servicing of equipment by field-based engineers, who access data on their PDA's. The PDA's write to a MySQL db which is accessed from FM via ESS. All reading/writing is done from the FM side i.e. one script pulls information, the other pushes info to the MySQL db.
The solution has been running for a couple of years, though over the last few months the speed of running the 'pulling' script has decreased from about 2/3minutes to well over an hour. All operations are logged in an error-log within an FM Table and it seems it is hanging for 30+ minutes on very basic searches on fields in MySQL that act as 'flags' to detect that an action is required. For instance, running a search on a table that has 100k + records for a field = 1 could take over 30 mins.
I've tried searching but found little information online. If the solution were restructured to enforce an 'archiving' system whereby old data would be filtered out relationally, would that improve performance, or does the relational enforcement not deliver any efficiency improvements over a good old search? I'll illustrate an example below:
If data over 1 month old were flagged with a '1' in an 'archive' field, and the relationship between FM and MySQL was based on archive=0 between the two tables, and we then ran individual searches in this restricted table instance, would it be more efficient (significantly) than running a search on all the data to begin with?
If I've not provided enough information, please let me know. I'd be very grateful for any insight into how FM works so that I can plan a solution accordingly.
EDIT: I appologise if this should have been in the FM Pro section. Not sure which forum best suits it since it is executed from the Server, but essentially is the same problem if run from the client. Feel free to move it at will!