Calculated fields vs. calculated stored fields

[Bernd.N]

I currently wonder which of both is more efficient for smart client regarding speed: calculated fields or fields that I store in my table and calculate on my own whenever it is necessary.
So here are my two questions:

When you have several calculated fields that show in a table form, does the AppServer do the calculation before sending the foundset to the smart client, or is the smart client calculating them locally? Because in the first case, the AppServer has a burden when more and more users are online.

Example: I have four calculated fields that all show in a table, they mainly produce different icons based on some conditions.
Should I take those calculations out and replace them by real fields in the table, which I have to set myself when the conditions change?

[achiary]

Regarding your concerns about performance and based on our experience I would recommend the following :

1) Be very cautious to use Servoy Calculations : they are very powerfull in terms of development but may take you to a poor performance at runtime; so there has to be very good reasons to use it.

2) When you need some data to be calculated you shoud analyse when it REALLY should change its value and try to change it ONLY in that situation.

I would classify this cases as follows (in order of preference to minimize overhead) :

- Data that changes in specific transactions due to business rules : for example the "due amount of an invoice" , that changes every time a payment is done against that invoice, so let´s say 1, 2 or a very small number of times in its life.

- Data that may need to be calculated when the row is inserted or updated , and this is done in the onRecordInsert and/or onRecordUpdate events methods because there may be several transactions that can fire it and it is wise and covenient to write this logic in only one place. There is a lot of data of this kind and we use it a lot to prepare information that will be queried later. This will happen several times but always fired by a change produced by a business transaction. Depending on what you are dealing with you can estimate how many times per minute, day, week, month or year it can happen.

- Data that must be calculated every time a row is accessed in order to be showed but it is not needed to update the database : in this case you may use a Servoy NOT Stored calculation that will be fired EVERY TIME the row is accessed either on a form or programatically, and the calculated value will behave like another column of the row. For example : the user is a stockholder who queries his shares and the application shows him his CURRENT money based on the CURRENT value of the stock. Another example : the number of days an invoice has been unpaid.
Disadvantage : it is calculated many times.

- Data that must be calculated every time a row is accessed in order to be showed and also it is needed to update the database with the calculated value : in this case you may use a Servoy Stored calculation that will be fired EVERY TIME the row is accessed either on a form or programatically, and the calculated value will behave like another column of the row because it actually is a column of the row. It is not easy to find examples that justify this case but people may use it because makes developmenet easier.
Disadvantages : it is calculated many times and each time an UPDATE sql sentence is executed over the database, even when the recalculated value is equal to the stored in the database.

Hope this helps you to evaluate what programming resource to use depending on which are your business rules.

[Bernd.N]

Thank you very much for your answer, I fully agree to your recommendations.
I began to take out stored calculations so that I can decide when they are calculated to minimize the performance effects.

Also, I look now very carefully at the content of calculations, and try to avoid functions that could be costly when called too much times, e.g. utils.hasRecords().

[Harjo]

Disadvantages : it is calculated many times and each time an UPDATE sql sentence is executed over the database, even when the recalculated value is equal to the stored in the database.

I don't believe that is true! Ofcourse the calculation is done many times, but I don't think an UPDATE statement is done EVERY time! only when it changes.

Maybe someone of Servoy can comment on that one.

[achiary]

Would be nice to know if only updates when value changes.

[ROCLASI]

You can do a test.
Open the Servoy-Admin page and check the performance page (clear it before you do anything in your app). Or if you want more detail, enable tracing on the servoy-admin log page.

(Or Johan simply tells us how it works )

[jcompagner]

yes all stored calculations are ask for, but this doesn't mean they are recalculated, they are only recalculated if they where never touched or if they are in a dirty state (some dependencies that we know are changed)

We do that to make sure that for that row if it is saved everything is in the state that it has now.

Looking at the performance data of SQL won't really help, because if the stored calcs didn't change they are not send in the update statement.

Calculations are always done at the client side

[ROCLASI]

Hi Johan,

Thank you for explaining this.

Looking at the performance data of SQL won't really help, because if the stored calcs didn't change they are not send in the update statement.

I would argue that it does help. It would show that it won't do an UPDATE on calcs that don't change (and does on the ones that do change). The very question achiary wanted to have answered.

[achiary]

Sure it does help if the UPDATEs are only performed when the value changes. It is very usefull to know that.