Sign in to follow this  
Followers 0

ESS Search Queries from FM Prohibitively Slow With Large Tables

2 posts in this topic

Hi Everyone,

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.


Jason V.


Share this post

Link to post
Share on other sites

Are the fields on which you're searching indexed in MySQL? If not, the search will become much slower as the number of records increase. If you can index the appropriate fields in the external DB, you will be able to return results pretty quickly even with large numbers of records.

In my case, I have a table on SQL server 2008 with ~500K records. If I search from FMP on a non-indexed field the search takes about 5 minutes. If I search on an indexed field, it takes <1 second.

Another thing you could do with regard to reducing the number of records, would be to create a view in SQL that only shows the recent records, then have FMP connect to that. I'm not sure how to create views on MySQL, but I'm sure the system admin for that server will know how.


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • MS SQL in Azure and ESS
      By MarttiT
      Hi all!
      I am trying to connect to SQL database hosted in MS SQL server, located in Azure. 
      My setup: 
      - Win 2012 Server, FMS 13 Advanced,
      - ODBC Driver: ODBC Driver 11 for SQL Server 11.0.2270.0 (64-bit)
      - FMSA 13, tried MAC & WIN
      Setting up System DNS without problems, test completed succesfully
      Added this DNS as external DataSource in my DB.
      When trying to add TO to Relationships graph , get the message: 
      SQL Database & All its Tables and Views shows in "Specify Table" Dialog
      "This action cannot be performed because the required table is missing"

      I though that the credentials for SQL Database does not allow to see tables/ edit data. But the SQL database guy said that I have all rights there. At the moment, I can't check it
      I am new to MS SQL connection, but done quite a lot work with MySQL databases. 
      I would be happy to get some hints what to do. The database guy in SQL side is not very helpful. 
      rgdrs, Martti 
    • Connection problems between MySQL and FM databases
      By Wardiam
      Hi everybody,
      I have trying to combine data from a MySQL source with my local FM database using a shadow table with ESS connector. Basically I have done a Point Of Sale (POS) that gets the data from a MySQL database (it contains the information about the products, prices, stock, etc). I have two problems:
      1. I have done an script that connect to this shadow table when I insert a barcode for a product and extract the information about that product. If I open the FM solution and initially run the script, the process is too slow but if I go firstly to the shadow table and then I run again the script, in that case the process is faster. I have solve this problem using and script that goes briefly to the shadow table (when I open the application) before returns to the main layout. Is there other alternative more elegant?
      2. Certain products are duplicates in the database and have different barcodes. If I insert one of these barcodes, a floating panel is shown, an script using ExecuteSQL query is run to show only this products (from shadow table). To do this query, I need to go to the shadow table and do a search in Find Mode and then run again the ExecuteSQL query. 
      I think that both problems are related and I suppose that I need to optimize the connection to MySQL shadow table. Could anyone help me to enhance it? Do I need to include any other step in the scripts to establish or maintain the connection to the MySQL database?
      Thank you very much,
    • Testing ESS availability in a Server Scheduled Script
      By Max Heller
      Using Filemaker 11 Server.
      I have a server script that is going to several ess layouts based on ORACLE views over ODBC, and exporting the data to excel files in the Documents Folder on the server. A separate database has a scheduled server script to import the data into local Filemaker tables that mirror the ORACLE structure.
      This works great when the ORACLE server is online, but when it goes offline (usually without notice), the script just hangs (it eventually times out at the time limit I have set on the server).
      On the client I can do an Open File script step and check for an error before going to the ESS layout, but that script step is not supported on the Server. Is there some other way to test for availability before going to the layout to prevent it from hanging?
    • Renaming ESS fields
      By woodlandtrek9
      Does anyone have any tips for renaming fields that are connected through ESS?
      According to the ESS tech brief, it seems like this should be possible without an issue:
      So if I'm just renaming the field, the unique key should remain the same, right? When I tried it and synced the tables again, the old field was changed to type "Unknown" and the new name was added as a separate field? Is there any nice way to do this and keep field linkings in scripts and layouts?
      (Details: The files are hosted by FMS13 on Mac, connected through ActualODBC to MySQL)
    • Foreign key field not updating relationship with ESS
      By El_Pablo
      My students are developing a project in Filemaker using the separation model. The FM data file connects to a MySQL database.
      I noticed that when updating a foreign key value on the Presentation layer, the view of the value on the related table does not update. Like if the relationship didn't updated.
      Eg :
      I have a "buildings" table and a "building_types" table. There is a field "buildings::fk_building_type_id" which is related to "building_types::id". I am using a layout attached to BLD__BUILDINGS in the presentation layer. In the layer, there are two fields "BLD__BUILDINGS::fk_building_type_id" and "bld_BUILDINGTYPES::name". When I change the "fk" the "name" does not update directly. I must commit the record. Doing so in the data file, the update is instantaneous. What is the reason for this behaviour?
      Thanks for helping