Performance core principles: 4. Table widths

Is that wise for me to go all in with repeating fields like that? I'm not saying you're wrong, it's just that repeating fields have always seemed strange and old (I guess since I started using FileMaker long after they were used much).

But you wouldn't suggest that method with indexing? I've always let FileMaker choose when to index tbh. Is there a 'core principle' type topic on 'when to index'?

Have you tested the network impact of this? My gut says that downloading a record that has 10 repetitions full of data could be nearly as costly as downloading a record with 10 separate fields. is there something about fetching the table/field metadata that makes it faster to fetch a repeating field than 10 fields?

2 Likes

Maybe the nature of repeating fields as an FM implementation of arrays which could be considered as single instruction multiple data (SIMD) are faster then individual fields somewhere processed under the hood.

When it comes to layout objects conditional formatting and trigger assignment one definition takes care of all repetitions. Maybe something analog happening within the cells being faster then individual fields within the structure ..

2 Likes

Our ecoWeb App uses hundreds of repeats to store hundreds of css properties for each theme and overrides for each web page. This runs very happily online on computer or iOS over cellular and has been doing so for more than 5 years.

The reason I recommend the use of repeating fields for data that will never for searched on nor used in relationships and hence never requires indexing is because it works very well.

I never make recommendations lightly.

Cheers, Nick

3 Likes

Right on, thanks for the real-world testimonial. At the end of the day, we really just want stuff to work well, and it sounds like it does!

2 Likes

This sounds very plausible. Also I'd never heard the term SIMD, thx for that.

1 Like

Yes, I suspect that is the case. It is undoubtably the case that if I added 750 additional fields to an EcoWeb record it would have an adverse effect on performance. I can add up to 32,000 reps of any field which delivers really significant benefits - used in the way that I am using them. It is also much more efficient writing code / expressions to be able to address a large number of fields purely numerically.
However, this is not something for nothing, it requires care and as you observe the normal things you can do with a field are reduced. It is just another tool in the FileMaker box suitable for handling lots of “simple” data points.

1 Like

My pleasure - always happy to share
Cheers, Nick

1 Like

your empirical test match my observations @nicklightbody. To me it could be indicative that there is major potential for code optimization.

I am currently narrowing down some tables that I use by combining multiple booleans into one number field and use one number field also to get rid of 2 integer fields to be stored in one number field. Low code at its best. (by low I mean closer to the machine).

2 Likes

I loved this comment.

1 Like

Good - interesting that you are going the same direction.
I talked a bit about this on the podcast I did with Jeremy Brown recently - he sounded unconvinced - having never really looked at repeating fields - then came back a few days later and said he had asked Todd who had agreed with me. I have been using this approach for more than 5 years now and could not do what I do without this method being efficient. I told FileMaker some time ago that this is a valuable method which means it is essential that they retain repeating fields going forwards.
Cheers, Nick

2 Likes

This is an important reminder. It's a different way of working with the data and its not immediately clear how to begin. I'm just thinking of how repeating fields are used. For instance, how do we find the first available slot? Can we re-use slots? Do we have to remove empty slots after data is deleted? And so many other questions that arise.

Vectors aka repeating fields enhance computational execution in so many aspects besides the stated above (shrinking field count). No need at least for me to get any platinum advocates voting for it or does it? Claris should do what’s logical not political IMHO ..

1 Like

In the past opinions have been expressed that repeating fields are an irrelevant anachronism which is why I told Claris I would mount a one man protest outside their front door, that was at the wedge, if they ever suggested deprecation.

It would be good if Claris received feedback about the utility of this feature so that product managers have market information on use and value.

Cheers, Nick

2 Likes

Then there are proofs that for some specific cases repeating fields provide better performance than having a related table. Would using a list or a JSON structure in a field provide also a better performance ?

Sounds like someone should set up an experiment to test and gather data.

A key element in this sort of question is generally what would be doing the work with the json - the main FM calc engine or something else?

If the load on FM is reduced it would be good.

If in practice it creates extra work for FM then probably not such a good idea.

I test, observe, measure and reach conclusions in all of my work on performance over the past 10 years or so.

It’s the only reliable way I have found to separate speculation from fact.

Looking forward to hearing your observations?

Cheers, Nick

one scenario where repeating fields are faster then related fields where duplication of parent and recreation of child records appears slower then one step duplicate and child data in repeating fields ..

To give up normalization for performance can be tricky but it should not go so far that a single text field would do all the database and you pretty much have to (re)write your own dbms where the storage is just that field and the rest is on your own implementation.

That we have to use Pause and Refresh besides other tickling to get data and storage in sync adds another perspective ..