4 Replies Latest reply on Feb 7, 2013 6:00 PM by davidanders

    Slow Sort



      Slow Sort



           I have an issue with "Records Remaining to Sort" taking 5-10 minutes on around 200 records.  Let me give you a brief description of my database:



           Employee-Stats (Same for Calls)

           In employee, I have around 15 calculation fields that get information from Calls and Stats.  There is also a calculation field that adds and uses these 15 fields.  This is called "FinalGrade".  

           There are around 100 employees.  There is a field called "Rank" that is boolean.  Calls and Stats have around 20,000 records a piece.

           When I go to a layout called "Ranking", it performs a find from start to end date and omits all non-ranked employees.  It then sorts them by "FinalGrade".  After this, it records the employee's rank into a field.  There is another calculation it does to determine if the employee is in the top 25% of employees.  When I run the script, it gives the Sorting message, then two update messages and then another sorting message.  The first sorting progress bar takes over 5 minutes the first time.

           Here is the script:
           Go to layout["ranking"(ranking)]
           Enter Find Mode [restore]  //Specifies ranked=1
           Sort Records[Restore; No dialog]  //Specifies to sort by final grade.
           Replace Field Contents [no dialog; employee::rank; get(recordNumber)]
           Replace Field Contents [no dialog; employee::rank; if(get(recordnumber)<=Get(foundcount)*.25; "Top 25%"; People::Bucket)]


        • 1. Re: Slow Sort

               Final Grade is an unstored calculation field. Sorting on an unstored field will take much longer than sorting on a stored field. In addition, the complex relationship with inequality operators is slowing down how quickly the final grade field evaluats. (The sort has to evaluate this field on every record in the found set before it can sort them.

               I don't know how much it would speed things up, but one way to match by a date range without inequalities is very similar to your cDateList field.

               A custom function (requires Filemaker Advanced to add it to your database file) or a script can replace the datestart and dateend fieilds with a single text field filled with a return separated lists of dates from date start to date end. That would make your relationship similar to this:

               Employee-Stats (Same for Calls)

               And it  might be possible to set up DateRange so that cDateList is no longer needed by omitting those dates from the list in DateRange--further simplifying the relationship.

          • 2. Re: Slow Sort

                 Phil!  We meet again!  This makes since because I recently moved out of testing 10-20 employees and now the sort is sorting 80.  With 10-20, it took under a minute to sort.  This was around the time we were working on the cDateList/DatesOmitted relationship.  I think I'll look into changing the Start/EndDate to DateRange next week.

                 I do have FM Pro Advanced (12).  I'm not sure if I have my head around the DateRange field just yet.  Would this be similarly populated as cDateList.  Where I create a loop that populates a date from start to end?  I'll pick this up next week.

            • 3. Re: Slow Sort

                        Would this be similarly populated as cDateList.  Where I create a loop that populates a date from start to end?  I'll pick this up next week.

                   You have the right idea. A custom function can use a recursive loop to populate the field. Without that function you can use a looping script as you describe to populate the field. In either method, the dates from table of omitted dates can be omitted from this list to simplify the relationship even further.

              • 4. Re: Slow Sort

                Solving Performance Emergencies with FileMaker Server

                Performance Optimization of FileMaker Databases
                     How Can I improve performance of my databases?

                The Marvelous Optimization Formula