3 Replies Latest reply on Jun 28, 2012 8:22 AM by wimdecorte

    Help with optimisation

    MarcoV

      I'm developing an FM system for entering figures against Tasks. There is a hierarchy, top level contains Programmes, which can have many Projects which in turn can have many Work Packages which in turn can have many tasks which can have many Costs (another related table). At Cost level I am storing the actual costs, these are mostly in GBP but certain programmes use Euros and have a different layout to allow a more detailed breakdown of costs, therefore I'm using 2 different cost fields. At Cost level I have a field CostFieldSelector which is a calculated field which does a logical lookup, if currency = GBP use Cost GBP otherwise use Cost EURO.

      At Task level I have a field called SpendToDate which is a calculated field: Sum(Costs::CostFieldSelector) and so on up the related tables to give SPendToDate figures for each Work Package, Project or Programme.

       

      The problem is when in List views the layouts take far too long to display as FM is doing some heavy number crunching, especially worse the higher up the related tables you go.

       

      Is there a better way to do this?

      Many Thanks in advance.

        • 1. Re: Help with optimisation
          RubenVanDenBoogaard

          Hi Marco,

           

          You could store results at a higher level, the higher the level the least number crunching.

           

          In the ideal situation you could store the totals on the Programmes level. but this all depend on which level the mutations are done.

          you could also use a serversided script to update the totals every xx minutes, to move the load to the server.

           

          Hope that helps,

           

           

          Ruben van den Boogaard

          Infomatics Software

          ruben@infomatics.nl

          • 2. Re: Help with optimisation
            MarcoV

            Thanks Ruben

             

            The problem is we need to see the figures at all levels. However I do like your server script suggestion. What type of field would the totals be and how would you specify them being updated?

             

            Thanks

            • 3. Re: Help with optimisation
              wimdecorte

              typically in these kinds of calculations you want to be careful using calculations as they tend to show historic data if at some point you change the calculation (ro maybe even if the exchange rate changes).  While it is more work you would set totals and calculated values by scripts and events.