Filemaker Native CF Performance

Lounge (Discussions)
1

I quite often use the CF 'ZapValues' to remove the values in ListA from ListB:
https://www.briandunning.com/cf/193

Which is a recursive CF which does exactly what I need, but I find performance to be pretty bad when dealing with lists of, say 20K values on either side.

PlugIns from BaseElements and MBS can perform this functionality lightning fast, but I'm always keen to try and keep things native where possible, and it bugs me that the native CF functionality seems to have this performance hit after a certain number of iterations.

I've tried to re-create the functionality of 'ZapValues' using the new While function, and although it works, the performance is no better than before.

Does anyone have any better (faster) ways of filtering lists without using plugins?

2

I came across a really clever non-recursive custom function that I vaguely recall doing this but now I can't find it. I think it used UniqueValues and/or SortValues and prepended one list with an invisible character that was ignored by the Sort/unique function. Does that ring a bell for anyone?

1 Like
3

Hello @JamesG

Some ideas/thoughts/feedback for you:

Bruce:

Bruce Robertson authored a CF similar to ZapValues, which, I believe, is named AntiFilterValues. You might look for that function, and see if it is more performant.

Agnès:

I would also look to see if you can find anything by the brilliant Agnès Barouh. I seem to recall that she once posted a custom function that leveraged the v.16 FMP native SortValues function in a really genius way.

EDIT: Correction: The function used UniqueValues (not SortValues). See link posted below by @jwilling.

My memory is no longer clear about this, but I believe it might have been another implementation of the ZapValues/AntiFilterValues concept. If you can find it (or if anyone can chime in with a link to it), I would encourage you to also test that for performance.

Agreement with your findings:

FWIW: I have also found that, given the same number of iterations, and the same work being performed with each iteration, the While function has not offered a significant performance advantage in the few cases that I have tested -- though it certainly can help to write easier-to-read code,

Regarding optimization of CFs:

At the risk of stating the obvious:

The key to making such functions perform optimally is to both:

  • Keep the overhead per iteration as low as possible.
  • Keep the number of iterations to a minimum.

In the context of a re-write ZapValues type of function:

If you simply re-wrote the ZapValues function by keeping the same concept, but using the While construct, then I believe that you might be able to win a performance gain by making changes to your implementation. The key would be to exploit any potential to zap more than one value in the source list per iteration.

Of course, the resulting function will likely lose its elegance that comes with its simplicity, but perhaps it would be a worthwhile exchange if you are able to write something which is air-tight and more performant. I think it's worth checking out to see.

Examples of things to play with to reduce the iteration count:

  1. If your input list contains duplicate values, is the code zapping all instances of a repeated value at once within a single iteration? Or does the code iterate through every value in the input list?

  2. Experiment with operating on a sub-block of the input list per each iteration, and performing multiple tests/zaps per iteration. This would likely mean some repetitive code, but it would reduce the number of iterations by a factor determined by your block size.

  3. Combinations of both #1 and #2 above.

Finally:

Before investing much time in evaluating and possibly improving the performance of the implementation that you wrote, I'd strongly suggest checking out both the implementations by Bruce and Agnès. Doing so may very well yield some great insights, is likely to be inspiring, and may save you some time.

Kind regards, and good luck!

I hope that you will post back with your findings...

-steve

3 Likes
4

Hey @jwilling

Yep! I'm fairly certain it was Agnès Barouh.

And I think you've described it really well. I'm hoping someone will be able to find that and post a link to it.

All the best,

-steve

2 Likes
5

Can you put some numbers to "pretty bad"? That would frame what alternatives are available.

One way would be to use JavaScript in a webviewer, which typically would be able to handle this very quickly.

3 Likes
6

I think I found it: FileMaker Custom Function: ExcludeValues ( MyList ; Out )

It doesn't work for every value and/or locale I believe, but if you control the environment and have predictable inputs, I think it would work.

Here's a community thread about it. Claris Community (English)

Agnès Barouh is from another planet.

4 Likes
7

Super Interesting stuff from Agnes. I'm always intrigued as to how one would work out such nuanced behaviour of filemaker native functions which aren't really documented at that level.
Anyway, thanks all!

2 Likes
8

I was looking for the same thing. I found the custom function, but not the community link anymore. I glad @jwilling was able to retrieve both.

2 Likes
9

I never saw the community post until today, so I was happy to stumble on that discussion as well.

10

I hear you about FMP's performance problems with data sets not that large. A slow "CF" Is not the solution in my case.

To solve this very problem, I created a custom list operations in a micro-service that is many, MANY, times faster than FMP. I would be happy to benchmark my results with a sample database you might have and I'll be happy to report them back to you.

Below is an example I posted back in July of 2018 where I solved this FMP performance problem by coming up with my own (FAST), cross-platform, free, and usable by any HTTP-enabled application.

listops
listops1166×625 135 KB

