4 Replies Latest reply on Oct 28, 2014 11:40 AM by Vincent_L

    Slow relationships?

    tonymak

      Using Filemaker 13 Pro Advanced, FileMaker Server 13 both on Macs, Server is running on a Mac Mini 16Gb ram.

       

      I have a situation I can't figure out.

      I am working on a dashboard type layout and having performance quirks.

      pulling records from a table and two different relationships act very differently.

      the table Months has 12 records one record for each month giving a starting and ending date for each month.

      the estimates table has only 204 records in it.

       

      The fields i'm pulling from the table are:

      Relationship1: TO::Months to TO::EstimatesPerMonth (Table Source Estimates)

      setup as:

      Months::DateStart <= EstimatesPerMonth::Date

      AND Months::DateEnd >= EstimatesPerMonth::Date

      AND Months::zz_Sold <> EstimatesPerMonth::Status (zz_Sold is a literal "Sold")

       

      Using a portal to a TO of Months named MonthsALL

      a calculated field MonthsAll::cEstimateTotal set to: Sum(EstimatesPerMonth::Grand Total)

      the layout will load in under 2 seconds.


      -But-

       

      a relationship built on the same source table (Estimates) as a TO of ContractsPerMonth like this:

      Months::DateStart <= ContractsPerMonth::Signed_Date

      AND Months::DateEnd >= ContractsPerMonth::Signed_Date

      AND Months::zz_Sold = ContractsPerMonth::Status (zz_Sold is a literal "Sold")

       

      then removing the MonthsAll::cEstimateTotal from the portal and using the second relationship

      a calculated field MonthsAll::cContractsTotal set to: Sum(ContractsPerMonth::Conf_Grand_Total)

      the layout then takes as long as 45 seconds to load.

       

      so basically i'm using 5 fields from the Estimates table

      Date

      Grand Total

      Signed_Date

      Conf_Grand_Total

      Status

       

      a relationship built on Date, Grand Total, Status Loads Fast.

      but a relationship built on

      Signed_Date, Conf_Grand_Total, and Status takes a really long time to load.

       

      I'm at a total loss, any suggestions would be great.

        • 1. Re: Slow relationships?
          NickLightbody

          Hello Tony

           

          Any slowness is most likely caused by FMP having to evaluate some value as part of working out what records to display. Most commonly this is caused by an unstored calc at the other end of a relationship.

           

          Suggest you start by showing / writing out exactly what sort of fields are involved in your relationships.

           

          Screen shots pasted into Omnigraffle is a good way of debugging these sorts of things.

           

          Any which hold values which are not set in concrete require reviewing and possibly changing.

           

          One method is to duplicate any such fields and change the original fields to "concrete" but looking up from the copies

          Then use a self relationship to run the lookup.

           

          Alternatively you may find you can use auto enter in the key fields but with the don't evaluate checkbox unchecked so that they do evaluate when something the auto enter calc they contain changes in value.

           

          A common technique to make this sort of thing update is a "tickler" - an additional cartesian predicate in the relationship with a value that is guaranteed to change hence forcing the relationship to re-evaluate.

           

          The point here is that having a concrete field with an auto-enter on the other end works but any form of unstored calc doesn't.

           

          Sorry this is fairly general - if you give the necessary info then I am sure someone will help.

           

          Cheers, Nick

          • 2. Re: Slow relationships?
            mrwatson-gbs

            Hi,

             

            are the Months complete calendar months from the 1st of the month to the last day in the month?

             

            IF SO: there is a much faster way to code the relationships avoiding the date comparisons with greater thean and less then, and it is this:

             

            create a number field in each table representing the year and month numerically, YYYYMM and make a relationship with EQUALS - this is hundreds of times faster

            E.g.

             

            +Field Months::YYYYMM = Year( Months::DateStart ) *100 + Month( Months::DateStart )

             

            +Field ContractsPerMonth::Signed_Date_YYYYMM = Year( ContractsPerMonth::Signed_Date ) *100 + Month( ContractsPerMonth::Signed_Date )

             

            then change the relationship criteria:

             

            Months::YYYYMM = ContractsPerMonth::Signed_Date_YYYYMM

            AND Months::zz_Sold = ContractsPerMonth::Status (zz_Sold is a literal "Sold")

             

            Does that help?

             

            MrWatson

            • 3. Re: Slow relationships?
              mrwatson-gbs

              Is the Field Conf_Grand Total

               

              a) a stored number? or

              b) an unstored calculation or sum?

               

              If b) is the answer, then yes it will be slow because you are dynamically summing a dynamic sum.

              • 4. Re: Slow relationships?
                Vincent_L

                Hi Tony,

                 

                Are you sure about your data sources of your TOs. It happened to me once to have a duplicate one that absolutely killed performance.

                That said, > < are much slower