General-Purpose Read Method, Take Three

Questions
, ,
1

I have tried building a general-purpose read method twice before — one common method that other methods call instead of hand-rolling their own Finds/SQL, returning data in a predictable structure. I ended up retiring both attempts. They did the job but the runtime cost was too high.

I am taking another shot at it, with a different approach. I am benchmarking the potential pieces first, one by one. I would be interested in second opinions from anyone who has built something similar, or has data that agrees or disagrees with mine.

For retrieval, I tested native find + iterate to extract data, eSQL, eDAPI, and relationship access. There was no universal winner — very different cost curves. In all cases, reads were tested while being isolated from any overhead due to session and script initialization or latency.

Native find + iterate: low entry cost, strong on small indexed reads, cost grows with the found set. Executes better server-side for small sets (N=100 -> 5ms on blank layouts against indexed fields), scales well on a slightly digressive curve (0.05 ms/record @ N=100, 0.03 ms/record @ N=10000, ~300 ms total, does even better client side).

eSQL: real fixed entry cost (200 ms server-side, 40ms client-side), scales very well (232 ms server-side @ N=10000), but hard to justify for frequent small reads.

eDAPI: very fast server-side at small and medium sets (2 ms at N=1, 4 ms at N=100, 30 ms at N=1000). Client-side it has a much higher initial cost at 80 ms, but scales on a gentler curve after that. This is a server-first technology, so that might explain that. Unfortunately, there is a deal-breaker for a general-purpose method: its returned fields depend on what is present on its target layout. I do not want a generic data-access method depending on someone editing a layout later.

Relationships: extremely fast on small sets (1 ms for one record, 11 ms for 100, indexed) but scales badly: 2700 ms at N=10000. More importantly, it introduces a graph dependency, which violates the architectural principles the system is built upon.

So Native find + iterate is the leading candidate after the architectural constraints are applied, with eSQL potentially useful for larger sets (reports, data warehousing).

I expected layout switching and variable assignment to be cheap going in — maybe not quite this cheap, but cheap. I isolated it properly: an actual layout switch measured 0.05 ms locally, 0.03 ms server-side, and switching to the current layout was a zero-cost no-op, so I see no performance reason to guard against same-layout switches. It is worth noting that this is a blank Logic layout with no objects whatsoever — I would expect a populated layout with fields/portals to behave differently once rendering cost enters the picture. Context establishment is not where my problem is, at least in this setup.

Finding the records is the cheap part, packaging them is where the battle will be lost or won. By contract, the method is guaranteed to return a JSON Object. Repeated JSONSetElement on a growing container degrades badly — 0.07 ms/element at 100, 1.05 ms/element at 1000. Best approach I found is building one multi-element JSONSetElement expression and firing it through Evaluate instead, and that held flat around 0.05 ms/element through 999 elements — 21× faster around the 1000 mark. I already had a brush with the 999/1000 ceiling before when using Evaluate, from a localization module that populates global variables through that function, and I have seen other community posts mention the same limit, so this was more a confirmation than a surprise. Chunking works around it and, in my opinion, chunks can safely be concatenated as text because they have already gone through JSONSetElement, so FileMaker's JSON processor has already handled escaping.

I also dug into JSONParse. One text-based JSON object read repeatedly is already fast — the automatic cache handles it. Alternate between multiple text-based objects, though, and it gets worse: server-side, I measured 0.0118 ms/read unparsed vs 0.0045 ms/read parsed. So JSONParse's value is not making one object faster — it is stopping objects from competing for the cache. This is a win for the method.

Set Variable came in around 0.005 ms including overwrites/repetitions, in line with what I expected.

Having killed two attempts on performance before, I am not assuming I have solved it this time. I would be very interested to hear from anyone who has gone down this road before. Have you centralized reads in a large solution without regretting the overhead? What mechanics did you land on? I am also curious if anyone has found a faster way to turn a large found set into structured JSON, or found a way to bypass the 1000-elements Evaluate wall.

Thank you for reading this rather long post, and for any input you might have.

Testing was done on FMS 22 / Virtualized macOS / Mac Studio M1 + FMP 22 / macOS / MacBook Air M3.

2

Hello @karimhanafi,

Thank you for taking the time to carefully describe what you are up to, and what you have tried so far.

