14 Replies Latest reply on Nov 20, 2013 12:33 PM by Mike_Mitchell

    Ideas for faster startup


      Howdy all,


      I have a working solution that I'm simply looking for additional ideas for startup speed.

      It is a document management Dbase, where a given user may only be allowed to see certain documents.


      Document Table..........WhoCanSee Table.............Employee Table

      DocumentID----< DocumentID

      People with Access ------- Name/AccountName translator



      The third table (employee) is needed simply because the access definitions are by full name, whereas the account names are not full name.

      I could simplify this by either making access names on the WhoCanSee table by account name, or have login by full name, but neither of those are options for me due to outside restrictions.


      On startup, I do a find from the perspective of the Document Table for the account name in the Employee table...two tables away, FMP Server12 on the cloud. It takes exactly 61 seconds (tried 8-10 times, this is very repeatable).


      I am looking for ideas that I haven't already tried to shorten the startup process. Once in the found set, things work pretty quickly...it is just the initial find that takes a while.


      I have tried:

      - Start with Employee table, GTRR to WhoCanSee, GTRR to Documents...this is also a find (two of them) and takes longer.


      - "Show all records" from within a script that does not have full access privileges (no improvement).


      - Create a separate join between employee and Document with just the DocumentID and accountname (so I'm only looking one table away)...this takes just as long as what is above.


      - Blank layout to start with list of Doc's they can see, then a click takes then to a different layout with the document and the list. But...the list of what they can see only populates if they are on a record they are allowed to see...thus I have to do the find anyway which makes this approach sorta useless.


      Anyone have the great idea that I missed? I'm sure I missed a few options...

      Thanks for your input.

        • 1. Re: Ideas for faster startup
          Stephen Huston

          If your goal is to get a screen in front of users from which they can decide what to do next, I found this to be fastest.


          Have a single-record table (for globals or something, as few fields as possbile in the table definition) and open to that table's base TO layout by default (meaning the file was closed on that layout when last opened in unhosted single-user mode).


          On that layout (which should be in form view, not list view), there will be almost nothing to cache from the server (you are on a WAN with cloud hosting, so caching is a really really big issue).


          On that layout, give the users a menu (buttons) of things they can do next, and let them endure whatever caching delays are necessary only for what they choose after the initial screen pops up quickly.


          Some delays are going to be more painful than others for running scripts -- listing records, etc over a WAN. This option gets a clear screen in from of users super quickly, and they will gradually learn which things take longer than others. No reason for them to endure a delay initially if the programmed startup options may not even be what they need at the time.

          1 of 1 people found this helpful
          • 2. Re: Ideas for faster startup

            Good call, I appreciate it.


            I will likely do "something" like this...but I failed to mention that document access is the sole purpose of logging onto this database...the only choice for the user would be to wait for the find.


            I've gone so far as to try having the jeopardy theme playing in the background, but that slowed it down even further

            Toward what you suggest, I'm looking at the local splash to have other "items of interest to look at while waiting".


            All the same, I'm sure I haven't tried all of the options available to shorten the wait...and remain open to suggestions from the forum.

            • 3. Re: Ideas for faster startup

              Hey Eric,


              My typical solution for mobile speed issues is to index the data that's currently related right into the local table. Huh? Well, in your case this means I would create a field named something like "employeeAccess" within the Document Table.


              The downside is that you suffer obvious data duplication and potential out-of-sync issues. However, the upside is the speed increases due to not having to form a join based on the search. If you're confident in your ability to maintain the data in both locations through your UI, you can realize some pretty nice speed increases.


              What you have to factor into this solution is that when the data is updated through your UI, you must make sure and check the two locations where the data resides. In other words, I don't rely on the employeeAccess for checking access I would simply use it for loading the data. At the point in time the user wants to access a document I would then go through the relationship to check if access was still valid. This would catch times when a document shows up in the results in which access may have been recently revoked.


              In those instances when you get the call like "My boss Fred just told me to use the new sales report - but I don't see it." you provide a nice and simple "refresh" button. You then say, "Did you try clicking the refresh button?". And now your brain says "Ah, just like the web." If a page is updated on the server, but the page you're looking at is older, you just click refresh.


              You can do the same thing within FileMaker. Searching within one table and only a few fields will pretty much always be faster than more tables and fields.


              The authoritative relationship is always used on individual records, but the "locally cached" (although duplicate) data is used for performance and speed.


              This whole solution will require a bit more implementation but it's not as painful as hearing the complaints of your solution always being so slow.


              Matt Petrowsky


              • 4. Re: Ideas for faster startup

                Hi Eric,


                First I would give each user a unique ID (number) to be sure that you get the right employee, (chances might be slim, but better be sure, and a search on a number field is faster then a search on a text)


                So update the WhoCanSee table with the employeeID and on entry, Get the employeeID from the Employee table and do a find on EmployeeID, in the WhoCanSee table.


                Then show the document in the WhoCanSee table using the relation from the WhoCanSee table to the document table.  (document::document)


                You will see a massive improvement in startup time.


                Hope this helps,


                Best regards,


                Ruben van den Boogaard

                Infomatics Software


                • 5. Re: Ideas for faster startup
                  Stephen Huston

                  Do all users really need to see a list of everything they CAN view rather than being allowed to search for an item on their own?


                  My other comments about speeding it up are separate from doing a find. Especially about closing the development file in a form view on a single-record table TO so the file will open there by default when served. This is a lot faster than simply running a startup script to take one there, especially if the last-closed view was a list (which must then cache even if not seen!).


                  If you open to a list view and a Find process, the list caching is a real issue in the delay. But if you default-open to a single-record table (global fields) in Form view, and then let the user choose when or how to move on, the initial screen refresh will be as close to instantaneous as you can get on a WAN.


                  Also, it is my experience that delays after a user chooses an action are more acceptable to the users than delays they experience while waiting for the system to startup. One they know they caused, but the other is the software's (or developer's) fault in their minds.

                  • 6. Re: Ideas for faster startup

                    Thanks for the ideas!


                    I'll try them and report back.


                    Regarding List vs form: What I am showing now is the container field (external secure), and a portal next to it showing the other documents they can see.

                    Clicking on any of the items in the portal does a find for that document.

                    Finding the Doc from the DocID number (clicking on the portal) is pretty quick...as I mentioned before, it is only the initial startup find that is slow (pending trying the ideas above).


                    I hadn't expected it, but it makes sense in retrospect, that the portal itself only populates if the user is sitting on a Document record that they are allowed to see.

                    I'm playing with that one too to see if I can just use a cartesian join to get the portal to populate on a splash page.


                    Again, thanks for the ideas to try!

                    • 7. Re: Ideas for faster startup

                      Another interesting "find"...


                      I created a record that EVERYONE is allowed to see, and then did a find for that record in the opening script, using the Document ID# directly, within the table the layout is based on

                      (The thinking here was to land them on a record that could be found directly rather than do the two-TO-away-search)

                      .......61 seconds to perform the find.


                      I then removed the portal from the layout and ran the same startup...the portal that listed the documents via the "whocansee" relationship.  It now took <2 seconds to perform the find.


                      Put the portal back on the layout and ran the same startup.......61 seconds.


                      It seems that it is not the find, but the populating of the portal that is causing the delay.

                      Any suggestions on that one?


                      @ Stephen: Direct searches for documentID or Name are quicker as you suggest...but few of the personnel remember the title or control number of the documents.  Just about all can find them quickly and easily from a list.  That's why I'm trying to include the list.  Good thought though!

                      • 8. Re: Ideas for faster startup
                        Stephen Huston

                        Yep, it's about record caching.


                        In order to populate your portal, the client machine has to cache all those records from the portal across the network from the server before it can draw and display the portal.


                        That's why I recommend starting on a default layout without anything (or hardly anything) that needs to be cached before the display can refresh for initial startups.

                        1 of 1 people found this helpful
                        • 9. Re: Ideas for faster startup

                          What you say makes sense...but I'm feeling like a newbie again walking into an area where I ain't never played before.  For managing caching, I think my feeling is accurate!



                          - Display the document

                          - Have a way to get to another document

                          - FMP Permission to view documents only when your name is listed

                                *currently this "list" is actually one of many related records, not an actual list



                          - Display the documents you are allowed to see (folks don't remember document titles or control numbers to just do finds)

                          - Avoid the dreaded <<no access>> tags (avoid landing on records you can't see).

                          - Click on the name of the document you want to see and have that come up

                          - Avoid creating a time suck managing lists of people who are allowed to see the document...the related records already exist.

                          - Be faster on startup


                          I understand the default layout, and that does make startup quick as a bunny.  But it does not have Desire #1.

                          Do I understand your suggestion as intended, or did I miss the point of your suggestion?

                          I can land on a layout that is not a document quickly, but when the user moves to a layout that DOES have the list, the caching delay occurs and the annoying teacup shows up.


                          Again, I appreciate your feedback.

                          • 10. Re: Ideas for faster startup

                            Jumping in here ...


                            As Stephen mentioned, the record caching delay is occurring because FileMaker has to load the entire related set (all fields) before it can display the portal. You can speed this up, as you discovered, by eliminating the portal, or making the payload smaller.


                            The former can be accomplished through the use of a value list, from which users can choose the related document. (A value list will populate far quicker than an entire related set.)


                            The latter can be accomplished by either removing unnecessary fields from the related table, or using a one-to-one relationship to a second table that contains only the necessary fields to display what you need in the portal. A lot more work, obviously, but it will improve your load time.





                            • 11. Re: Ideas for faster startup
                              Stephen Huston

                              Mike has an excellent idea of providing a list of available documents via a value list rather than a record list. That should remove the need for caching much of the record data that your previous system was pulling across the WAN.


                              When you do take them to the record requested in the value list, be sure you are in a Form View layout rather than a list view. List views require caching of records that are not even visible in the list!

                              • 12. Re: Ideas for faster startup



                                One of the big points at this year's DevCon was the concept of "narrow" tables. As Stephen noted, and you've discovered, if you display a portal, or access a value list, the client has to load up all of the stored data for all of the records. However, if you load up a table that ONLY contains Invoice Numbers (for example) and links to the "full" record, and that narrow table in the portal, or value list, the display can be dramatically faster.


                                The speed increase is directly proportional to the amount of data in the stored fields of the "full" table. You mention "documents" in your target table. If this means large stored text fields, then this is likely the cause of the 60 second delay and a narrow table will help a lot.


                                There is a little maintenance involved in this, but I've implemented it in several places since DevCon and found it pretty easy to set up. I also typically use a Server script to double check the maintenance of the narrow table. NOTE: I've found it easier to ADD a narrow table to FMP, than to turn the existing table narrow and move the actual data fields to a new fat table.


                                As iOS and Web access become more common, these kinds of techniques are becoming more important.


                                Bob Gossom

                                • 13. Re: Ideas for faster startup
                                  Stephen Huston

                                  Excellent point, Bob.


                                  I inherited a file system which needed WAN access and had over 150 tables, some more than 700 fields wide! Trying to keep the system from ever landing on one of those -- especially in list view -- for WAN users became a chore.


                                  One of the rules of thumb I heard a few years back at DEVCON was to start rethinking one's table structures as you approach 35–50 fields. Since then, I have rarely let a new table exceed 36 fields. It's not a magic number, but a great reminder.


                                  Creating a related record can be as easy as placing a non-portal field with one of the required related fields on the main record layout, and voila! -- the related record gets created as the field data is entered (if one sets up the relationship to allow record creation, just as it would in a portal).

                                  • 14. Re: Ideas for faster startup

                                    Funny you should mention 36 as a "magic number". Ray Cologon, in one of his presentations at DevCon 2012, explicitly said that, as a rule of thumb, if a table is more than "about three dozen" fields wide, it's a "red flag" that something might be wrong in the data model.


                                    Great minds think alike.