13 Replies Latest reply on Sep 16, 2014 8:49 AM by jormond

    15 minutes to open a file?

    JStars

      Been working with a client on a file on a server. Yesterday we imported 30,000 records to one of the tables. The file opens to a layout built on a one-record table with no related records displayed on it. There is no opening script and no script triggers are attached to the layout—yet it now takes over 15 minutes for the file to open. The same file opens in 2 seconds on my local machine. What could be going on?

       

      Jonathan Stars


        • 1. Re: 15 minutes to open a file?
          Mike_Mitchell

          Jon Thatcher went over a very similar situation in one of his DevCon presentations this year. Look for:

           

          1) Aggregate calculations (Sum, Count, etc.) across the found set or across related records, especially where they cascade (i.e., where there's more than one level).

           

          2) Summary fields.

           

          3) Lots of relational joins that go back to the current table.

           

          This is especially true if the table in question has many fields in it (say, more than 100 or so). It's a bandwidth / latency issue, because whenever FileMaker loads a record, all fields have to be downloaded (with a few exceptions). More fields = more data = more time. If there are any aggregates on the layout, then ALL the records the aggregate needs will have to be downloaded so the calculation can be completed.

           

          HTH

           

          Mike

          • 2. Re: 15 minutes to open a file?
            JStars

            Well, here's the thing (or things): There is only one record in the opening table. And that table—although it connects to other tables—had no fields from those other table on the opening layout. It's essentially a layout that announces the file name and has a few buttons to direct users to other areas of the file.

             

            I duplicated the file and removed the 30k records and put it up on the server, where the two files now run side by side. The smaller file (few hundred record, and again, none of those records have anything to do with the opening table) opens instantly, the bigger file takes 15 minutes.

            • 3. Re: 15 minutes to open a file?
              Mike_Mitchell

              The fields don’t necessarily have to be on the layout. When the file opens, FileMaker evaluates all the joins to establish the cache. What does the Relationships Graph look like for this file? You say it “connects to other tables”. What kinds of joins are involved? Where are these 30K records located?

              • 4. Re: 15 minutes to open a file?
                jormond

                How is your "opening table" set? Via the Go to layout... in the file options?

                • 5. Re: 15 minutes to open a file?
                  JStars

                  The one-record table feeds a few images and and text for buttons to other tables in the file. The graph is not over-complicated. (I know because I've built some very complicated graphs!) And I've built served files with hundreds of thousands of records before and never had this problem. Once the file is open, everything moves as quickly as it ever did. Even when I go to the list view of the 30,000 records, I can scroll right through them at normal speed.

                  • 6. Re: 15 minutes to open a file?
                    JStars

                    I've tried setting the opening layout a couple ways since this issue came up, including File Options and setting it with a script. The screen is blank for the entire 15 minutes before it even displays a layout. And to add a little explanation to Mike Mitchell's quesion, the graph did not change. Only the number of records in one of the tables. And that table does not have a portal on the opening layout.

                     

                    J **

                    • 7. Re: 15 minutes to open a file?
                      jormond

                      When the file first opens, it opens to the last layout that was open when the file was not hosted. Even if you change it, or use a script, or Go to layout in file options.

                       

                      Unhost the file, create a blank layout, attached to a blank table, and see if it helps.

                      • 8. Re: 15 minutes to open a file?
                        JStars

                        I think we're onto something. I added a table connected to nothing in the graph and a layout based on it. The file opened quickly—until I switched to the old opening layout.

                         

                        So I made a copy of old opening layout (the one-record table) and took eveything off it. Then I added the background image field. No problem. Then I added another field and it went into the spinning Mac beachball.

                         

                        The odd thing to me is, why doesn't this cause a problem when there are just a few hundred records in the data table? And after that, how can I get this speeded up, because nobody can work in it this way.

                         

                        J **

                        • 9. Re: 15 minutes to open a file?
                          JStars

                          SOLVED:

                           

                          There was a cross-table calculation that filled a background image field on the opening layout. I had incorrectly assumed it was a static image. Once I "flattened" the calculation, everything went back to normal. Joshua's post was particularly helpful. At his suggestion I added a completely empty layout with no relationships and closed the file while viewing that layout when it was not being served. That proved there was something on the original layout causing the problem. Then I added one field at a time (there were only 4) until the problem returned.

                           

                          Thanks for the help.

                           

                          J **

                          • 10. Re: 15 minutes to open a file?
                            Mike_Mitchell

                            Then it’s definitely a caching issue. The data are being downloaded from the server to the client. Once there, you don’t have a problem; it’s the initial load that’s taking forever.

                             

                            There’s a reason FileMaker is loading all 30,000 records to the local client when the file opens. Most likely, there’s an aggregate somewhere that requires it.

                            • 11. Re: 15 minutes to open a file?
                              ch0c0halic

                              Most likely FMP is not doing what you think its doing.

                               

                              Is this file related to any other files? After it opens are there any other files open in the Hidden state?

                              Menu: Window -> Show Window -> (my file)

                              The parenthesis around the file name indicates it is hidden and has never had an open window. And very likely it was opened through a relationship. This would indicate the layout used on-open is actually showing related fields.

                               

                              You said "The file opens to a layout built on a one-record table and no related records." Does that mean you are using the File Option "Switch To Layout"?

                               

                              Let me try and explain.

                              FMP must go to the last layout the file was on when closed while locally accessed (hosted by same FMP app that opened it) in order to establish context. Even though an on open first window script trigger or Switch to layout is set in the File Options they are performed only - after - the "on last Closed" layout establishes context. The file is most likely opening to a non-blank layout with related fields displayed, which would be why it takes 15 minutes to open over the WAN. Could also be a calculation using a related field.

                               

                              My guess is it only takes 2 seconds locally because there is no network latency when opening related file(s).

                              • 12. Re: 15 minutes to open a file?
                                DavidJondreau

                                Some of the advice on this thread is accurate. It's possible an external file reference that is slowing things down.

                                 

                                An opening script won't bypass the last "locally opened layout" issue. However, Switch to Layout... will. It can be safely used to avoid the file reference issue.

                                 

                                This can be demonstrated with a simple test.

                                Create a file that with two layouts, one based on a local table, another based on an external data source.

                                Add a script that goes to that layout. Have that script triggered by the OnLastWindowClose option. This ensures the last layout is always the related one.

                                When opening the file, it will go to the related layout and the external file will show hidden (under Window-->Show Window in parenthesis).

                                Add a Switch to Layout...option to go to the local table layout.

                                When opening the file, it will go to that local layout and there will be no other open windows. The external file was never accessed.

                                 

                                I've attached two sample files.

                                • 13. Re: 15 minutes to open a file?
                                  jormond

                                  In this case it sounds like it was a calculation field and container issue.

                                  JStars wrote:

                                   

                                  SOLVED:

                                   

                                  There was a cross-table calculation that filled a background image field on the opening layout. I had incorrectly assumed it was a static image. Once I "flattened" the calculation, everything went back to normal. Joshua's post was particularly helpful. At his suggestion I added a completely empty layout with no relationships and closed the file while viewing that layout when it was not being served. That proved there was something on the original layout causing the problem. Then I added one field at a time (there were only 4) until the problem returned.

                                   

                                  Thanks for the help.

                                   

                                  J **