A few thoughts are below. I deliberately waited a couple of days to reply on this so as to give some thought about what ideas to offer in response to your carefully written post. I hope the feedback may be useful, despite not being direct answers to some of the questions you posed.

  1. I could see desiring a utility script that writes the current found set to an array of JSON objects; I think that I would prefer having the functionality stop there, leaving the task of generating the found set to the developer who makes use of such as script. In other words, leave the "Find" task outside of the scope, and just have a utility script that does the harvesting.

  2. If I were to implement such a script, I think that the first stab I would take at it would involve a simple loop which iteratively builds up the result in a $_script_variable via the Insert Calculated Result script step (which, historically, has been more performant than using a Set Variable script step -- thanks go to Russell Watson for this insight).

  3. I would not be opposed to a custom function to handle the found set harvest task, but I lean towards a script for two reasons: Ease of supporting inclusion of related table data in the output, and I believe other devs would find it easier to customize and maintain.

  4. Regarding regret: I have experienced regret upon inheriting code where a previous developer had factored all of their Finds into a centralized Find script. The regret did not have to do with runtime code execution overhead, rather it had to do with development time overhead, as I felt that, for a seasoned developer, the code was less readable and more difficult to maintain than would have been the case of using a standard FM development pattern of script steps. (I won't claim that others share my sentiment on this; this was a personal/subjective judgement).

  5. Regarding alternative techniques for building up JSON output:

  • As previously mentioned, the use of Insert Calculated Result to iteratively build up a JSON array.
  • IIRC, @apjcs once shared a technique which involved Exporting record data, followed by processing.
  • This video shares a technique for using SQL literal strings and string substitution to generate JSON output via ExecuteSQL. I'll note that I would suggest going beyond what the video shows, by giving special handling to not only double quote chars, but also to newline chars.
  • For small to medium sized record sets that can be easily obtained using ExecuteSQL, I have made considerable use of some custom functions based upon the technique in the YT video (above). The drawback about this has been that it does cost other devs some extra effort to understand my code given the use of an unfamiliar custom function. For this reason, I use it selectively, in scenarios where I think that that added learning curve can be tolerated.

HTH and kind regards.

3

Thank you, Steve. There is a lot in your answer that either gives me something new to test or makes me look at the problem differently.

One thing I had not given enough weight to is the development cost of the abstraction itself. Your experience with centralized Finds, and your comment about using unfamiliar custom functions selectively, both point in that direction. I have been almost entirely focused on runtime because that's what killed my two previous attempts, but if another developer has to decipher the abstraction every time they use it, I haven't really solved the problem either.

I had already tried to make the request somewhat familiar by borrowing from eDAPI, but your comment made me revisit it and tweak it to match even more. This is approximately the shape I am working with. It might still shift a little with the actual implementation:

{
  "<resultName1>": {
    "tableName": "<tableName>",
    "fieldNames": [
      "<fieldName1>",
      "<fieldName2>",
      "<fieldName3>"
    ],
    "query": [
      {
        "<searchField1>": "<criteria1>",
        "<searchField2>": "<criteria2>"
      },
      {
        "<searchField3>": "<criteria3>"
      },
      {
        "<searchField4>": "<criteria4>",
        "omit": true
      }
    ],
    "limit": 0
  }
  "<resultName12>": {...}
}

It is not eDAPI, but a FileMaker developer who has used the Data API should hopefully recognize the shape: AND within a request, OR between requests and an omit flag. fieldNames is my addition because I need the projection itself to be part of the request.

One limitation I already know I will have to face is sorting. There is no equivalent with a native Find to passing in a dynamic sort specification like I could with eSQL. For now my answer is basically "we'll solve it later", which I don't particularly like and the problem will have to be dealt with down the road. Since you've seen other implementations of abstract read scripts, did any of them find a good way around this?

I had heard before about using Export Records as a fast way of reading data, although I have never used that approach myself. I tried finding the post from @apjcs you mentioned but couldn't locate it. He is prolific enough that I suspect it was probably an answer buried in a thread about something other than reading data through exports.

Unless I am missing something about the technique, I don't think I can use it here. The fields being exported depend on a previously defined export order rather than being dynamically specified by the request. This is very similar to what eliminated eDAPI for me. Execute FileMaker Data API would probably have been the best option based purely on the server-side numbers, but the fields it returns depend on the objects present on its target layout. I don't want the behaviour of a general-purpose read method to depend on someone later changing a layout or an export order. I assume Claris had good reasons for making eDAPI work that way and it is probably fine in the contexts they designed it for, but it doesn't work for this one.

Related data isn't an issue in my case. The Logic file where this runs is deliberately about as barren as a FileMaker file can be: no relationships, blank layouts named after the base tables whose context they represent, and absolutely no layout objects. All calls to the read method would be internal to that file. I do agree with you that a script is a better fit than a custom function for this particular use case.

I also understand the rationale for separating the Find from the actual read, but this is probably where what I am trying to build differs most from what you are describing. The purpose isn't really to avoid writing Find steps. From a method perspective, I am trying to issue one call describing all the data the method needs, potentially from several tables, retrieve all of it up front, and then process it. If I leave the Find with the caller, I am back to issuing separate reads for each context and lose much of what I am trying to achieve with the abstraction. Right now, I can either use eSQL and pay the higher cost, or cluster the method with multiple Finds and loops just to retrieve data.

Insert Calculated Result outperforming Set Variable is completely new to me. I am definitely adding that to my next round of benchmarks. I was already planning to test nested-loop overhead and Loop vs. While() for the record-level read, so I will test this at the same time.

I am also interested in your SQL-literal/string-substitution technique. I am increasingly convinced that building the returned JSON efficiently is going to be the harder part of this problem rather than finding the records, so I would be curious to see how you are using that technique in practice. And if you happen to remember where that @apjcs export discussion was, I would still be interested in reading it.

4

Not sure whether the following has value, as I am not exactly sure what you are attempting to accomplish.

Honzu @ 24U did a large number of benchmarks on insert calculated result vs set variable showing that each iteration of SET VARIABLE took more time than the previous iteration, vs Insert Calucated Result, showing almost no degradation in speed over many iterations. 24U has some benchmarking tools that you may find useful.

Also of note in your quest for a performance FIND - or at least the use for the result - I'll put this in 2 time phases, as it shows the genesis.

If you have 3 fields in your table - a stored calc Get (RecordID), and UNstored calc Get (RecordID), and a summary list of the UNSTORED record ID field, when FIND completes, grabbing the SUMMARY LIST if nearly instantaneous, even over a WAN on millions of records. It is likely this has something to do with the unique internals characteristic of the found set RecordIDs being maintained in memory. Stuffing that summary fields into a global field used to match against the STORED Get (RecordID) from a table from the source find, and doing a GTRR, then pointing to any layout whose context is the base table, is quite fast.

We've used this technique for years. Recently, however, Claris decided to bless us with a simpler codification of this onerous but effective technique.

The new calc GetRecordIDsFromFoundSet and the complementary script step, Go To List of Records, makes the onerous legacy work effort obsolete, yet implements the same functionality in far simpler fashion. All my FINDs now use this from a card window, going back ot the original, resolving the age old problem if the found set being unique to the WINDOW, and not the table layout context.

Also note with eSQL FINDs, any open record will pause the find until the record is closed, making the use of SQL queries a tightly controlled execution space.

5

Thank you, @Kirk. There is quite a bit of useful material in your answer. I didn't know about Honza's benchmarking solution before your post. I downloaded it and will take a closer look at it before my next round of benchmarks.

Your point about Insert Calculated Result also lines up with something @steve_ssh mentioned earlier in the thread, and that one is definitely going into the next benchmark pass. What I had measured so far was the overhead of Set Variable itself, not the behaviour of progressively building a larger and larger result, so that distinction matters.

The found-set technique is interesting, although I think it solves a slightly different problem than the one I am after. To clarify what I am trying to build, the goal is a general-purpose read method where the caller describes the records and fields it needs, potentially across several tables, and gets all of that data back in a predictable JSON structure. I can provide more context if needed.

The Find itself is only one part of this. For the small reads that dominate this use case, the Find is already relatively cheap. The expensive part appears to be extracting and packaging the data afterward. From what I understand, your technique aims to transport found sets between contexts, but you still have to do the actual read afterwards. Please correct me if I misunderstood.

There is one part of your older technique that really caught my attention, though. Using an unstored Get ( RecordID ) calculation as the source for the summary field seems counterintuitive to me, especially if this remains nearly instantaneous over millions of records. Was the unstored calculation important to getting that performance? Did you ever compare it with summarizing the stored Get ( RecordID ) calculation instead? I would be curious to understand what you found there.

6

What about OData call? It would not be relying on layout structure and you would get json response. As as downside there would be a need to modify privileges for a user or use different user for making odata call.

7

The unstored recordID field is unique in that it is apparently automatically maintained in memory. Any other fields that gets gathered requires the traversal of all the records which is time consuming. You still needed the stored to create the relationship match.

for your scenario, this is not likely to work as record ID is unique to a table but can have the same ID in other tables.

On the Odata note; I’m not an expert by any means but I have read that odata performance lags with large data sets. YMMV

your requirement sounds like something where semantic find would work??

Note recordid is not immutable; there are a number of scenarios like data migration that would change the record id so you can’t treat it like a primary key

8

Thank you, @villegld and @Kirk. You have both given me some new things to think about and investigate. I want to take a little time after my work day to properly explore the ideas you've brought up rather than answer too quickly. I'll come back with a more comprehensive reply later.

This thread is turning out to be a gold mine of ideas, perspectives and techniques. I really appreciate everyone's contributions and look forward to continuing exploring these ideas with you.

9

The Underlying Cost: Data Transfer:

For hosted solutions, it is worth noting is that such traversal typcially means pulling the record's data down from the server to the client. This pulling of data costs time because data transfer across a network typically costs time.

The Cost Multiplier: FMP data transfers are Record-Centric:

Further worth noting is that FMP's method of transferring field data from the server to the client is record-centric, meaning that FMP does not transfer data at the atomic level of one field at a time, rather it transfers data as one record at a time.

What this means is that, even if the client solution only needs access to one field of data from the record, if that field needs to be pulled from the server to the client, not only will that field's data be pulled across the wire, but all the data for the other (non-container) fields in the same record will also be pulled across the wire. If the table happens to be a wide and densely populated table of data, this can mean a tremendous added cost in terms of the payload that must be transferred. And, because transferring data across the network is often a slow point in a solution, this added payload usually translates to more time that a user has to wait. Understanding this is to understand one aspect of why devs are cautioned to avoid things like unstored calcs and summary fields.

A second look at the Record ID performance topic:

Speaking in terms of a hosted solution:

When a found set of records is loaded into a FMP client, the Record ID for each record in the found set is efficiently transferred to the client right away. This means, that the client already has these values readily available (as already noted by @Kirk). Gathering these values via an unstored calc set to Get( RecordID ) allows the FMP client to take advantage of the fact that it already has the needed values, and so it is able to quickly return them without any need to pull any data down from the server. It is wonderfully fast.

In contrast:

If your solution were to try to summarize a stored field of data, FMP is going to pull that field's data down from the server unless it already has it cached on the client. And, in many cases that data will not already be cached on the client.

This is because (unlike the Record ID), FM does not send comprehensive (by that I mean for the full found set) record data to the client when it loads a found set. The UI is rendered to give the appearance of a found set of records, but as far as initial transfer of record, FMP judiciously tries to send enough record data to make initial browsing feel nice and quick, but then it lazy loads additional record data on an as-needed basis. (The last I recall, FMP transfers around 25 records worth of data in advance for a Form View view found set, and then anything else is lazy-loaded. That number could have changed, or I might recall it wrong, so please take it with a grain of salt.)

It's a sensible arrangement if you think about it; it helps cut down on a lot of waiting. But, if you suddenly need to summarize that one stored field across every record in the found set, you are going to have to wait for that field to be pulled from the server for every record that is not already in the client-side cache. And, because the transfer is record-centric, you are actually going to be waiting for each full record (minus container data) to be pulled across the wire.

Props to Mark Richman:

I first learned about the "record-centric" nature of data transfer from Mark Richman of Skeleton Key. Back at around version 10 or 11 of FMP, Mark did a number of presentations to share this information, which, at the time, was new to many of us FM devs, and it opened a lot of eyes in a very helpful way. It made many of us think more about architectural strategies that could emphasize narrower tables, or which might factor a seldom used heavy text field into its own separate table.

10

Agreed with @villegld:

The OData feature set looks like a really good fit for what has been described so far in this post:

  • Data returned as JSON
  • Returned fields dynamically specified and not dependent on a layout
  • Sort order can be specified for the returned data
  • Multiple queries (including for different tables) can be packaged into one request
  • It's a feature of FMS that Claris seems invested in supporting

Caveat: Like @Kirk, I have heard mention of performance concerns for large data sets. I think you would want to investigate this before fully committing to it.

Another consideration with OData is security:

  • It requires authentication, and therefore this opens up security questions about how to best handle storage of credentials.
  • Additionally, when OData is enabled on a server, that exposes the endpoint of the service to anyone who can direct their traffic through to the server, and so if I were doing this I would want to research what sort of best practice configurations people use to either prevent this, or reduce its risk.

@karimhanafi: I feel like the specification of features you wish to build out is well-articulated and clear, but, to the extent that you can share, I think it could be helpful to hear a small amount of context to help us better understand to what end this serves. At the very least, it would be interesting to hear, and I wouldn't be surprised if it generates more (hopefully helpful) ideas to share with you. One question in particular which has been in the back of my mind is whether this centralization of reads is intended for one single solution, or if the idea is to build out something which will be applied to many various projects and solutions.

11

First, @Kirk and @steve_ssh, thank you for the explanation around Get ( RecordID ). That makes the behaviour @Kirk described much clearer to me. I was looking at it as an unstored calculation being summarized and wondering why that would not require the same traversal as any other field. If the RecordIDs for the found set are already available to the client, then the performance makes much more sense.

@steve_ssh, that record-level vs. field-level transfer behaviour is also worth flagging here. I was already familiar with it, but it's an important part of the cost model to keep in mind when comparing these approaches.

Thank you, @villegld , for bringing up OData. It is a valid fifth read mechanism that I had not even included as a candidate.

I skimmed the FileMaker OData API guide, and philosophically it actually fits what I am trying to build remarkably well. It supports dynamic filtering, field projection, sorting and limiting without depending on layouts, and it returns the data already structured as JSON. It can also batch several independent queries, including queries against different tables, into a single request. The batching is particularly interesting to me because that is exactly what I am trying to do when several datasets can be read together.

What still gives me pause is that OData is an HTTP API. Having a FileMaker solution make an HTTP request to its own server to retrieve data it already has direct access to feels somewhat counterintuitive. More importantly, I would expect there to be some fixed entry cost associated with going through HTTPS and the OData service regardless of whether the request returns one record or ten thousand.

Since small reads dominate the use case I am investigating, OData would have to be efficient enough at the actual retrieval, projection and JSON construction to recover that entry cost before it became competitive with Native Find + Iterate. Have any of you guys actually tested this kind of setup, using OData internally from FileMaker against the same server?

Still, OData definitely deserves a deeper look.

@steve_ssh, regarding your question about what I am ultimately trying to accomplish with this abstraction: this is being developed for one existing solution rather than as a framework intended to be dropped into unrelated projects. The motivation for this implementation is primarily long-term maintainability.

I have been gradually moving the system toward stable contracts where callers know what behaviour they want, but don't need to know how that behaviour is implemented internally, or even where it actually resides. I would like to get the same property for data retrieval.

If hundreds of methods eventually retrieve their data through the same contract, the actual retrieval mechanism becomes replaceable infrastructure. Native Find + Iterate might be the best implementation today, but if a future FileMaker version gives us something substantially better, I would have one place to change rather than hundreds. Even during development, being able to switch between two or three implementations of the same read in seconds and compare them without touching the calling methods has considerable value to me.

There is an abstraction cost to that, of course, and your earlier comments about maintainability are part of why I am paying more attention to it this time around. data.read internals can be more complicated if they need to be. What matters to me is that the methods using it only need to understand what they can ask for, how to ask for it and what they will get back. The question I am trying to answer is whether I can get that maintainability and future-proofing without introducing enough runtime or development overhead to negate the benefit.

There is also a processing model behind this. Where practical, I am trying to move methods away from a pattern of read → process → write → read → process → write, where reads occur as they become necessary during processing, and toward something closer to batch read → process → write.

The value to me is that it makes the data dependencies of a method much more explicit. Instead of discovering what data a method needs by following its execution and encountering reads along the way, the reads that can be known together are described together. Once that data has been retrieved, the processing can work against a known set of inputs. I find that separation easier to reason about, easier to maintain and easier for someone else to understand later.

So that is really the end I am trying to serve. Not a universal FileMaker read framework, and not necessarily the fastest possible way to retrieve any particular dataset. I am trying to find out whether I can establish a sufficiently cheap and maintainable read mechanism that the rest of the system can depend on without caring very much about how the data is actually being retrieved.