9 Replies Latest reply on Mar 29, 2016 2:28 AM by Mike_Mitchell

    Reality Check For Improving Remote Performance

    user21731

      Hello,

       

      I'm trying to improve performance on a legacy database, especially when displaying lists of records remotely. I've been researching the subject here and elsewhere, and I'm hoping to make sure I'm understanding things properly:

       

      1. When serving a list of records, FileMaker Server sends the client data for every field in every record displayed (plus a few extra) even if only a few fields are actually displayed in the list layout. So my first task will be to pare down the number of fields in the table displayed. Over the years, there have been a lot of redundant or now-unnecessary fields added to the database.
      2. If I understand correctly, FileMaker Server doesn't also send data from fields in related records -- unless these related fields are either displayed in the list layout or used by a calculated or summary field that's displayed in the list layout. Is that right?
      3. If #2 is correct, what happens when a field in the list layout does need to make use of data from a field in a related record? Does FileMaker then have to send all of the data from all of the fields in the related record, or does it just grab the data from the specific related field that it needs?
      4. My layout needs to display some totals at the bottom of the list. Is there a performance difference between a summary or calculation field that tallies a stored field in the current table vs. a summary or calculation field that totals stored fields from a related field?

       

      Thanks in advance for your help.

        • 1. Re: Reality Check For Improving Remote Performance
          bigtom

          I do believe #1 is incorrect. I believe only the fields on the layout for each record are sent.

           

          Summaries and Calulations both have a cost, but generally Summary is faster in my experience.

           

          #3 if you want the FM client to do something with the data like sum it, it has to be sent to the client even if it is only the fields needed. I believe it is only the fields needed.

           

          Is there a better way? Usually. I have been using Perform Script on Server (PSOS) to return sums using ExecuteSQL and storing them in global variables.

           

          ExecuteSQL ( "SELECT SUM (\"TheFieldYouWant\") FROM \"TheTableItIsIn\" WHERE \"Paramter1\" =? AND \"Paramter2\" = ?"; ""; ""; parameter1; parameter2 )

           

           

          Why do this?

          • Less fields required on the layout so that is faster if you were adding fields just to get the sums you needed.
          • The query runs on the server and does not need to transfer any of the records anywhere.
          • The server is like much faster in general than the client.
          • You send just a simple command and a few parameters over remote connection.
          • You only get back a few bytes of data.
          • You can get exactly what you want without having to deal with moving layouts and found sets on the server but you can if you want to. At that point you do not need ExecuteSQL, and some queries can get complicated. You can use special layouts specifically for these tasks and just do finds to get the records you need for the summaries.

           

          Will this always be the best answer? No. Depending on what you are doing with the records and how you want the summaries to be dynamic with changes on the list view. You might not want to be running a PSOS script every few seconds. But if you have a static lists for review or reporting (total sales of a department and each of its employees from last week) this can be a very fast way to get it done.

           

          PSOS is a great tool for remote connections regardless of whether you use ExecuteSQL or not. The biggest slow down in remote connections is the actual connection speed so sending less data back and forth is the best. If you are not running a version with PSOS, Upgrade! Some people report speed up to 100x faster using PSOS and I have seen results similar to those numbers for certain tasks.

          • 2. Re: Reality Check For Improving Remote Performance
            user21731

            Bigtom,

             

            Thanks for taking the time to respond. It's appreciated.

             

            Are you sure about #1? The FileMaker Design Performance Guide (Design: Performance) seems to be pretty clear on that point:

             

            1. FileMaker moves data from the server to the client one entire record at a time. With only a few exceptions, this means that all of the fields in a table for any given record move as a single block of information.

             

            I'll definitely look into PSOS and ExceuteSQL. I've got a few ideas where I think they'd work well. Would they be less useful for totals at the bottom of a list that's likely to be modified regularly by the user? I'd need to insert some kind of trigger to recalculate the summaries when the list changes, I'm guessing?

             

            Thanks again.

            • 3. Re: Reality Check For Improving Remote Performance
              Mike_Mitchell

              bigtom wrote:

               

              I do believe #1 is incorrect. I believe only the fields on the layout for each record are sent.

               

               

              No. The OP's original understanding is correct. All fields in the target record will download, with the exception of unstored calculations, container field contents, and globals. This applies to whatever record is being fetched (related or parent).

               

              Be careful with PSoS, also. It's a great tool, but every new session creates a new virtual client on the server. You can easily overload the server with extraneous threads.

              • 4. Re: Reality Check For Improving Remote Performance
                ch0c0halic

                Dan,

                 

                1. You are correct. Fetching data is record based. When the FMP client requests data it requests a record's data, not a field list of data.

                2. Same applies to related records.

                3. Not applicable.

                4. A summary or calculation field, either way the values isn't calculated till it is displayed. And either way the more records used in the results the more data that has to be fetched by the client.

                However,

                If you can perform a script on Server (PSoS) to return the value and put it into a global field then the display isn't impacted by the calculation.

                BUT, the speed difference is only as great as the number of records in the found set. The more records the better the speed improvement.

                If you don't have large (lots of data) records then you probably won't see any difference till you are well over 100 records, maybe over 1000.

                • 5. Re: Reality Check For Improving Remote Performance
                  user21731

                  Thanks, ch0c0halic,

                   

                  Just to make sure I'm understanding you correctly regarding #2 and #3, let's say that I have an Invoices table that is linked to a Line Items table. When I display a list of invoices, I know that Filemaker Server is sending me data from all of the Invoice fields in the displayed records, but is it also sending data from all of the Line Item fields in as many records that are linked to those invoices? Is that the case even if the Invoice layout doesn't show any of the Line Items fields or show any calculations/summaries based on the Line Items fields?

                  • 6. Re: Reality Check For Improving Remote Performance
                    Mike_Mitchell

                    Dan Brogan wrote:

                     

                    When I display a list of invoices, I know that Filemaker Server is sending me data from all of the Invoice fields in the displayed records, but is it also sending data from all of the Line Item fields in as many records that are linked to those invoices? Is that the case even if the Invoice layout doesn't show any of the Line Items fields or show any calculations/summaries based on the Line Items fields?

                     

                    No. Related records are always fetched when required, and not before.

                     

                    Also, in my testing, performing ExecuteSQL on the client with all records committed actually outperforms PSoS for the same operation. The SQL query is still performed on the server in both cases, but the overhead of starting up a PSoS session is not created.

                     

                    If you have open records on the client, then all records in all tables in the query are fetched. This is ... Bad.

                    • 7. Re: Reality Check For Improving Remote Performance
                      PowerSlave

                      ch0c0halic wrote:

                       

                      2. Same applies to related records.

                       

                      There are 2 scenarios, if the related records are displayed in a portal with a scroll bar, then yes , all related records will be loaded. If no scroll bar is on the portal, only x number of related records will be loaded, with x being the number of portal rows. This rule is especially important for CWP/PHP and can have a massive impact on performance.

                       

                      The key to good FileMaker performance is normalization of your data (don't duplicate data), avoid calculation fields or conditional formatting in lists, use numbers as primary keys where possible, and limit the number of fields per table. These points alone allowed me to increase the performance of our legacy CRM by a factor of 20, and now it's extremely fast.

                      • 8. Re: Reality Check For Improving Remote Performance
                        bigtom

                        I am corrected. The records are transferred but if it is not on the layout it does not render.

                         

                        Only sending data and not the UI using data separation helps. I try to keep all my images in a separate table and often keep full size and thumbnails in separate tables as well. I also sometimes keep record images in a table on the client with data separated UI file that checks for new images and fetches the new ones only when needed. When users need a large image the understand the may need to wait a few second for the new image to download if needed.

                        • 9. Re: Reality Check For Improving Remote Performance
                          Mike_Mitchell

                          Keeping images in a separate table - or a separate file - is an excellent idea. (In fact, if you’re using managed containers, a separate file is a best practice to avoid the migration issues associated with transferring the external assets.)