FileMaker Relationship Graph Math

There are lots of ways of structuring FileMaker Relationship Graphs. A topic worth discussing...

How does the structure of the FileMaker Relationship Graph(s) affect the speed of a FileMaker solution? What is the effect of the number of:

  • Table Occurrences (boxes)?
  • Relationships (lines)?
  • Paths?

[Q] What is a path?

[A] Hard to explain in words…please see the diagram below. Note that while the number of Table Occurrences and Relationships scale up in a linear fashion, the number of “paths" scales up in a non-linear fashion (faster).

When we compare different ways of managing FileMaker Relationship Graphs, which has:

  • More Table Occurrences and Relationships?
  • More Paths?

Which approach works best for small systems?

Which approach scales better as small systems turn into large system?

What actions in FileMaker read structure and/or data from the Relationship Graphs?

What is happening under the hood? // would make a good "under-the-hood" presentation.

Thoughts?

5 Likes

Great questions @tonywhitelive.

Abstracting the problem to this level does give us food for thought.

The questions lead in many directions and the thing that occurs to me is that growing from small to large is not just a matter of adding more records. Often, growth requires new data structures. In a group of fifteen objects you may not need to categorise them. In a group of 1000 objects, you may want to provide extra structures that allow categorisation. A small system that cannot accomodate that new structure will not be able to grow as easily. I've seen this problem often. To grow a system needs to have extensible and reproducible components ( topic is diverging here : such as small scripts that do one thing).

2 Likes

Excellent, @tonywhitelive!
Exploring the graph and the consequences of different types of graph layouts, along with information how this influences performance, gives a great tool for database design to developers.

2 Likes

Honza did some measurements of the difference between spider, anchor buoy, and selector connector on a large solution.

Graphs with lots of paths (or maybe it’s really long paths) take longer to resolve when a solution is opened by a client. An under the hood session that touches on that would be cool for sure.

Honza found that by changing his graph from spider to anchor buoy, their solution could initiate a PSOS session 3.5x faster. Furthermore, when they added a “connector” table and linked all the anchor TOs, the speed gains were immediately reversed back to the original slow spider speed.

So I’d venture that anchor buoy is the way to go to preserve performance as a solution grows, ie more discrete TO Groups, even with some relationship redundancy, is more performant.

A link with more info: Arachnophobia at FileMaker Konferenz 2017 | 24U Software

I believe Honza’s slides are posted somewhere as well.

7 Likes

Great stuff - thanks for sharing - what I would like to know if metrics still valid on current FM19.2.2? Apparently lots can change on each iteration (but it is tedious to repeat all the time with fast paced updates) ..

This still appears to be the case.

I created two identical files with 50 TO groups (TOGs), each group with 6 TOs (one parent TO and 5 child TOs). In one of the files, I connected all the parent TOs to a single CONNECTOR TO. So we have one graph with discrete TOGs (Anchor Buoy) and one where everything is linked (Spider/SelectorConnector). The two graphs look like this:

I hosted both files on an AWS t2.small EC2 running FileMaker Server 19.2.1. I accessed files using client Pro 19.2.2.

I ran a script in both files Time PSOS, which just calls an empty script (PSOS) via PSOS and measures how long it takes to complete that step:
Time PSOS

The results:

Graph Time
Spider 500-700 ms
Anchor Buoy 100-120 ms

So discrete TOs seem to still outperform spiders/connectors, and the performance benefit manifests even in graphs a fraction of the size the one Honza tested with.

13 Likes

@jwilling Nice work. Thank you!

2 Likes

Great thanks. That was quick. Very impressive indeed!

2 Likes

Now to test:

  • Record creation.
  • Finds.
  • Multi-table commits.
  • Imports.
  • ......

LOL

1 Like

Haha. Well, I'm not a betting man, but I have a strong feeling that those would either be the same speed for both graphs or faster for anchor-buoy. ¯\_(ツ)_/¯

1 Like

Great test, thanks.
It would be interesting to test this with older version of filemaker, before spider was faster, but then FM Inc, silently changed something that made Anchor bouy faster (which is a shame).
But did anchor bouy got really faster, or did spider slow down ?

1 Like

Yeah, if that's true, it would be interesting to know when that switched. The first version I ever used was FM13.

That said, I'm lucky enough not to have any clients on really old versions of FM, so knowing how the new versions behave is good enough for me. I'm interested in the history of FM to a point, but my favorite type of insight is the actionable kind. That's why I'm so thankful for revelations like Honza's research here, which inform my dev decisions.

I'm happy to share my Spider and A/B files if someone else has an older FM setup they wanna test.

3 Likes

I started anchor-buoy with FM 7, never tried spider graphing, but from what I remember somewhere between 8.5 and 9 they made a change in favour of bigger numbers of TOs in the graph. Big graphs with 700+ TOs got cut of in early FMP-Versions or TOs were rendered empty. Those big ones also were slow.

Josh, I would like to take your file and try a different approach ... do you think you can send me your file(s)

Thanks

1 Like

I also seem to remember at one prior DevCon (not Engage) under the hood session with Clay. He mentioned that joins are cached on the graph... but joins of more than (I think it was 5 hops away... if someone remembers what it was please post)... those joins are not cached and as such would be evaluated every time.

4 Likes

Hi Vince,

welcome to the soup, great to have you join us :slight_smile:

Holger

1 Like

Hi Holger, thanks and great to be here.

3 Likes

Sure thing, here are the test files.

EDIT: Whoops! The files I uploaded before were password protected. Here are the unlocked version:

Login is Admin with no password.

Graph Spider VS Anchor Buoy.zip (592.8 KB)

1 Like

Howdy Vince, Good to see you here.

1 Like

+1

Delighted to see you here @vmenanno

1 Like