General-Purpose Read Method, Take Three

Questions
, ,
21

@Kirk , sure. Stripped down to the basic idea, the construct looks something like this:

While ([
~result =   "" ;
~i =        0
] ;

~i < ~recordCount ;

[
~i =        ~i + 1 ;
~result =   ~result &
            JSONSetElement (
                "{}" ;
                [ "fieldA" ; GetNthRecord ( Table::fieldA ; ~i ) ; JSONString ] ;
                [ "fieldB" ; GetNthRecord ( Table::fieldB ; ~i ) ; JSONString ] ;
                [ "fieldC" ; GetNthRecord ( Table::fieldC ; ~i ) ; JSONString ]
            )
] ;

~result

)

The actual expression is quite a bit more complicated because part of its job is also to produce the exact result shape I want from the method. I attached the full expression I am currently using if you want to look at the real thing. But the harvesting idea itself is basically what you see above.

Your point about Evaluate() and injection is a good one. In this case, though, none of the harvested data is inserted into the expression being evaluated. The generated expression contains the field references. The actual record values only enter the result when GetNthRecord() executes.

Performance surprised me. Replacing the script-level record traversal with the generated While()/GetNthRecord() collector gave me roughly a 2× improvement at that stage of the implementation. That change is really what made the performance of the read method start becoming acceptable.

I haven't tried to establish that this is the fastest possible way to harvest the data. What I measured was this approach against the script-level traversal it replaced.

queryResult.txt (4.9 KB)

22

I wonder if you replaced the JSON with ICR?

JSON - not just in FileMaker, but universally - is not a highly performant data format; it contains a lot of overhead to maintain the structure - a structure that might be easier to built in JSON, but at a cost. However, not looked at the full calc yet, so maybe there is some logic in using JSON.

IDK if you have explored this, but the JSONParse command may be a way to further increase performance while maintaining the JSON construct.

Part of the personal bias may be that as a 20+ year FM dev, many of these "new" additions are useful, convenient, and rapid time-to-value, but not all of the long existing, finely tuned legacy syntax are good candidate for replacement.