Ditch those Flush Caches, Use Cartesian Join Instead!

By Daniel Wood, 29 January 2011

All too often, the Refresh Window script step is used with the 'Flush Cached Join Results' option checked. Sometimes this is through lack of understanding, but other times it is required to refresh those pesky relationships which have trouble re-evaluating. This article explains why Flush Cache can be detrimental on performance, and offers an alternative and more efficient method for refreshing relationships.

What is Flushing Cached Join Results?

The FileMaker documentation describes this as the following:

Select Flush cached join results to delete the results of queries for related records and cause related records to be refreshed. Do not select this option if you know your script does not affect related data, and if you want to minimize the performance impact of re-accessing related data (particularly when sharing a database over a network).

Whenever a relationship is evaluated (the query) the related records found are cached (downloaded) to your computer. If you are accessing the database over LAN or WAN, then the speed at which this happens depends upon your connection speed. Once cached, the relationship should perform much faster because the records now reside on your machine.

However, by flushing the cached join results, all results of relationships that have been done (i.e. all the records downloaded through relationships) are cleared from your cache, and you basically have to start all over again re-downloading related records when viewing them.

The reason why FileMaker states this should be avoided over network, is because of this re-download factor.

When is a Flush Cached Join Results needed?

Most of the time, a flush of the cached join results is not necessary at all, and typically it is used in error. People may think that because a calculation is not refreshing on their layout, it will require a refresh of the window to display correctly. While this may be true, all too often I have seen the "Flush Cached Join Results" option checked at the same time, merely for added insurance that the desired result will be achieved. Sometimes the performance hit is negligible so it goes unnoticed, but once the solution goes up on the network, that is when the real fun begins!

Flushing of the Cached Join Results should really only be used when dealing with a particularly complex relationship whose results are not re-evaluating as desired.

What causes problematic relationships to not refresh?

I have found over the years that the most problematic relationships are those which make use of globals, and unstored calculations whose calculations reference related records. Sometimes it may be necessary to build these types of relationships to achieve a desired result (notably when using an interface/data model).

The particular situations in which relationships have refresh issues always tends to elude me, but when it happens, the reasons are usually clear, and include one or more of the reasons above.

So, why not just flush the cached join results when it happens?

Well for starters, as mentioned earlier, every single downloaded record that you have downloaded and cached since you first open a file, will be cleared. This basically means that you are starting from scratch since the file was opened, and any record you viewed will be re-downloaded.

Over WAN this is where it is most noticed. When using a WAN hosted database, you may notice upon first use that portals and relationships may load slowly as the record is downloaded - scrolling portals may be slow, that sort of thing. But after more use, things speed up because the records are now being accessed from your cache. Flushing the cache puts you back to square 1.

In 99% of the cases your intention for wanting to flush the cached join results is merely to refresh one particular relationship, not the entire database. If only there was a way to do this...... well there is !

The solution: Using a Cartesian Join in your Relationship

The refresh problem arises because the relationship is unaware of its own need to refresh the results. The trick here is to tell the relationship that it should refresh itself.

Relationships will re-evaluate themselves if one or more fields on the source side of the relationship change explicitly. However, we know that any field put on the source side of a relationship will actually affect the results of that relationship.

The exception here is a cartesian join.

Cartesian join predicates, by definition, find ALL records through a relationship:

cartesianrefresh 1

The above relationship uses a cartesian join, so will find all contact records through this relationship.

cartesianrefresh 2

In this relationship, a standard "=" predicate is used. The result of this relationship will be all contacts whose status is "Active".

cartesianrefresh 3

Here lies the key in this technique. The above relationship uses a standard "=" predicate, AND a cartesian join predicate. The result of this relationship? All active contacts. The cartesian has no effect on the relationship, EXCEPT as a means to refresh it.

We have managed to add a field into the relationship, which has no detrimental effect on the relationship, does not affect the relationships intended results, but is a field nonetheless which we can use to refresh the relationship.

cartesianrefresh 4

So, how does it work?

Simple, just set the field you have used in your cartesian join predicate to any arbitrary value (non-empty). Ideally, you should create a field whose sole purpose is for the refresh of relationships. You could create one field per relationship if you wish to control specific relationships, otherwise you can use one field for all relationships that require refreshing. I have created one in this example called "PortalRefresh".

The field can be set either via a script, or more ideally using a script trigger. The script trigger would be perfect if you wish the relationship to re-evaluate upon changing the contents of another field (which itself indirectly affects the result of the relationship).

The refresh field you choose can be global or non-global depending upon your needs.

An Example for you to play with

I have included at the bottom of this article an example file which you can use to see the method in action.

Because these refresh issues are sometimes contrived, I have come up with a similarly contrived relationship to illustrate the purpose.

