ESS Wisdom and Experience

Lounge (Discussions)
1

Hello Everyone,

I am looking at a potential integration between a hosted FMP solution (FMS18, MacOS) and an Oracle DB (version TBD).

Some background:

The integration will not be writing to the Oracle DB; It is a read-only (from Oracle) situation.

The number of tables will be small -- perhaps two or three tables.

At this time, I do not have a sense of the number of records, or the rate of growth.

Options that I am aware of:

  • Plug-in: MBS
  • Plug-in: 360WorksJDBC
  • The use of a microservice that can allow FMP/FMS to query via Insert From URL
  • FileMaker ESS with the Import Records and Execute SQL script steps
  • FileMaker ESS which allows for table occurrences on the relationship graph which shadow SQL tables

I'd like for this post to be a dialog about these last two options, the ESS options.

  • ESS feels like an old and somewhat marginalized technology.
  • It also seems to have earned a reputation for not scaling well to large DB situations.

If anyone can speak to experiences with using ESS, I'd like to invite you to weigh in with what you have learned about it.

--- I will start the ball rolling with lessons from my own experiences: ---

My learned list of points for getting the best performance out of an ESS setup, all of which relate to TO's on the graph:

  • Strive to keep the structures flat.

  • Strive to keep the relationships simple.

  • Avoid Refresh Window, flush cache external, as it means that records will have to be pulled down again that might have otherwise been available.

  • Try to limit searching to Finds that match exactly to key values.

  • Avoid doing a basic Find in a text field without ==, because the SQL DB will be forced to return lots of records across the network so that FMP can then post-process them to do a FMP-style of matching.

  • If find criteria must involve some kind of FMP-style string matching (ii.e not just "equals", but "contains", as well), look for a way to involve any other constraint that meets an exact criteria, do that find first -- then constrain using the more costly constraint. This is because ESS won't automatically make such considerations for optimizing the query, and so we must impose the optimization ourself.

  • Using Perform Script on Server is not necessarily the advantage that it normally is with non-ESS, because each request starts with a fresh/empty cache.

  • When performing a find, enter Find mode before navigating to the target layout/context. We already have made a practice for this with regular FMP tables to prevent unnecessary downloading of record data, but apparently the overhead savings with ESS is far more dramatic.

Credit:

Credit for reviewing the above list, adding the point about entering Find mode before layout navigation, and being very generous with sharing his knowledge of ESS goes to a long-time FMP dev community contributor Lee Snover, who graciously shared his insights when I asked him about this topic. (Thank you!)

One particular question that I have:

Nearly all (if not all) of the above tips are geared towards requiring the least amount of data to transfer from the SQL source to FMP. I think that anyone who has worked in an ESS environment has probably had the experience where suddenly there is a dramatic slow-down while a large amount of table data is being downloaded from SQL to FMP. I'd like to think that by following the points above, that such experiences could be minimized.

It seems likely that there is a varied list of circumstances which will cause the ESS connection to refresh and result in a massive download of data. I'd be interested in learning any insights others have regarding whether some such refresh circumstances are beyond our control as developers, or whether they are mostly avoidable per tips such as the above.

Lastly:

Posting this request isn't to mean that I am sold on ESS, and am heading in that direction. This post is mostly because ESS feels like the biggest unknown of all the options that I have in front of me, and so if I can tap into the wisdom of everyone here, I stand a much better chance of seeing it clearly.

Thanks everyone -- especially long post, I know. Thanks for making it to the end.

-Steve

2 Likes
2

The one thing that I can add is that we found the greatest improvement came when we asked the SQL DB admins to provide us with VIEWS that corresponded to our needs. It wasn't too much work for them and at our end, we implemented those views in the same way we use different TOs.

3 Likes
3

Your original recap is great in itself.

FileMaker won't have indexes for that data, so be mindful if you need to do things involving stuff like data-driven value lists. You said it will be read only, so validations (uniqueness) won't be a problem. Relationships will work, but as you pointed out, the query translation can keep you waiting. Also, you won't be able to add global fields to that table (only unstored calcs and summary fields).

If you do implement it and have some optimization to do, I believe there is a way to obtain the translated queries FileMaker will be sending to SQL, but I forgot how this can be done (what 3rd party tool / required config). Not that it would let you change what gets used, but by seeing the resulting query, it makes it easier to understand how it performs (or lags), letting you seek alternatives that result in better performing translations.

HTH

1 Like
4

Hi Steve,

in one of my longtime projects I integrated different SQL-Server Sources into the FM Solution.

The slowest was ESS via Web/internet to MySQL, both servers were located in Germany but the bottleneck allways seemed to be poor network performance and a bad gateway. The solution does read and write but ODBC isn't the fastest technology. Tables have a max of 90 columns and about 50.000 rows.

Next was ESS to MSSQL on the local network, 30 different tables, 5 to 20 columns, 10 to 6.000.000 rows, the ESS is used for reading, writing is done via API-HTTP with MBS. Performance for reading is good, we don't do much searching, everything is navigated via relationsship. That is fast, even with big tables, no waiting, feels like working on the FM Server itself. Of course local network is gigabit etc.

