6 Replies Latest reply on Mar 1, 2013 9:14 AM by stcav

    Slow calculations or hang using Sum() across relationships

    stcav

      Title

      Slow calculations or hang using Sum() across relationships

      Post

           I have an application that, among other things, seeks to calculate a score for an object, based on links from another object or objects. The objects are based on one of two tables, Input and Output; either could be related to none, one or many of itself or the other. I have implemented this using join tables and it generally works well (after advice from PhilModJunk a couple of years ago).

           The score calculations also work accurately until I ask for a larger number of calculations, perhaps by going deeper through the linked network or by displaying a list view with multiple records all showing the 'Score' field. In these circumstances the application slows down and frequently hangs, needing a Force Quit to get out of what seems to be an infinite loop. There are not many records, only about 80 to 100. The problem is much less marked if the score fields are blank or with very few records.

           I have searched the forums and noted complaints about FM12 being very slow in some circumstances, but that is not the cause here - FM10 and FM11 also hang although they are quicker in some other respects.

           I have concluded that the problem must lie in the way I have coded the calculation, which sums the score(s) in the join table(s), which is itself calculated by multiplying the score in the remote object(s) by a weighting in the join table(s). I would be grateful for any help with a better way of doing this.

           The basic relationship is:

      Output_root-----<Output_Output_fed>-----Output_fedBy_Output            (Or similar for other permutations.)

           Output_root::_kp_Output_id = Output_Output_fed::_kf_ID_Output_feeding
           Output_Output_fed::_kf_ID_Output_fed = Output_fedBy_Output::_kp_Output_id

           The field for storing the score is 'Score_calc', a Calculation in the Output table: 

      Case ( IsEmpty ( Score_input ) ; // To allow for manually introducing a score from which to start the calculations
      Score_calc_from_Inputs + Score_calc_from_Outputs  ; // To add together links to Outputs or Inputs
      Score_input )

           'Score_calc_from_Outputs' (and the equivalent 'Score_calc_from_Inputs') is also a Calculation in the Output table:

      Sum ( Output_Output_fed::weighted_score )

           'Output_Output_fed::weighted_score' (or equivalent) is a Calculation in the join table:

      Case ( IsEmpty ( Output_fedBy_Output::Score_calc ) ; // To force the field empty if the right hand object has no score recorded
      "" ;
      weighting * Output_fedBy_Output::Score_calc ) // Multiplies the linked score by a weighting percentage, which is manually entered into the field 'weighting' as a decimal between 0 and 1 into the join table.

           The intention is to produce scores like this:

                                                                                                                                                                                                                                   
                          Output_root::score (=60)                     ----25%----                      
                                                                      Output_fedBy_Output::score (=240)
                          Output_root::score (=120)                     ----50%----                      

           As I say, the calculations work properly and acurately in test mode and when working towards the right end of the linkage network, which is rather like a value stream or cause & effect diagram. The calculations seize up and ultimately hang the application when I go deeper into the calculations. I infer from other forum topics that the issue might to do with the Sum() function. I have tested the application with calculations removed, which resolves the problem, and with all the calculation fields empty, which prevents the hang but is still a little slow.

           I have checked for circular references but can find none. (However, it would be very helpful to implement an error trap for this - how would I do that or break into an infinite loop?)

           Any thoughts, please?

           Stephen

        • 1. Re: Slow calculations or hang using Sum() across relationships
          philmodjunk

               A reference to Sum ( RelatedTable::Field ) and a reference to:

               RelatedTable::sTotal

               where sTotal is defined as a summary fieid, total of "field", should produce the same result and some posts here suggest that using the summary field reference will evaluate more efficiently.

               It does look like you may have a recursive calculation here, so even a few records may "hang" your database due to very large numbers of recursive loops.

               I'm having trouble tracing the calculations that you are using here since apparently, Output_root and Output_fedBy_Output are two occurrences of the same table and then you explain that several of your calculations "are in output". If I have that right, it would help to know what table occurrence is selected in the "context" drop down for each of these calculations.

          • 2. Re: Slow calculations or hang using Sum() across relationships
            stcav

                 Thanks Phil for your quick response.

                 

            I'm having trouble tracing the calculations that you are using here since apparently, Output_root and Output_fedBy_Output are two occurrences of the same table and then you explain that several of your calculations "are in output". If I have that right, it would help to know what table occurrence is selected in the "context" drop down for each of these calculations.

                 By "are in output" I mean that the fields are in the Output Table. You're right that both Output_root and Output_fedBy_Output are Table Occurrences of the same Table. The fields 'Score_calc', 'Score_calc_from_Outputs' and 'Score_calc_from_Inputs' are all evaluated from the context of Output_root. The field 'weighted_score' exists in the Table 'Output_Output_join' and is evaluated from the Table Occurrence 'Output_Output_fed' (based on 'Output_Output_join') and evaluated from that context.

            A reference to Sum ( RelatedTable::Field ) and a reference to:

            RelatedTable::sTotal

            where sTotal is defined as a summary fieid, total of "field", should produce the same result and some posts here suggest that using the summary field reference will evaluate more efficiently.

                 So you're suggesting that I should do the summarising on the join table 'Output_Output_join', then reference that summary across the relationship form 'Output_root'. Is that right? Would that still capture all the values where there are several instances of links between 'Output_root' and several objects to the right? Trying to aggregate values from those several links is why I plumped for Sum() in the first place.

                 Nice to correspond with you again, sorry I keep giving you grief!wink

            • 3. Re: Slow calculations or hang using Sum() across relationships
              philmodjunk

              Sum ( RelatedTable::Field ) and RelatedTable::sTotal

                   evaluate exactly the same.

                   The summary field method updates less smoothly when showing a portal total where the user can edit values that affect the total returned so I prefer Sum() in those situations.

                   But I don't think that's the main issue here...

                   

              The fields 'Score_calc', 'Score_calc_from_Outputs' and 'Score_calc_from_Inputs' are all evaluated from the context of Output_root.

                   

              The field 'weighted_score' exists in the Table 'Output_Output_join' and is evaluated from the Table Occurrence 'Output_Output_fed'

                   Carefully mapping the data flow from calculation to calculation produces this:

              Weighted score (join)--->Score_calc_from_Outputs (root ) ---->Score_calc (root ) ---> Weighted score (join)

                   This would seem to indicate that your calculations can form a literally endless loop.

                    

              • 4. Re: Slow calculations or hang using Sum() across relationships
                stcav
                     

                This would seem to indicate that your calculations can form a literally endless loop.

                     Thanks Phil, I had reached the same conclusion. I'll work on trying to break the loop somehow, and explore using a script to do one-off calculations, since the user requirement does not call for continuous calculation.

                      

                     Kind regards,

                     Stephen

                • 5. Re: Slow calculations or hang using Sum() across relationships
                  philmodjunk

                       I don't have enough detail here to tell whether this suggestion may work, but you may find that you can use auto-entered calculations on data fields instead of calculation fields.

                  • 6. Re: Slow calculations or hang using Sum() across relationships
                    stcav

                         Thanks, I'll try that and let you know.