cartesianrefresh 5

The example file comprises 2 tables - Home, and Contacts. There are 3 relationships on the graph as shown in the above image:

  1. The first is a standard cartesian join to find all contacts through a relationship
  2. he second makes use of the cartesian join technique for refresh
  3. The third leaves off the cartesian technique and just finds a particular record.

On the Home table there is a calculation which obtains the Contact ID of the Nth record through the first relationship. The user enters a number into a global field which corresponds to the contact record they wish to view.

cartesianrefresh 6

To cause the refresh issue, the global field the user enters the record # into resides on the Contacts table, and is pulled through to the Home table for use in the unstored calculation.

What it all boils down to is a relationship to a particular contact, which will not refresh without a little nudge :)

Using the Example File, a Demonstration

In the example file you will see a portal of all contacts, and their record number displayed beside them:

cartesianrefresh 7

You will also see the following entry fields:

cartesianrefresh 8

Enter a number into the Record # to show box. If you click out of this box, you will notice that the Found Record value does not change to the record you entered. This is the refresh issue. Due to the nature of the relationship & the fact it is based on an unstored calc that references globals in the contacts table, the relationship is not refreshing.

The button beside the field will run a script that either uses the flush cache method, or the cartesian refresh method, depending upon which tab you are using.

Beneath each record entry field is a script-trigger version of the field, which runs the associated script using an OnObjectModify trigger.

Both versions in the example file (flush cache & cartesian) exhibit the same issues, but each uses a different method of solution.

Final Thoughts
You should always use the flush cached join results option on the Refresh Window script step with caution. Don't just check it willy nilly to guarantee your refresh issue will be solved. Understand what it really means and what impact it has on your solution, especially when deployed over LAN or WAN.

Finally, consider the alternative method of refreshing a relationships results by using a cartesian join - you will retain your cached results, and only "flush" the specific relationship you need flushed, improving WAN performance in the long run.

Example File:

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.

Download Example File

Something to say? Post a comment...

