9 Replies Latest reply on Sep 20, 2016 7:00 AM by carlsson

    Sort on server 14 very slow when first loading then lightning after that

    wpbernardi

      I have a database for service calls on server which has some 13000 records with some related tables. it runs on an exclusive mac mini with server 14 installed and nothing else. very few users - less than 5

       

      I use a startup script to open the db and load all records and get to a list layout initialize variables etc all of which is done in a few seconds - the records are unsorted (the appear in order of creation).

       

      I also have several buttons that find and sort various subsets of services all records. One button is FIND ALL RECORDS which loads in seconds also.

       

      If I then click a button to toggle SORT by creation date (ascending or descending)  IT IS PAINFULLY SLOW about a minute. even when I am the only user on the network.

       

      AFTER that sort is done I can resort on the same field in the same layout all 13000 records and it is lightning fast (less than a second)

       

      WHAT GIVES - I have tried every trick i can find - switch to layout with only the key field and the creation date. I have indexing turned on for both fields. i have eliminated unstirred fields from layout.

       

      any ideas - I guess we can just add a minute to the startup but I am concerned that something else is wrong.

       

      any ideas?

        • 1. Re: Sort on server 14 very slow when first loading then lightning after that
          techt

          First, if at all possible, do *not* perform Find All Records or allow your users so to do. How is anyone supposed to work with 13K records. In most instances, "active" records, records within x days, or some other formula is a much better option for sanity and support.

           

          So the first thing your server is doing is sending all fields of those records to the client. Once there the sort can occur. Any unstored calculations are only going to further slow things down as all of these calcs need to be performed. Depending on what field types you have, and how many are included in the sort, it's going to take a while, and that sort is occurring on the client, not the server. Once the sort is done (locally), it becomes much faster going forward as all of the data has been downloaded and sorted on the client.

           

          One option that I find works well is to open to a layout that has only one field (very quick to download), Enter Find mode there, then switch to the table, and perform a find that limits the found set in some fashion. The perform the find so that only that short set of working records is found and then sorted.

           

          HTH

          • 2. Re: Sort on server 14 very slow when first loading then lightning after that
            bigtom

            Is the creation date indexed always? Keeping the fields you sort on indexed will help. Set this in the field storage settings.

             

            13k records is not that many depending on how wide the table is.

             

            On the server you might see some improvements optimizing the cache size and using SSD storage. However you have to remember the sort is done on the client once all 13k records are sent to the client from the server. The server is not sorting the records for you. How good is the connection to the server?

            • 3. Re: Sort on server 14 very slow when first loading then lightning after that
              bigtom

              I have done finds on tables with 1M+ records of 50 or so fields each and returned sets of 50k records then sorted on various fields. This takes only a few seconds even on a hosted solution over WAN that has a only 2 core CPU.

               

              You may want to consider doing your finds on the server and returning only the records you need. This works really well.

              • 4. Re: Sort on server 14 very slow when first loading then lightning after that
                wpbernardi

                key field and sort fields are indexed.  This file has related tables and 297 fields 28 of which are un-stored due to using related fields for counts etc. this is a service call database and when someone calls in a new svc call sometimes it is useful to look back at all records etc

                 

                i just started running into this find all/sort delay which takes over 1 minute but once the first find all sort is done - i can sort all records in several fields instantly, so i put script in startup to pay the 1 min penalty at the beginning but waiting a minute for startup every day is unacceptable.

                 

                i also tried to make file cache 512 mb (largest allowed) no help

                • 5. Re: Sort on server 14 very slow when first loading then lightning after that
                  coherentkris

                  Sort happens on the client and is not affected by indexing.

                  At first first open records are loaded onto the client cache then the cache is sorted if necessary.

                  Any sorts after the first sort are faster because it's only sorting the cache and the overhead for loading all records from the host is not present or minimal.

                   

                  At least this is my understanding

                  • 6. Re: Sort on server 14 very slow when first loading then lightning after that
                    richardcarlton

                    coherentkris is correct.  + 1

                     

                    Indexing has ZERO impact on Sorting.   Sorting data occurs (except with WebDirect) on the Clients... so All the data is pushed down through the network. 

                     

                    This material and other performance topics are covered in detail in our Pro 15 Video Training Course.

                    FileMaker Training Videos for FileMaker Pro 15

                     

                    Thanks,

                     

                    RC

                    • 7. Re: Sort on server 14 very slow when first loading then lightning after that
                      siplus

                      wpbernardi wrote:

                       

                      I have a database for service calls on server which has some 13000 records with some related tables. it runs on an exclusive mac mini with server 14 installed and nothing else. very few users - less than 5

                       

                      I use a startup script to open the db and load all records

                       

                      stopped reading here.

                      • 8. Re: Sort on server 14 very slow when first loading then lightning after that
                        wpbernardi

                        solution for now:

                         

                        Since in several cases the user MUST see/sort ALL or THOUSANDS of records to accurately analyze different svc call problems etc I decided to add script that checks the found count and dialog the user warning them of the SORT delay giving the user the choice of waiting a minute or two for sort to complete or cancel.

                         

                        still need a way to analyze what really is driving slow sort of many records - is it client machine performance, too many unstored fields, or something else.

                         

                        user is happy to be forewarned, but i need more knowledge on FMP sort performance

                         

                        thanks to everyone for your input - Tect, Kris, Big Tom, Richard and Siplus

                         

                        Thanks for taking the time to reply

                        • 9. Re: Sort on server 14 very slow when first loading then lightning after that
                          carlsson

                          AFAIK; Sorting on related values take at least 1000 as long time as on non-related values.

                           

                          Example:

                          Sort 2000 Invoice on related Customer Names takes 10 seconds.

                          Sorting on the Customer ID is almost instant.

                           

                          Maybe this is what you're experiencing?

                           

                          It has been this way as long as I can remember. I hoped it was solved in FM server 15, but no luck. I really don't understand how FM can be so bad on sorting?