Calculated fields vs. calculated stored fields

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Calculated fields vs. calculated stored fields

Postby Bernd.N » Sun Apr 03, 2016 1:51 pm

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?
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 487
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Calculated fields vs. calculated stored fields

Postby achiary » Thu Apr 14, 2016 6:19 am

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.
achiary
 
Posts: 81
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: Calculated fields vs. calculated stored fields

Postby Bernd.N » Thu Apr 14, 2016 7:43 am

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().
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 487
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Calculated fields vs. calculated stored fields

Postby Harjo » Thu Apr 14, 2016 2:37 pm

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.
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4277
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Calculated fields vs. calculated stored fields

Postby achiary » Thu Apr 14, 2016 3:50 pm

Would be nice to know if only updates when value changes.
achiary
 
Posts: 81
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: Calculated fields vs. calculated stored fields

Postby ROCLASI » Thu Apr 14, 2016 3:58 pm

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 ;) )
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5246
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Calculated fields vs. calculated stored fields

Postby jcompagner » Thu Apr 14, 2016 4:11 pm

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
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8049
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Calculated fields vs. calculated stored fields

Postby ROCLASI » Thu Apr 14, 2016 5:50 pm

Hi Johan,

Thank you for explaining this.

jcompagner wrote: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.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5246
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Calculated fields vs. calculated stored fields

Postby achiary » Fri Apr 15, 2016 2:53 pm

Sure it does help if the UPDATEs are only performed when the value changes. It is very usefull to know that.
achiary
 
Posts: 81
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina


Return to Programming with Servoy

Who is online

Users browsing this forum: Bing [Bot] and 5 guests

cron