Comments

  • Jens Rasmussen 20/11/2015 10:04pm (5 years ago)

    In 13, I found Refresh Object not doing a refresh of the relationship. I read this post long ago but just revisited yesterday, and today I got creative. I already had a 3-predicate join, but the left side of 3rd predicate was an unstored calc. Thinking about whether it would yield a new result was the clue for me: It was before calc = "0000" & "¶" &
    List ( some_user_ids ), but I changed the unstored calc to a global set in the script which conditionally opens the popover containing the portal. The global is set to Get ( CurrentTimeUTCMilliseconds ) & "¶" &
    List ( some_user_ids ). So only it the same user would pop up the portal twice in the same millisecond would I have a minor problem. :-)
    I love being a FileMaker citizen!

  • Eldad T. 27/09/2014 1:01am (6 years ago)

    Hi James,

    Refresh Object is working for me only to hide/unhide object...
    It didn't work for me for refreshing portals.

    for conditional formatting I still have to check..

  • James Bardwell 18/06/2014 6:58am (6 years ago)

    I recognize that this discussion has been going on for awhile and FMP has advanced since it started. However doesn't the 'Refresh Object' script step (for example tied to a script trigger) help address the issue of a portal refresh without having to refresh an entire window? I suppose there are still instances where the script step would not work. But am I incorrect in assuming that 'Refresh Object' script step flushes cached join results for a named portal? FM Help seems to imply that it would.

  • Théo 17/05/2014 8:17pm (6 years ago)

    Hi all !
    Why are your posts already in jun/... nov/Dec 2014 ? Are we on the same planet ?
    @Michael: ESS virtual tables are a pain ! If someone (FMI?) could help !

    @all: THX for sharing

  • Michael Daracz 16/05/2014 3:42am (6 years ago)

    I red this blog post a couple of months ago and I just came back to this. This method works great, but it does not appear to work with external source tables such as Microsoft SQL server. Are you guys aware of a way to refresh external sql data in a "lite" fascion.... Thanks!

  • Jason Young 05/12/2013 5:40am (7 years ago)

    Sorry, should have read the previous comments a little more closely. ;-)

  • Jason Young 05/12/2013 5:34am (7 years ago)

    Hey Daniel,
    I know this is a relatively old post, but we've found that once you start getting a large number of records on the right side, that there is a performance price to just paint the portal. When moving from parent record to parent record with roughly 40,000 records on the right side, the server CPU was getting absolutely crushed and the users were complaining of some serious lag. When we removed the cross join, the load on the server dropped dramatically and performance noticeably improved. Presumably, the relationship does need to resolve all those records in the cross-join predicate, and that takes some doing. I've only tested this in 12, so it may have just been introduced then. Fortunately, the users' need to refresh the portal is less than just browsing from parent record to parent record, so using the traditional flush cache when needed gives them noticeably better overall performance. Has anybody else experienced this? If anybody would like to take a look at my tests, please ping me at Jason@SeedCode.Com.
    Thanks and Cheers!

  • Francis 27/11/2013 5:06am (7 years ago)

    Hi Daniel,

    I have used this technique with great use. However I recently experienced a strange behavior with FMP 11 over a network.

    A scripted search that would use a relation, which also has a Refresh Cartesian Join, worked well ? as long as I was working with one FMP client only.

    As soon as I used FMP network sharing and connected with other clients to the host file, the search delivered zero results, where it should show results in the local use case. So there seemed to be a problem when using this over the network, at least for FMP 11. The scripted search (over network) worked again, after I removed the Cartesian Join.

    The client does not plan to use Filemaker Server, and I'm (shame on me) to lazy to set up a test environment to check this...

  • Cameron 13/06/2012 11:47am (8 years ago)

    I really do dig these little insights and tricks Daniel provides. This one has got to be the most useful (or should I say most used!). One question though, could a similar technique be used for portals that are used to enter data? Of course, as soon as you use the Cartesian Join, you can't create using the relationship.

  • Daniel Wood 11/06/2012 9:07am (8 years ago)

    Hi Phillip, thanks for the comment. As far as I am aware this still behaves in 12 as it did in 11 :)

  • Phillip 08/06/2012 11:52am (8 years ago)

    Fantastic stuff! Does this still apply in Filemaker 12 and with the new file format? I would assume so, but I wanted to check.

  • mark 22/12/2011 8:50am (9 years ago)

    If found for a filtered portal type set up, that you can use the global "search" field in the main table and a global "refresh" field in the source table for the cartesian relationship. If you make the refresh field a calculation = to the search field, all you need is a one line script Set Field [search ; search]

    This commits the record, changes the refresh field and places the cursor back in your search field all at the same time. It also fixes an issue with the onscreen keyboard bouncing off and back on the iPad when using FM Go.

  • Steve Bennett 01/05/2011 5:25am (9 years ago)

    Perren,

    I came across the same script free setup when starting to test this and it seems to work fine in the separation model. I was testing with Interface, Data and Calculations in three separate files which was giving me all sorts of refresh issues.

    I also set up multiple cartesians in the relationship, one for each of the fields that should refresh the relationship. I was testing with a typical invoice model and set the line item price and the line item total to each refresh the invoice total through the relationship without needing any scripts.

    Using multiple cartesian joins in a single relationship may slow it down with large data sets but this is pretty cool so thanks everyone for sharing.

  • Perren Smith 05/04/2011 2:37pm (9 years ago)

    Daniel...working with your example file and Jason's comments I've got something interesting happen that might be of interest.

    I changed the cartesian in the portal relationship to use the global GetNthRecord as suggested and now refresh is instant - no "helper scripting" required at all! Of course this is single file / single user so mileage may vary if using a separation model and/or hosting the file.

    Have you bumped into that new twist yet?

  • Stiti 31/03/2011 10:28pm (9 years ago)

    It's really a very interesting concept.

    Thanks Daniel for sharing this brilliant concept of using cartesian join to refresh relationship and thanks to Jason for giving a clear picture on using global cartesian join to refresh the join result with best performance.

  • Dillip Kumar 31/03/2011 10:03pm (9 years ago)

    It was just an excellent idea to refresh a relationship when used in a database. But the thing is that i have applied it in my project where the client is complaining abot the serious issue of refreshing the page. I have implemented the same thing in my project by using a cross join. But the same issue persist till. I have found a solution to this by finding the records in that layout by perform find upon that criterias which are used in the relation ship. Now in this case it is coming nicely but the process is slow down for 10 sec atleast.
    Please suggest me anybody what i can do next to resolve this performance issue because my client wants speeding up the process.

  • Martin Sorich 05/03/2011 6:31am (9 years ago)

    Also, in relationships that use Cartesian joins with globals, the relationship resolves even when you have an empty table (no records) in either table. So if Table A has no records, and is related to Table B by a global Cartesian join, you can still "see" all the records in Table B via the relationship from Table A.

  • Lyndsay Howarth 04/03/2011 11:36pm (9 years ago)

    Ditto to Honza and Daniel's comments.
    It is an amazing study of something we use everyday and think we understand... and take for granted without asking more as Jason has done about what is actually going on.
    I'm impressed!

  • Daniel Wood 04/03/2011 8:23pm (9 years ago)

    Than you Jason for that contribution, that is an awesome insight into Cartesian Join and how they evaluate, and how relationship predicates in general evaluate, I certainly learnt a lot! I am going to take your advice and do some testing that you mentioned and will let you know how I get on.

  • HOnza 04/03/2011 1:21am (9 years ago)

    Jason, you rock!
    What a complete research. Extremely valuable. Thanks!

  • Jason L. DeLooze 03/03/2011 10:46am (9 years ago)

    In a multi-predicate join, the resulting record set is the intersection of each related record set from each predicate. In other words, the record set from the 1st predicate is intersected with the record set from the 2nd predicate, and the result is intersected with the record set from the 3rd predicate, etc. Since a Cartesian predicate resolves to the set of all records in the related table, adding a Cartesian predicate to a multi-predicate join does not change the resulting record set; in fact, FileMaker should not waste time forming the Cartesian record set (all records) and performing the intersection.

    Unfortunately, when the target match field of the Cartesian predicate is an indexed field, as is the case with the Cartesian predicate [PortalRefresh x ContactID] in your demo, due to what I believe is a bug, FMP will resolve the Cartesian predicate and intersect the (all records) set with the result of the intersection of the other predicates. To resolve the Cartesian predicate, the (value) index tree is walked to gather the record ids - a possibly time-consuming process.

    On the other hand, if the target match field of the Cartesian predicate is a global field, FileMaker will skip the Cartesian predicate when resolving the multi-predicate join, as I believe it should in a multi-predicate join. This makes the resolution of the join fast - very fast.

    To demonstrate what I have said, (1) leave your Joins in the Example file as is, create many more Contact records - 100K or more Contact records - and exercise the file, noting the speed. Now (2) create a global field in the Contact table and change the target match field of the Cartesian predicate from ContactID to this global field. Now exercise the file and notice the instantaneous speed.
    ======

    I've been investigating how FMP resolves the various predicate-types as well as how multi-predicate joins are resolved from these predicate resolutions. In doing so, I have discovered what I think are some bugs (or at least bad or unexpected behavior). For example, a Cartesian predicate should always return all records in the target table. When a Cartesian predicate is used in a multi-predicate join, the RHS target match field is indexed, and the LHS target field contains any value, FMP walks the value index tree to form the set of matching records. However, if the LHS target field is , the Cartesian predicate returns an empty set, which, when intersected with the results from the other predicates, causes the Join to have no matching records. This is simply incorrect behavior.

    In a nutshell, when defining a Cartesian predicate, whether it is by itself in a single predicate join or one of the predicates in a multi-predicate join, both LHS and RHS target fields should be global fields. Performance is best and empty fields have no effect on the join results.

  • David Jondreau 22/02/2011 5:31am (9 years ago)

    With filtered portals I've been using a lot of selection-->detail layouts. A Class layout with a list of enrolled Students in a portal. Click on a student, set a global var (Coded() with the Classes PK) and show their Enrollment details in a single-row filtered portal next to the student list. That takes a Refresh/Flush though. But not anymore...

    This gets my vote for tip of the year!

  • Josh Ormond 17/02/2011 6:51am (9 years ago)

    <blockquote>SQL/javascript and .net often end up simpler"</blockquote>

    Wow. That's a new one.

    Anyway, isn't the key to being a great developer less about the actual technology being used, and more about finding what satisfies the customer's needs. Whether it be FileMaker, or something else.

    If you are seeing poor performance from FileMaker, you need to identify the bottle-neck. Most often, and I refer to my own experience, poor performance from the solution was a result of the way I design that aspect of the database. While some technologies have benefits that FileMaker doesn't, the reverse can also be said. I've found a nice implementation of both FileMaker native features combined with the power of SQL and Java/Groovy to be incredibly empowering.

  • Daniel Wood 12/02/2011 7:14am (9 years ago)

    Thanks for the comments Paul & HOnza.

    John, were you bullied as a child by a copy of FileMaker? Seriously, your comments appear to be nothing more than a personal vendetta against FileMaker, and completely uncalled for here. Grow up.

  • HOnza Koudelka 12/02/2011 4:56am (9 years ago)

    John, you have probably never had to compete against a serious FileMaker developer.

    Every serious FileMaker developer has at least basic (but usually much deeper) knowledge of SQL and JavaScript and can wisely decide when it is more efficient to use FileMaker and when it is more efficient to use JavaScript/SQL.

    Nowadays, these technologies can work together very well to achieve really valuable results. Lufthansa is one of the oldest examples of wisely combining FileMaker and Oracle to provide high efficiency.

    On the other side, most JavaScript/SQL developers have almost no knowledge of FileMaker (except the fact that it exists and that many people say it's bad).

    Now as a client, who would it be smarter to listen to? Someone who knows only one of the technologies, or someone who knows both?

    If you don't like FileMaker then don't use it. But if you want to be faithful to your customers, you should learn it first.

RSS feed for comments on this page | RSS feed for all comments