Conditional formatting slowing down portal?

[MikeKD]

Hi folks,

I've got a portal with a list of kids in the class.

I've set up two conditional formatting to flag up kids who are a certain percent below their all time average or the class average.

The conditional for the class average is below (the other conditional is identical with the references replaced.

Pupils::ThisYrAssesmentAverage ≤ ( Classes::AssessmentAverage - Classes::UnderachievementValue)
and
Classes::CompareUnderachievment="Class Average"

Enabling these conditionals seems to have a disproportionally huge impact on the performance of the portal - it takes seconds to load and sometimes quite a bit longer.  Class sizes vary from 2 to 28.

Performance is fine with these two conditionals off.

 

Have I bodged here someway; is there a way round it? Potentially, this kind of data is very useful....

 

Cheers,

Mike

 

[MikeKD]

Hi folks,

I've put both conditionals together; it seems more sensible, and it /might/ have speeded things up very slightly.....

(Pupils::ThisYrAssesmentAverage ≤ ( Pupils::AssesmentAverage - Classes::UnderachievementValue)
and
Classes::CompareUnderachievment="Last Year Average")

or

(Pupils::ThisYrAssesmentAverage ≤ ( Classes::AssessmentAverage - Classes::UnderachievementValue)
and
Classes::CompareUnderachievment="Class Average")

[MikeKD]

Does anyone have any ideas how I can optimise this / how I've bodged please?

It's just about useable, but very sluggish and I've got a powerful MacBook...

[Kris M]

Some questions to ask yourself:

Is their any other choice to achieve your goal than conditional formatting?

Are the average fields summary type or unstored calculations?

is their a way to shift the math to the table level instead of the conditional formating calculation so that the conditional calc is as simple as possible?

[MikeKD]

Thanks so much Kris; it hadn't occurred to me that conditional formatting wasn't always the best solution.

 

At some stage I wanted to have a kind of alert popup for each class - that I could click on to see if any girl was under / over performing.

I was thinking about just one alert field for each class with a formula to grab bits of info from other records, but potentially this could become far more sophisticated.

 

The average fields are (I think) unstored calculations - is it more / less efficient to use summary type?

 

Cheers!

Mike

[Wim Decorte]

Are any of those fields calculations (and perhaps unstored ones) by themselves?  If so then you're exponentially decreasing performance as for each related record FMS now has to calculated these fields if they are unstored calcs, before it can apply the format.

 

Since they are averages, calculate them when new scores come in and set them statically; don't rely on calculated fields for this.

[MikeKD]

 

Since they are averages, calculate them when new scores come in and set them statically; don't rely on calculated fields for this.

 

I've checked and they are unstored calculation fields. I didn't understand your first question though Wim - I suspect you might be right though as it seems a good explanation for the loss of performance!

 

Is there a way to set the field to unstored and then just bump it to recalculate when necessary?

Or should I change it to a normal field and use a script to insert the average?

 

Thanks so much!

Mike

[Kris M]

Wim squarely hit the nail I was holding.

My point was that unstored calcs can be detrimental to performance.

I was also suggesting that summary type fields may also incur the same performance hit. 

[Wim Decorte]

I've checked and they are unstored calculation fields. I didn't understand your first question though Wim - I suspect you might be right though as it seems a good explanation for the loss of performance!

 

 

Unstored calculations forces FM to calculate the result every time they are shown.  So every time you go to that layout, FM calculates the averages.  Which does not make sense for data that is not so dynamic that it changes every second.  If the data has not changed since the last time FM had to calculate it then you are just wasting time and processing power calculating it again and again.

 

I don't know your complete workflow but what I suggest doing is that the scripted workflow that adds data be tweaked so that at the end of adding data, the average is calculated in the script and set in a field.  That field is just a number field, not a calc.

[MikeKD]

Hi folks,

Well, my script to populate a relevant number field works, but is slow - is there anything I can do to speed it up? I'm even more rubbish at scripting than DB design!!

Go to Layout [ “Pupils” (Pupils) ]
Show All Records
Go to Record/Request/Page
[ First ] Loop
Set Field [ Pupils::ThisYrAssesmentAverage; Average ( PupilClassAssess_forYrAvg::cAcademicYearAvg ) ] Go to Record/Request/Page
[ Next; Exit after last ] End Loop

As it's fairly sluggish, I'm thinking of having a button to run it. If it was faster, maybe a trigger to run it after leaving the class assessment tab as well?

 

Thanks so much - this is all really helpful!

Mike

[Wim Decorte]

The script you post here is a one-off script, right?  That's not the script that will run when someone adds a record that needs to be calculated, correct?

[MikeKD]

Hi Wim,

That's right, yes Wim. I'm a bit of a novice though and assumed that my first attempt was not elegant or efficient. If I'm honest, I'm quite surprised it worked first time; that doesn't usually happen!!

 

Would you recommend one triggered when a record was committed, just to work out the average for that one kid?

 

Cheers,

Mike

[Wim Decorte]

 

Would you recommend one triggered when a record was committed, just to work out the average for that one kid?

 

 

Can't answer that; I would have to see the current workflow and design.

 

Script triggers can be ok but the Commit will fire even if other data is changed on the record.  I would tie something specifically focused on the data that needs to be averaged.

[MikeKD]

OK, with a bit of experimenting; this script seems to work OK. It's triggered "On Object Save". I was surprised that it worked OK without having to go to a different layout, but I'm not complaining.

Set Field [ Pupils::ThisYrAssesmentAverage; Average ( PupilClassAssess_forYrAvg::cAcademicYearAvg ) ]
Set Field [ Pupils::AssesmentAverage; Average ( Pupil_Class_Assess_Join::Percentage ) ]

I couldn't work out a way to know for certain if all the grades for one class had been completed and run a script then.

I decided against running the first script I posted daily (e.g.) too.

 

Just making these to fields non-calculated has improved the performance quite a bit.

 

Do calculated fields that aren't on the layout also impact performance if related records are changed; or are the calculations only made when moving to layouts they're in?

 

Thanks!

Mike

[Wim Decorte]

 

 

Do calculated fields that aren't on the layout also impact performance if related records are changed; or are the calculations only made when moving to layouts they're in?

 

 

Unstored calcs are calculated whenever they are used (on a layout, in a script,...).  Some are obvious like the ones that are on a layout, but some can be somewhat hidden like the ones in your custom formats.

Also watch out for cascading calculations: an unstored calc that uses other unstored calcs that uses other unstored calcs.... it can be quite a mess to read through that and try to understand what is dependent on what.