Let me know if you would like to get some actual performance (benchmark, non hand-waving data. This technique is not complicated.

(It would be straightforward to add other list operations)

I created my one-line call to the micro-service so long ago, it still uses the outdated HTTPPOST.

Insert from URL [ Select ; With dialog: Off ; Combinelists::list_result ; // use HTTP Post for larger lists "httppost://localhost:4567/listOps?" & "list1=" & Combinelists::list1 & "list2=" & Combinelists::list2 & "op=" & Left(Combinelists::operation; 1) ; Do not automatically encode URL ]

(The micro-service doesn't have to be local.)

1 Like
11

The coming up FM version might give a new possibilities with the new JavaScript function. Meanwhile it needs a plugin (BE, MBS) or a webviewer to do it. Here a CF a made for extracting lines containing a values from a list from a multicolumn list:

/*
Name:		ExtractMatchingJS ( _list1; _list2; _delimiter; _idpos; _plugin )
Parameter:	_list1: csv-list
		_list2: ID-list
		_delimiter: column separator
		_idpos: column to match
		_plugin: the to use plugin ("BE", "MBS")
Return:	text-list
--------------------------------------------------------------------------------
Autor:		Otmar Kramis
Version:	1.0
Erstellt:	23.12.2019
--------------------------------------------------------------------------------
Beschreibung:	filters a multi column list by a liste by a list of 
values at column _idpos, using JavaScript.
--------------------------------------------------------------------------------
*/

Let ( [

_js = "var list1 = '<list1>';¶
var list2 = [<list2>];¶
var array1 = csvJson(list1);¶
¶
var json = array1.filter(function(item) {¶
    return list2.indexOf(item.id) !== -1;¶
});¶
¶
//var json = JSON.stringify(result);¶
¶
var fields = Object.keys(json[0]);¶
var replacer = function(key, value) { return value === null ? '' : value }¶
var csv = json.map(function(row) {¶
  return fields.map(function(fieldName) {¶
    return JSON.stringify(row[fieldName], replacer)¶
  }).join('<delimiter>')¶
})¶
¶
csv;¶
¶
function csvJson(csv) {¶
  var lines=csv.split('\n');¶
  var result = [];¶
  var headers=lines[0].split('<delimiter>');¶
  for(var i=1;i<lines.length;i++) {¶
      var obj = {};¶
      var currentline=lines[i].split('<delimiter>');¶
      for(var j=0;j<headers.length;j++) {¶
          obj[headers[j]] = currentline[j];¶
      }¶
      result.push(obj);¶
  }¶
  return result;¶
}¶
"
; _header = Substitute ( CustomList ( 1 ; PatternCount ( GetValue ( _list1 ; 1 ) ; _delimiter ) + 1 ; "\"F\" & [n]" ) ; ["F" & _idpos ; "id"] ; ["¶" ; _delimiter] )
; _list1 = Substitute ( List ( _header ; _list1 ) ; ["¶" ; "\n"] )
; _list2 = "'" & Substitute ( _list2 ; "¶" ; "','" ) & "'" 
; _js = Substitute ( _js ; ["<list1>" ; _list1] ; ["<list2>" ; _list2] ; ["<delimiter>" ; _delimiter] )

; _result = Case ( 
_plugin = "BE" ; BE_EvaluateJavaScript ( _js )
; _plugin = "MBS" ; Let ( $jsobj = MBS ( "JS.New" ) ; MBS ( "JS.EvaluateToString" ; $jsobj ; _js ) )
; "Plugin either Base Elements or MBS needed!"
)
; _rel = If ( _plugin = "MBS" ; MBS ( "JS.Free" ; $jsobj ) )


] ; 

Substitute ( _result ; ["\"" ; ""] ; ["," ; "¶"] )

)
4 Likes
12

No, not so. Even without the improved interaction with JavaScript, you can use the current webviewer to execute a bunch of these things already. JS has excellent array diffing tools and I'll bet they're fast enough for what is expected. And potentially faster than having to do a hand-off to an external service.

Which is why I asked about how slow slow is, and how fast ideal is. Probably no point in setting up complex code to go down from 300ms to 200ms unless the code has to be run in large iterations.

13

@WimDecorte:
No, not so.....

So you're saying the upcoming improvement will not allow to use JS in a calculation?

14

Dunno... I'm just saying that we already can use JavaScript for these kinds of operations, we don't need to wait on a new feature.

15

Yes, as I wrote above:

Meanwhile it needs a plugin (BE, MBS) or a webviewer to do it

:wink:

1 Like
16

My apologies, my mind didn't get past the "needs a plugin" :slight_smile:

17

The new script step mentioned for JavaScript still requires a webviewer. It's not entirely a bad thing. It will have some interesting uses. Long term, they may eventually be able to remove the need for the webviewer, but we will have to wait and see.

18

Yes, let's benchmark an actual example for the OP. My code is ready to go and was written two years ago (no plug-in required).

SEE MY BENCHMARK VIDEO WITH AN ACTUAL FMP APP BELOW!

:slight_smile::slight_smile:

19

Some actual benchmark figures would be great - sorry I've gone quite on this one - I'm travelling at the moment, and will get back into this when I return in a few days. Thanks to all who have chimed in!

20

Pure benchmarking is fun. To see the relevance, we also need to know more about use case.

JavaScript running in the webviewer without having to call out is going to be hard to beat unless you are requiring to do millions of cycles. And even then. :grin:

1 Like