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

    Slow Sort

    FileMakerNovice

      Title

      Slow Sort

      Post

           Hello,

           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:

                       DatesOmitted
                       /        
           Employee---Stats
                        \
                         Calls

           Relationships:
           Employees-DatesOmitted
           EmployeeID=EmployeeID

           Employee-Stats (Same for Calls)
           EmployeeID=EmployeeID
           StartDate<=Date
           EndDate>=Date
           cDateList!=DatesOmitted

           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
           PerformFind[restore]
           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
          philmodjunk

               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)
               EmployeeID=EmployeeID
               DateRange=Date
               cDateList!=DatesOmitted

               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
            FileMakerNovice

                 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
              philmodjunk
                   

                        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
                davidanders

                http://www.briandunning.com/browse/browse0110.shtml
                Solving Performance Emergencies with FileMaker Server

                http://help.filemaker.com/app/answers/detail/a_id/5268/~/performance-optimization-of-filemaker-databases
                Performance Optimization of FileMaker Databases
                     How Can I improve performance of my databases?

                http://fmbench.com/marvelous
                The Marvelous Optimization Formula