10 Replies Latest reply on May 27, 2014 9:56 AM by nihmbrisby

    Flush cached join results vs Daniel Wood's cartesian refresh

    nihmbrisby

      Title

      Flush cached join results vs Daniel Wood's cartesian refresh

      Post

           So I'm trying to adapt/learn from starter solution scripts and I'm pretty happy with the results so far.  One concern of mine however is the frequency of "Refresh Window [flush cached join results]".  I've read on several blogs/articles that this can be a performance killer on a WAN.  In this blog post (and subsequently on many other blogs/posts) Daniel Wood outlines a method of using cartesian joins (a method which, seemingly, gets an improvement in the comments (see Jason DeLooze's comment) to only refresh the relationship in question.

           Now I'm still very much trying to get a handle on the basics of what needs refreshing and why, but considering that I'm currently adapting filemakers scripts, it would be great if I could get this issue right as I implement, rather than having to go back and substitute all those refresh triggers with this Mr. Wood's method.  That blog post, despite the bugged dates on the comments, appears to be over 3 years old (FMP 11).  I've found many posts on fmforums and elsewhere in the meantime regarding this issue, though most remain unanswered/inconclusive. 

           Is anyone out there familiar with this issue?  Is this cartesian join method still used in fmp solutions?  Or are there any known issues with it?  I'm particularly inclined to think refresh is still an issue over WAN's since Filemaker's solution only run the refresh step on desktop devices and not ios/web (but that's only a guess- maybe the only use it on desktops for other reasons).

           Many thanks in advance.

        • 1. Re: Flush cached join results vs Daniel Wood's cartesian refresh
          nihmbrisby

               I usually search before I post, but I neglected to here.  Just yesterday someone posted on this very subject, remarking that flush cache was taking a big toll on their performance (and including a mention to the same blog post:

               http://forums.filemaker.com/posts/131f530b42

               Additionally I just successfully implemented (I think) Mr.  Woods method in Filemaker's starter scripts in place of their refresh script.  First I disable the script and sure enough- portals weren't refreshing properly.  Then implemented the cartesian join as described in the article and the portals are working fine! 

          • 2. Re: Flush cached join results vs Daniel Wood's cartesian refresh
            philmodjunk

                 Here's the technical info on why the Cartesian Join method is usually the better option:

                 Refresh Window with that "flush" option completely refreshes everything associated with your current layout and current record and/or found set.

                 The Cartesian Join trick forces an update of just elements associated with the portal based on that relationship. That's a much smaller subset of the total "number crunching" and data transmission from host to client needed in order to execute a "refresh window [ flush..." step.

                 I have found that the Cartesian join trick is not always required. In some cases, just using a local field in place of a global field or variable in the portal filter expression is sufficient to get a portal that updates when data in that field changes.

                 PS: if Refresh Object could be used to force a filtered portal to update, we'd have a third and potentially better option to the above two methods. But my tests thus far seem to indicate that this new script step can't update a filtered portal to show different records when the portal filter now references different data in it's calculated expression.

            • 3. Re: Flush cached join results vs Daniel Wood's cartesian refresh
              nihmbrisby

                   Thanks Phil.

                   "But my tests thus far seem to indicate that this new script step can't update a filtered portal to show different records when the portal filter now references different data in it's calculated expression."

                   This is exactly my situation at the moment.  Most of my layouts have a portal for selecting a record from another table occurrence on the other side of a join table.  I'm imitating what filemaker does in their starter scripts- a portal to the distant layout accompanied by a search box.  The portal is filtered by creating a list out of name & company (if the distant table is clients for instance), the doing a pattern count with that and the term in the search box.  The FMP devs use flush cache to refresh it, but the cartesian method works to.  

                   I'd hate to think this particular function could cause a slowdown for the user once the database is deployed on a remote server, but I also think the built in dropdown + value list sucks when you have 1000's of records.  I'm still looking around for other solutions if anyone has any suggestions.

              • 4. Re: Flush cached join results vs Daniel Wood's cartesian refresh
                philmodjunk

                     Here are two demo files of alternative search methods that I use. The "search portals" use the cartesian join method to update and I've since found that the method also works well in FileMaker Go on an iOS device.

                     Several methods illustrated all in one file: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7 (open from FileMaker's File menu if you are using FMP 12 or newer.)

                     An auto-complete list based on names that links records by ID numbers and which correctly handles having multiple records with the same exact name:

                     FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                     Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                      

                • 5. Re: Flush cached join results vs Daniel Wood's cartesian refresh
                  nihmbrisby

                       Those files were great, thanks very much.  I have a question about EnhanceValueSelection.fp7 (which was really excellent by the way).  

                       In your Cartesian relationship you set gSearchField --x-- ProdRefresh.  ProdRefresh is the following calculation- Prodrefresh = Invoice::gSearchField.
                        
                       My question: Why does Prodrefresh need to be this calculation? Do both sides of the cartesian product need to be refreshed or just one?  And, if just one, would that enhance performance?
                        
                       I tried 2 changes.  First I set gSearchField---x---gGlobal where gGlobal is an empty text field that uses global storage.  This is the method recommended (if I understood him correctly) by a commentator on the weetbicks blog I mentioned.   I just tried it out of curiosity- not because I have any idea of whether Mr. DeLooze is right/wrong/neither.  Anyways- as best I could tell it worked the same as gSearchField --x-- ProdRefresh.
                        
                       Second I set gSearchField---x---randomtext where 'randomtext' is just an empty text field.  This is the way I understood the method as outlined in the original blog post.  This also seemed to work (the portals refreshed)

                       So three possibilities:
                       gSearchField --x— ProdRefresh
                       gSearchField---x—gGlobal
                       gSearchField---x—randomtext

                       I’d greatly appreciate it if you could discuss any difference in outcome or performance from these three approaches when a solution with at most around, say, 20 thousand of records is deployed across a WAN.

                       Thanks!

                       PS. you triggered the refresh script on modify (filemaker's solution triggers on save) which was very nice- it updates as you type! I much prefer your way, but am I correct in assuming that this might be more taxing for the network connection?

                        

                  • 6. Re: Flush cached join results vs Daniel Wood's cartesian refresh
                    nihmbrisby

                         I reread the comments from the Daniel Woods blog and saw what may the explanation for your method?  Am I right in think that the important thing is that neither field be indexed, and since ProdRefresh is an unstored calculation, filemaker won't actually like perform the calculation for every record in order to create the Cartesian Join.  Er.... sorry if that doesn't make sense as I'm not 100% what I'm even saying.

                         mark-

                    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.

                    • 7. Re: Flush cached join results vs Daniel Wood's cartesian refresh
                      philmodjunk

                           What you've quoted here looks correct.

                           I worked out the details to that demo file sometime ago in collaboration with another forum participant. We kicked the file back and forth several times before coming up with the simplest possible combination of steps that still supported getting the portals to update with each key stroke.

                           Since developing that demo file, I've discovered that it also makes a difference whether or not the search field has global storage. I haven't tested it with this demo file, but filtered portals update more smoothly when their filter expressions refer to a non-global field instead of one with global storage specified. So there may be yet another option out there that works with a search field that does not have global storage specified.

                      • 8. Re: Flush cached join results vs Daniel Wood's cartesian refresh
                        nihmbrisby

                             Thanks Phil- that totally has me questioning why I'm using a global variable in the first place! (answer- cause I just copied Filemaker's script).  So I replaced the global with a non-global.  After all- it's a field attached to just the current record which other people can't work (cause it's locked) so what's the harm?  And I'll clear the field with an on exit trigger on the popup.

                             Having made this switch- the filtered portal updates without need of the whole cartesian refresh thing.  Am I missing something or is this definitely the way to go?  Am I getting into trouble by making the search box non global??

                             Thanks.

                        • 9. Re: Flush cached join results vs Daniel Wood's cartesian refresh
                          philmodjunk

                               There are two advantages to using a global field and you've already ruled out one of them.

                               a) With a global field, two different users can use the same field while on the same record to bring up different lists of matching records in the portal. For some types of "navigation" implementations of this technique--where the object is to find a completely different record, this can be very important. But if you are only using this tool to edit the current record, you need to lock out the user anyway.

                               b) The other advantage is that you can specify data in the search field and see the same list of values in the portal no matter what record you change to on that layout. That may or may not be useful to you as it depends on  how you need this to work.