Next interacting with Gira. Since we didn't get a connect to the local SQL-Server, we first tried interfacing with Gira via API but that was dead slow. Gira doesn't allow any HTTP batch, every write had to be done in a single API call. I proposed doing sync between the FM-System and Gira via a server in the middle. IT has set up a MSSQL server on a third machine. We read and write to that server again with MBS, doing MD5-hashes and timestamps to minimize traffic and have a live sync every 5 min keeping 10th of thousand of rows updated. Usally sync runs in 30 to 50 secs. What happens on the Gira side I don't know but for us it freed performance.

To summarize: in my experience ESS can be OK if network connection is fast enough. When you have to do a lot of searches that may change. I found performance of FMSql on ESS to be poor. MBS-Sql is a bit faster. FM searches often are the fastest in my setup of ESS. So I avoid searches on ESS shadow tables and try to do most of the work with relations. That fills the graph but I gain speed that way.

2 Cents from Holger

1 Like
5

Just a note to say thank you to @Malcolm, @Bobino, @harvest for contributing to this thread. Each of you has either given me some new info that I wasn't aware of, or reminded me of something that I had forgotten.

@harvest : The information about each of your three experiences are really helpful to me. Thanks for taking the time to write out those details as you did. I really appreciate that.

Thanks to each of you for the valuable input here. It is going to help me out.

6

Also, from your original post, one more alternative may be using MirrorSync to sync FM & SQL. I've never done it, but from recollection it is part of their feature set.

1 Like
7

What is ESS?

8

External SQL Source. Some Database Engines like Oracle and MS SQL are sources for FileMaker. One may event add a TO to tables in such a database.

2 Likes
9

Hi All,

Just a note that I found the following document to be a great clarification/reinforcement for a lot of the concepts that were on my mind when I started this thread:

ESS Hinter den Kulissen (ESS Under the Hood) - 2010

Though it is an old document, I nonetheless believe that most of the information it contains is still very relevant to ESS.

Enjoy!

2 Likes
10

Hi @Cecile,

This page has a few videos on the topic of ESS:

The first video on the page (around 5 minutes long) gives a high-level overview of scenarios that ESS was designed to address.

HTH,

-Steve

2 Likes
11

Also, from your original post, one more alternative may be using MirrorSync to sync FM & SQL. I've never done it, but from recollection it is part of their feature set.

Excellent point / addition. Thank you, @Bobino.

12

One point of clarification that is important for project dependencies:
The "import" and "execute sql" script steps work with any ODBC source without restrictions, and is not strictly called ESS.

ESS is where those SQL source tables are treated as native FM tables and to make this feature work there is a much narrow band of combinations of FM version, ODBC driver version and backend source version. Each version of FM has its own set of supported things; and straying outside that supported space has almost immediate consequences in my experience. And that is an important dependency to manage: the back-end database can not be upgrade, the ODBC drivers cannot be upgraded and FM cannot be upgraded unless that whole dependency is resolved as a unit.

The single biggest thing that has plagued some of our busiest ESS deployments are ghost sessions where user sessions that involve ESS interactions get interrupted (with FM Go that is more an issue than with WebD or FMP) then the server for some reason cannot disconnect that session.

Of the available options I would pick either working through ODBC with the "import" and "execute sql" script steps or through the other system's APIs.

Our relationship with the various sync products has been a rocky road. While we don't avoid them at all cost, we also don't see them as 'easy solutions', We always underestimate the time it takes to deploy and troubleshoot them so now we treat them as a project risk.

5 Likes
13

We have systems using ESS to MySQL databases (we wouldn’t do this now), our experience is that ExecuteSQL is very slow using ESS tables despite both databases behind cloud based and ExecuteSQL being called by a FileMaker scheduled script.

1 Like
14

I remember this stuff happening here too. We sometimes had to restart the FM server because for some reason the broken or interrupted ODBC connection brought down the ability to reconnect to the ESS source the next time and that limited the servers script engine bringing it to a stand still.

1 Like
15

@WimDecorte: Thank you for weighing in on this.

Some of my takeaways:

  • The ghost sessions
  • The strict limitations w.r.t. managing versions of three separate pieces that must agree in order to work (DataSource, Driver, FMP)
  • Your view of these various technologies based on what, I am sure, is immense experience
  • The correction about the nomenclature of "ESS", and what that specifically refers to**

Thank you for all of this. Very appreciated.

-steve

** I had long thought that ESS just refers to the feature where one winds up adding Table Occurrences to the graph that are based on an ODBC source (and not to the Import Records or Execute SQL script steps). When I went to draft this post, I researched a bit, and among other things, wanted to check if I had the nomenclature correct. I think that I probably mis-interpreted some of what I read at this documentation when I concluded that I had it wrong. Nice to have this corrected (again) -- thank you.

16

Thanks for this, @AndyHibbs

17

Andy please specify if you are referring to Execute SQL script step or ExectueSQL function. In regards of latter - adding FIRST and OFFSET clauses apparently were introduced to improve ESS data retrieval if I remember correctly?

1 Like
18

A client of mine recently wanted to see if connecting via ESS to a MSSQL table was feasible. The performance, even for simple queries, was a letdown. If you have a fully-populated ESS table and can do a quick test, then it's worth it to see if ESS is acceptable, but my bet is that you'll be much happier using an ODBC import or writing an API to interact with the external data.

1 Like
19

Sorry for the delay, we’re flat out here at the moment.

It was ExecuteSQL function.

All the best
Andy

3 Likes
20

Thank you, any instances of OFFSET or FETCH FIRST used in conjunction with ESS and ExecuteSQL function?