Sign in to follow this  
Followers 0
Visionjcv

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.

Thanks,

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

    • ESS with Advantage Database Server (ADS)
      By El_Pablo
      Hi guys,
      Is there a way to connect an Advantage Database Server (ADS) with FM as an ESS?
      Anyway that can do the job done such as a ODBC wrapper, a plugin, JDBC.
      Thanks in advance!
    • Better option for working with ESS tables (JDE/Oracle)?
      By eljefejb
      I've got a file that contains several external data source tables from a JD Edwards EnterpriseOne (Oracle 11g) database.  If I understand correctly, FileMaker doesn't just execute a query via the ODBC connection, but rather downloads each connected table in its entirety when the file is opened.  Is that correct?  The problem with this is that the JDE tables are very, very large!  Each one can have a large number of fields/columns, and thousands of records!  This means that a ton of superfluous data must be transferred over the LAN.  (I only need a couple fields, and only from records that would match a WHERE clause.)  This makes the FileMaker file very slow to load, and very slow to change layouts.
      There must be a better way.  Anyone have any suggestions?
    • Required Values in field from SQL Data
      By Cerbera
      Hello all, Happy New Year and all that!
      I'm back at it already and have hit my first hurdle with using ESS (now the DSN is working that is!)
      I have several SQL views on a read only DSN which I'm pulling through to FM.  The first view has data relating to our customers and this is pulled from our ERP, which is the system of record, hence the data is read only.
      The problem I have is that there are four fields that are showing as "required values".  The first 3 are indeed required and will always be populated, the fourth (ID of parent account) however is only likely to be populated about 10% of the time in the ERP data.
      Of course when I edit any local fields which I've added to a layout using a related FM table, the solution validates the data from SQL and complains that the parent account ID field is empty, but of course I can't fill this in as the data is read only and set to can't edit... vicious circle! -worryingly I originally typed viscous circle which is a completely different thing! 
      Can anyone advise why this field is insisting on being populated - does it come from the SQL data, e.g. some kind of primary key? If I remove the "required value" bit from the field in manage database it just reappears.
      Thanks for any help you can offer.
       
      Regards,
       
      Tim
    • SQL Server 2008 ESS - Only displays system tables
      By Cerbera
      Hi all,
       
      This is my first post in the forum - one of no doubt many to come!
       
      I'm working on my first solution and as part of that I'm pulling in data from a SQL Server 2008 database which will be augmented by file maker tables.
       
      I have been able to locate the DSN and setup the External Data Source, however when I try to add the table in the relationships screen I can only see the system tables in the SQL database - not the views and tables that I have setup.
       
      Does anyone have an idea of a possible solution for this?
       
      Running FileMaker Pro Advanced 14 on Windows 7.
       
      Many thanks,
       
      Tim
    • 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: 
      Server
      - Win 2012 Server, FMS 13 Advanced,
      - ODBC Driver: ODBC Driver 11 for SQL Server 11.0.2270.0 (64-bit)
      Clients: 
      - 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