AnsweredAssumed Answered

Slow calculations or hang using Sum() across relationships

Question asked by stcav on Feb 28, 2013
Latest reply on Mar 1, 2013 by stcav


Slow calculations or hang using Sum() across relationships


     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?