1 2 3 Previous Next 32 Replies Latest reply on Nov 21, 2013 5:44 AM by taylorsharpe

    Developer guidelines for optimum WAN performance

    davehob

      I've been watching the session recording from last year's DevCon, entitled "Server and WAN Performance Under The Hood". In it, Jon Thatcher refers to other sessions in last year's DevCon and earlier, which concentrate on performance optimisation at the application end, but these don't seem to be available.

       

      I guess what I'm after is a list of "do's and don'ts", the essentials of developing in FM (11 at the moment, but soon to be 12), keeping WAN performance as zippy as possible. If you know of any such resource, I'd be very grateful.

       

      Dave.

        • 1. Re: Developer guidelines for optimum WAN performance
          taylorsharpe

          People try to make this too complicated.  It is pretty simple, if you have to bring a lot of info over, then it will take a long time.  Unstored calculations are the biggest issue, but so are things like lots of unnecessary relationships or index fields that do not need indexed, etc. FM12 does a better job of doing more of the processing on the server and sending less across the network, but it is not what I consider a major improvement.  If you have unstored calculations in static tables, have a batch script run at night to calculate the results and store them as an indexed field.  If you want to get really tricky, you can get into processing things on the server that you don't want to do on the client with things like FMPRC. 

           

          The big problem with FileMaker is that it makes it easy to do things that most SQL database developers would not do such as lots of unstored calculation fields.  That is both a benefit and an achiles heel.  FM databases generally perform fairly well compared to SQL databases if you set them up the same way.  Basically, if you use the standards that most enterprise SQL database developers use, they apply the same way to FileMaker.  Being a rapid application development tool, we often develop on FileMaker not using best database design standards, which is why FIleMaker sometimes has a bad reputation in the enterprise realm.  Then again, a SQL database can be designed poorly too. 

           

          By the way, Dave, are you coming to Devcon this year?

          1 of 1 people found this helpful
          • 2. Re: Developer guidelines for optimum WAN performance
            davehob

            Thanks very much for this, Taylor.  Unstored calcs are indeed a problem for my application - so I guess I need to make it a bit of a golden rule to avoid them (which I'm sure isn't always possible).  I also have a lot of relationships which will, I hope, be unnecessary when I bite the bullet with FM12 (or maybe 13 if it's with us soon), as "Execute SQL" seems really useful.

             

            Re. DevCon - I'd LOVE to attend one of these days, but, working for a cash-strapped charity on the wrong side of the Atlantic, I'm afraid I can only dream of doing so! 

             

            Thanks again,

             

            Dave.

            • 3. Re: Developer guidelines for optimum WAN performance
              jormond

              Dave,

              I really comes down to knowing what FileMaker needs to perform any give task.  What does it need to pull data down to the client to view a record, perform a calc, execute a script.

               

              I would highly recommend Skeleton Key's class on WAN Performance.  In it, Mark Richman takes an actual file ( yours, and 3 others from the class of 4 people ), and shows you where bottlenecks may be...and runs a list of things that affects what happens over the network.

               

              In general, here are a couple of things that I do to increase WAN performance:

              • On startup - use a blank layout, connected to a blank table with no records.
                • The first thing FM does on opening the database is "Show All Records".
              • When switching to another layout to perform a find, go to Find Mode FIRST, then switch to the other layout.
                • This will prevent FileMaker from having to download that initial set of records to display.
              • Remember that FileMaker caches all records that it needs to touch.
                • So if one record has a calc that has to evaluate 500 other records, FM has to download the entire 500, in their entirety to finish the calc.
                • No that those 501 records are cached, FileMaker has to maintain them with updates.
                • Another user changes a value in one of those 500 records, FileMaker now sends it to your cached copy.  Creating network chatter ( this may be necessary, or not, you have to decide that one ).
              • Use a program to see the packet data being sent across port 5003. Mark in his class provides some tips on doing this, and also a FM file to analyze the data.
                • Very quickly, you will get to see how much data is pullling down on the initial call...and on subsequent calls.  And also, when you are not actually doing anything.
              • When you host the file, make sure the last layout you are on ( which becomes the default startup layout ) is the reference layout in the first bullet.
              1 of 1 people found this helpful
              • 4. Re: Developer guidelines for optimum WAN performance
                taylorsharpe

                Dave, we'll miss you at Devcon.  I hope someday you'll be able to make it and maybe next year will be on the East Coast like like last year and you'll be more likely to attend. 

                 

                ExecuteSQL is a wonderful tool.  There are many times in FileMaker that I have had to create a relationship for one purpose or one calculation.  FileMaker has to think through all relationships when opening up even if you aren't using it.  So a lot of relationships sure slows down the opening of a FileMaker file.  But with ExecuteSQL, I can perform searches that are not based on any existing relationship, thereby simplifying the relationship graph.  Obviously you need to use relationships where they are appropriate and you'll be using that relationship for many purposes.  But ExecuteSQL is helping me get rid of a lot of relationships that were only needed for a single report.  Plus there are some things in SQL that can be done that are just harder to do the FileMaker way.  This is one of the best features FileMaker has added in a long time! 

                • 5. Re: Developer guidelines for optimum WAN performance
                  davehob

                  Wow, thanks Joshua for taking the time for this very helpful reply.  The Skeleton Key session looks useful (beyond my budget for now, but definitely one for the wish-list) - I love the idea of somebody who really knows what they're doing taking a look at my file and telling me where I've gone wrong.    (Then, in the second week... No, it's probably not that bad.)

                   

                  Glad that it's not all complex stuff, as Taylor implied in his post.  Your first 2 bullets, for example - hardly rocket science, but easily overlooked.

                   

                  Thanks again,

                   

                  Dave.

                  • 6. Re: Developer guidelines for optimum WAN performance
                    taylorsharpe

                    One of the best comments Josh has is about switching layouts.  FileMaker is notoriously slow at switching layouts.  I've found to speed things up, if all I need is a record elsewhere in an unrelated table, I do it in ExecuteSQL so that I can avoid switching layouts.  I also noted that I often uses the suggestion Josh mentioned about going to Find mode before switching layouts so it doesn't load all the records of the found set again.  That alone sped up some loops I have by 100% or more. 

                     

                    There are benchmark programs like Base Elements to do some testing of how fast various script steps take and you might want to look into it.  Also, they give away a free plugin that lets you test script step speeds down to a very small fraction of a second whereas the FM timestamp just goes down to seconds.  Plus the free Base Elements plugin is great for interacting with web or xml data as well as file manipulation.  The price is right:  Free!  Thank you Goya Pty for the plugin!

                    • 7. Re: Developer guidelines for optimum WAN performance
                      jormond

                      Taylor is right with what he said...sometimes people do just make it too complicated.  It's really about thinking about what FileMaker is doing.  What does it reach out to the server for and when.

                       

                      @taylorsharpe - agreed, ExecuteSQL has been beneficial is a lot of ways.  As I watch the amounts of data moving when I send a request...it's sometimes surprising to see how much is moving. Still interpreting the data and testing.

                       

                      Overall, the timing of how long things take is a good thing to watch. It is an easy identifier.  The benefits of watching the amt of data moving, is this: No matter what technique you use, it will always be faster if FileMaker has to move less data over the network.  Both benchmarks are important for development over WAN.

                      • 8. Re: Developer guidelines for optimum WAN performance
                        abridgesolution

                        Hi Joshua,

                         

                        Thank you for your detailed explanation!

                         

                        Am I to conclude that Filemaker processes all unstored calculation fields of the underlying table whether they are displayed on the target layout or not?

                         

                        How does that relate to unstored cal fields  of other tables within the file whether related to said underlying table of the target layout or not?

                         

                         

                         

                        Thank you

                         

                        Rob Bloomfield

                        • 9. Re: Developer guidelines for optimum WAN performance
                          thebridge

                          Re: Developer guidelines for optimum WAN performance  

                           

                          Hi Joshua,

                           

                          Thank you for your detailed explanation!

                           

                          Am I to conclude that Filemaker processes all unstored calculation fields of the underlying table whether they are displayed on the target layout or not?

                           

                          How does that relate to unstored cal fields  of other tables within the file whether related to said underlying table of the target layout or not?

                           

                          **** sorry for the re-post but original was never sent out via email

                           

                          Thank you

                           

                          Rob Bloomfield

                          • 10. Re: Developer guidelines for optimum WAN performance
                            jormond

                            While there are a few exceptions to this...the general idea is that FileMaker needs to download all the details of a record, and all relevant records to perform a calculation.

                             

                            Think of it like each record being a piece of paper.  If you needed to grab a calculation and sum up the totals on all invoices for Customer X that have a balance.  What piece of paper would you need?

                            • Depending on your file structure...you may need to just grab all of the papers that don't have a stamp "paid" on them.  So you flip through the file cabinet, pull out all the invoice papers for Customer X that don't have a "paid" stamp on them.  And you bring them back to your desk.  That is essentially what FileMaker is doing.
                            • Now, if your company doesn't mark each invoice as "paid" on the invoice itself, but you keep all the payment records in a separate drawer, now you need to pull all the Invoice papers, and all the payments that belong to the stack of invoices you filtered out. And bring those back to your desk, and calculate how much has been paid on each invoice.

                             

                            That's the general idea.  I may not have explained that exactly the way it happens, but you get the idea. Any record that FM needs to access to figure out the result needs to be brought back to the desk to be processed.  Now, in some cases, FileMaker may already have the updated records in the local cache file, so it doesn't need to move the data again.

                            • 11. Re: Developer guidelines for optimum WAN performance
                              thebridge

                              Joshua,

                              Understood on the parent and related records and your analogy, but was wondering about any unstored cal fields in non-related tables within the same file. I would think that these would be ignored unless referenced/needed by the original table or related records involved. and that seems to be what you are saying as well, correct?

                               

                              Thanks again

                              Rob

                              • 12. Re: Developer guidelines for optimum WAN performance
                                Mike_Mitchell

                                Rob -

                                 

                                The short answer is no. FileMaker does not process unstored calculations until they are needed (for example, displayed or called by another calculation). They are not downloaded from the server because they're processed by the client.

                                 

                                However, what Josh is pointing out is that if you create an unstored calculation that references multiple records, then the client will fetch all records it needs for that calculation to be processed. This is why aggregate calculations (like Sum, Min, summary fields, etc.) are absolute performance killers over the network. They have to fetch all records they reference for them to figure out what their values should be. And when the client requests a record, it gets all of it - all fields, with a few notable exceptions (like unstored calculations that aren't in use). Other exceptions include global fields (because they're client-specific), aggregate functions (so long as they're not being displayed or referenced in a field that IS displayed), and the like.

                                 

                                Nevertheless, it's always a good idea to keep your tables as narrow as you can, using as few fields as you can, especially in a WAN environment. When you start pulling pages out of the drawer, you don't want them to be made of lead.  

                                 

                                HTH

                                 

                                Mike

                                • 13. Re: Developer guidelines for optimum WAN performance
                                  thebridge

                                  Hi Mike,

                                  I happen to have a couple of those "lead pages" you refer to and have been in the process of cleaning up some calcs, relationships and unnecessary indexes in an effort try to speed up performance.

                                   

                                  I knee jerked while reading this thread because I thought that possibly an insinuation was that all unstored calc fields from all tables within the file might be processed.

                                   

                                  then I might have over thought it and wondered whether all records in a related table, even though most of those records were not involved NOT involved with the invoice displayed on the form, would be processed. (as an example, invoice line item records with unstored calcs not related to the found invoice record set)

                                   

                                  Thank you,

                                  Rob

                                  • 14. Re: Developer guidelines for optimum WAN performance
                                    ch0c0halic

                                    Along the lines of data being transferred from FMS to Client. Database design can greatly influence how much data is transferred. When requesting a record of data FMS supplies the whole record, not just the fields displayed. So when you design the DB if there are layouts for a specific purpose that require only 20 fields then it may be better to ignore proper DB normalization and create a table with those 20 fields and links to the rest of the data. That way when requesting the 20 fields you don't get all of them. Dividing data into smaller chunks so the download is limited to only what's needed speeds up the WAN performance.

                                     

                                    For (an extreme) example put Name and address fields for labels into one table. Create another table for phone/email/cell contact information. And others for unstored/AE calculations that 'go together'. Making the AEC enter on the same 'trigger' field allows you to update them as needed.

                                    1 2 3 Previous Next