7 Replies Latest reply on Oct 20, 2016 5:55 AM by OCDilla

    Sorting is extremely much slower on related recordes

    carlsson

      Working with related records in Filemaker have always been slow, but now I'm wondering....

       

      I have a table with 6.000 records containing Orders and related Clients.

       

      Sorting on the related Client Name takes a couple of minutes.

      If I lookup the Client Name in the Order Table and sort on that it takes 1/10th of a second.

       

      WTF?!

       

      Something must be seriously wrong... All fields involved are indexed, and there's nothing strange really. As I mentioned, Filemaker is not very good at handling related data, but I don't know if I have noticed it this bad before...!?

       

      To use a lookup is bad in soo many ways, and I don't want redundant data!

       

      Any ideas?

       

      [FM Server and clients are version 15]

        • 1. Re: Sorting is extremely much slower on related recordes
          coherentkris

          Indexes have nothing to do with sorting.

          1 of 1 people found this helpful
          • 2. Re: Sorting is extremely much slower on related recordes
            BillisSaved

            Good morning carlsson,

             

            I hope your day is going well. I'd like to get a bit more information before offering any suggestions. Are you sorting the records via portal sort or via relationship? How are you displaying the sorted records on the layout (e.g., list view, portal, etc...)? Thanks and have a great day!

             

            God bless,

             

             

            Bill

            • 3. Re: Sorting is extremely much slower on related recordes
              taylorsharpe

              As coherentkis noted, indexes are not for sorting (at least in FM's model).  They are only for searching.  FM is notoriously slow in sorting which has always been an chiles heel, especially through joins.  For building arrays and getting sorts, I often use things like MBS's plugin to sort an array on the server (PSoS) or you can get some blazingly fast results putting data into javascript in a web viewer.  I do a lot of virtual arrays to speed up things like this.  Depending on what you are trying to do, there are some work arounds.  But, yes, I concur with your conclusion that FM is painfully slow on sorts, especially if through a relationship. 

              1 of 1 people found this helpful
              • 4. Re: Sorting is extremely much slower on related recordes
                carlsson

                Thanks for the answers so far!

                 

                The sort is done in a list view and it looks like the picture (with both local and related values for testing purposes, and as you may see some value have already been altered which clearly shows why a lookup isn't a good solution (which in turn forces me to create a Server Side schedule for relookups)).

                 

                As Taylor mentions, it's painfully slow to sort on the related values. And I really don't understand why? It's a simple relation based on Client ID. (BTW, doing the same sort in SQL takes the same time as sorting on the local value.)

                 

                I can understand if a sort is slow if it was based on advanced calculations and millions of records etc, but this?

                Sometimes I'm ashamed to be a FileMaker developer...

                 

                 

                Skärmavbild 2016-10-20 kl. 09.03.52.png

                • 5. Re: Sorting is extremely much slower on related recordes
                  wimdecorte

                  carlsson wrote:

                   

                  as you may see some value have already been altered which clearly shows why a lookup isn't a good solution (which in turn forces me to create a Server Side schedule for relookups)).

                   

                  I would disagree with both these though

                  1. there would be no reason for relookups: the name of the company at the time of the order is set and will never change.  If the company changes name over time, any historical orders should still reflect their old name at the time, not their new name.
                  2. because of this requirement for historical accuracy; I would set the company name on the order record when the record is created and leave it be after that

                   

                  The side benefit here is that sorting on them will be fast.

                  Doesn't solve your original issue of slow sorts in general but I think it is important to call this out.

                  1 of 1 people found this helpful
                  • 6. Re: Sorting is extremely much slower on related recordes
                    carlsson

                    We clearly don't have the same kind of users at the keyboard...

                     

                    They work with telemarketing, are generally computer analphabetics and don't care what they put in the fields, as long as it's readable. For example, you can see that someone have put a url in the name. Great.

                     

                    I have to trim everything and remove all formats as autoenter, since they cut and paste everything from the web. And I can actually understand why they do as they do.

                     

                    However, this is okey as long as they correct it when the client signs.

                    • 7. Re: Sorting is extremely much slower on related recordes
                      OCDilla

                      You should try performing the sort in a blank layout rather than a list view. In list view, Filemaker is queuing a lot of data on the client. Performing sorts in blank layouts will normally see a bit of an improvement in performance.  This is really noticeable if your current layout has summary fields.

                      2 of 2 people found this helpful