Brian Dunning's FileMaker Custom Functions

joinRepeatingVariableFast ( variableName ; delimiter ; startIndex ; endIndex )

Join / concatenate a large repeating variable / array using a delimiter character

  Average rating: 4.3 (29 votes) Log in to vote

Sam Barnum   Sam Barnum
360Works
http://360works.com

Share on Facebook Share on Twitter

  Sample input:
$$foo[1] = "FOO"
$$foo[2] = "BAR"
$$foo[3] = "BAZ"
joinRepeatingVariable("$$foo" ; ", " ; "1" ; "3")
  Sample output:
FOO, BAR, BAZ

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Uses a recursive "divide and conquer" approach to concatenating a large array of values, yielding better performance than simply iterating over the array appending each item to a large string.

Unlike the version at http://www.briandunning.com/cf/1285, this focuses primarily on performance, and does not stop when it reaches the end of the array (all parameters are required).

There is a commented line which clears out the array contents as they are appended, saving memory at a small speed penalty.

The performance increase is achieved by avoiding creating/appending to very large strings. Instead, most of the concatenation operations happen on smaller strings, the smaller bits being built into larger strings at the end.

Benchmarks from a 10,000 repetition variable, each entry 100 characters long:

* Looping append Script, simple: 13,505ms
* joinRepeatingVariable Recursive Concatenation CF: 6,904ms
* joinRepeatingVariableFast CF with clearing array: 2,960ms
* joinRepeatingVariableFast CS without clearing array: 2,330ms
* Looping "divide and conquer" script: 1,930ms

For 20,000 repetitions:
* Looping append Script, simple: 79,026ms
* joinRepeatingVariable Recursive Concatenation CF: ? (too many recursions)
* joinRepeatingVariableFast CF with clearing array: 5,960ms
* joinRepeatingVariableFast CS without clearing array: 4,626ms
* Looping "divide and conquer" script: 3,979ms

For 40,000 repetitions
* Looping append Script, simple: 523,167ms
* joinRepeatingVariable Recursive Concatenation CF: ? (too many recursions)
* joinRepeatingVariableFast CF with clearing array: ? (too many recursions)
* joinRepeatingVariableFast CS without clearing array: (too many recursions)
* Looping "divide and conquer" script: 8,228ms

Note that these techniques fall apart once the arrays get too large, because of the stack limit. You would want to switch to a scripted approach to get around this, but use a similar mechanism where you concatenate the smallest strings first, then join them into larger and larger batches.

 

Comments

Jeremy   Jeremy, NC
Nov 19, 2012
Are you sure you're function is faster than the one you reference? Have you tested it and demonstrated it? Can you post a demo file somewhere? As far as I can tell, both algorithms have to perform the same O(N) string concatenations, and they even ultimately do those concatenations in the same order. Am I missing something?
 
Sam Barnum   Sam Barnum, 360Works
Nov 20, 2012
Thanks Jeremy, I posted some benchmarks. During testing, I hit the "max stack" issue with FileMaker custom functions. I'd recommend writing a looping script if this becomes an issue.
 
Bruce Robertson   Bruce Robertson
Nov 21, 2012
Doesn't work as written (won't compile successfully in custom function editor). Example doesn't make sense. It calls a function JoinArray which is undefined. Example contains no index values. An example file would really be helpful because it is hard to tell what this is supposed to do.
 
Sam Barnum   Sam Barnum, 360Works
Nov 21, 2012
Thanks Bruce, I had changed the name of the function, but forgot to update the recursive calls in the function itself
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.