14 Replies Latest reply on Jan 23, 2015 5:15 PM by Mike_Mitchell

    File takes long time to open or close

    justinc

      I am having some odd behavior with a file I created.  It wasn't doing this before...but I didn't really investigate the issue when it first happened to be able to narrow down what I changed that made it happen. 

       

      So, this file:  it's a local (i.e. non-hosted) file built in FM13.  It is separation model, and has 3 other files associated with it.  I run this file from a Macbook Pro with an SSD and it takes 20-25 seconds to open.  The other odd behavior is that it ALSO takes 20-25 seconds to fully close.  By that I mean that you can close the last window and the 'Quick Launch' screen will show up almost immediately...but then it beach-balls for 20-25 seconds before you get control back.

       

      This problem crops up before any scripts are executed.  I do have an 'on open' trigger on the file, and the delay happens before that script fires up in the debugger.  The initial layout is based in a simple table with 1 record.  The layout doesn't have any fields on it, but it does have some conditional formatting and pop-ups that have fields.  However, I ran a test where I removed all objects from the layout and it still took 20 seconds to open.

       

      On close, I also have a script trigger for a script that simply navigates back to the opening page, so that it will always open on that layout when opened.  (It's my understanding that a file will open to whatever layout it was on when it was last closed locally, and then it will navigate to whatever layout is defined in the 'File Options', and THEN if you have a script trigger it will run that.)

       

      Do the fields in the pop-ups have to get loaded?  It does appear to be 'opening' all the other files (perhaps because the pop-ups have some meta-data from those files in them); when I go to 'Window ->Show' all 3 other files are shown in black, not gray.  But the relationship graph from this opening layout is one-step to these meta-data tables in each file, and these tables only have one record each in them, on fairly narrow tables of mostly timestamps, dates, or labels.

       

      What else is FM doing when it opens a file?

       

      Thanks,

      J

        • 1. Re: File takes long time to open or close
          wimdecorte

          Create an empty table (no fields, so no records) and create a blank layout based on that table.  Set the file up so that it opens on that layout.  That makes sure that FM does not need to load anything at all until your script takes the user to the starting point.

           

          If that does not solve it:

          Remove any and all plugins you have installed and see if that makes a difference.

          • 2. Re: File takes long time to open or close
            Mike_Mitchell

            Another thing to try: Since FileMaker has to cache all joins on the Graph when it opens a file, remove the TOs for the external files (perhaps one at a time) and see if that helps. There might be something there in the joins that's causing the slowdown.

            • 3. Re: File takes long time to open or close
              justinc

              Hey Wim,

                   Probably should have thought to try this myself.  I thought that my removal of all objects would suffice.  Apparently not.

               

              I made a new table (0 records, 0 fields) and made a layout for it.  I closed the file while on that layout (carefully bypassing my 'on close' script) and then opened the file.  Boom, instant open.  And it even closes instantly.

               

              Great, so I have a solution...but it's a bit clutter-y.  So what's going on in the other table that causes it to bog down so much?  It would be helpful to know so that I can avoid the problem in the first place. 

               

              --  Justin

              • 4. Re: File takes long time to open or close
                justinc

                Thank Michael,

                     I figure that it is something along the lines of what you are talking about that is the real root of my problem.  (See my response to Wim, above).  But if I'm not showing any data, is it still evaluating the relationships?

                 

                --  Justin

                • 5. Re: File takes long time to open or close
                  wimdecorte

                  carefully inspect the layout that you were opening on:

                  - any unstored calculations?  If so, scrutinezie them, they are always expensive so figure out if you need them or if you can set them statically as part of some scripted workflow

                  - portals?  FM loads all their data so if you have one or more on there that shows a lot of data then you are shooting yourself in the foot

                  - related fields with complex / sorted relationships?

                  • 6. Re: File takes long time to open or close
                    DavidZakary

                    Check your file references as well. I've had files get glacial when there are file refs that can't be resolved or are looking over a network.

                    • 7. Re: File takes long time to open or close
                      Mike_Mitchell

                      Yes, it is. All joins have to be cached, whether or not anything is showing on the current layout.

                      • 8. Re: File takes long time to open or close
                        justinc

                        So I used Wim's suggestion to test for the problem and resulted in a file that worked great:  added the empty table and opened the file to that table.  So, to work backwards from there to check the relationships, etc, I made a copy of my file.  In that file I then switched it back to the using the original-problem-table-layout and was going to start removing relationships.

                         

                        But I didn't have to.  It worked great after putting the original layout back into place.  Ok...

                         

                        So I removed the empty table and its TO, removed the layout as well...still works great.

                         

                        I went back to my original file and did the same thing:  removed the new empty table I had created, remove the TO, removed the layout...Boom, works great.

                         

                        Closed FMPro, opened the app, opened the original file...opened just great.

                         

                        So...I am back to how I had things set up when I started this thread, but now it works as I would expect it to - very fast open/close operations.

                         

                        What the heck? 

                         

                        --  Justin

                        • 9. Re: File takes long time to open or close
                          justinc

                          Hey Mike,

                               I was hoping you could give a more in-depth explanation of what FM is doing in with caching the relations.  I am a bit confused by it.

                           

                          Our solution has a lot of relations to lots of tables, and you are saying that it has to cache all of these when the file opens?  After fixing the issue of this one relationship, the file opens much better.  Is it a case of where it just has to cache relations of any neighboring TOs, to whatever the layout it is currently on (and not all of them across the file)?

                           

                          While we fixed the long delay on the file opening process, this "Records remaining to sort....5210..." dialog still crops up on other layouts.  I bet it is really the same issue as before (this relationship caching issue); but I haven't investigated further yet.

                           

                          Thanks,

                          Justin

                          • 10. Re: File takes long time to open or close
                            Mike_Mitchell

                            Hm ... possibly, but I don't think so. As I understand it, caching is done when the file first opens, not when each layout opens. Is there anything on the layout that might require sorting - portal, Script Trigger, anything like that?

                            • 11. Re: File takes long time to open or close
                              justinc

                              Quick recap of the structure:

                              TableA <- all ->  Table B <- ID [sorted] -> TableC

                               

                              TableA has a cartesian relation to everything in B; B is related only to specific records (based on ID) in C; the C side of the BC relation is sorted.

                               

                              In our original problem (before we half-fixed it), we demonstrated that we could fix it in one of two ways:

                                   *  Delete the relationship between AB

                                   *  Remove the sorting option on relationship BC

                               

                              The layout that the file opened on was based on TableA, with no fields on it at all, but it was still on an actual record.  So I was surmising that it was doing what you were saying - caching related records ahead of time, and apparently multiple hops away.

                               

                              But now that we have removed the relationship AB, the file opens fine, but then on other layouts we will get the same sorting dialog that used to show up on file opening.  As if it were now only caching those records when it hit a layout that had that relationship (i.e. something based in TableB).

                               

                              Now, I still haven't really dug into this.    There may be something else entirely going on here.  I am just trying to understand this caching mechanism:  exactly what does it mean to 'cache a relationship'?  How far across the graph does it stray? etc.

                               

                              --  Justin

                              • 12. Re: File takes long time to open or close
                                Mike_Mitchell

                                You’ve answered your own question. You just don’t realize it.

                                 

                                A Cartesian join joins the current record to every other record in the child table. Hence, to establish what records are in the related set, FileMaker has to download the entire record set of the child table. Delete that relationship, you delete the need to do that.

                                 

                                Similarly, in order to sort a relationship, FileMaker has to download the entire related record set, because it can’t sort it unless it knows what it’s sorting. That’s where your sorting dialog is coming from; it’s the time it takes to download and sort that entire set.

                                 

                                You stated that once the Cartesian join was deleted, you only got the sorting dialog when you navigated to “other layouts”. Well, then it would seem the record downloading behavior doesn’t happen until it’s called for. The joins themselves are established, but the records don’t get downloaded until they’re needed.

                                 

                                Once the records have been downloaded, they remain part of the local cache for some period of time. I don’t know how long, but certainly for as long as they’re needed.

                                 

                                Now, what causes the downloading process to be slow? Primarily, the width of the tables. The more fields you have in a table, the longer it will take to download each record.

                                 

                                The basis for the “caching of relationships” is a talk given by Ray Cologon at DevCon in 2011. He didn’t go into a lot of detail about the low-level details. So I can’t tell you a precise definition, or how many hops across the graph are resolved. He just said all joins had to be cached as a caution against a Graph that was too busy. But it seems your problem is coming more from how your tables are structured than necessarily from the join caching process, just based on your own experimentation.

                                 

                                Cartesian joins are known to be slow. Sorting relationships is also a known performance bottleneck. And apparently, you have some “wide” tables in your structure somewhere that are slowing things down.

                                • 13. Re: File takes long time to open or close
                                  justinc

                                  Definitely have some wide tables, but its the one in the middle (b) , not the end resulting one (C).

                                   

                                  And the Cartesian relation isn't defined in the relationship itself, it is created by a constant1=constant1 relation (where both sides have all records set to 1, of course).  So...Cartesian in effect, but not in name. 

                                  • 14. Re: File takes long time to open or close
                                    Mike_Mitchell

                                    The one in the middle is the problem, then.

                                     

                                    And even if it's not a "true" Cartesian join, all the records still have to be downloaded. So that's where your bottleneck is.