Hi everybody,
I saw taht when being in debug perspective and calling databaseManager.saveData(), the calculations for the selected record in the foundset are called.
Am I right when saying that in Servoy 3.5 this was not happening?
My questions is: recalculation at databaseManager.saveData() in Servoy 5.x is a feature or a bug?
Thank you!
all stored calculations will be checked so that when a record is saved everything is really up to date.
jcompagner:
all stored calculations will be checked so that when a record is saved everything is really up to date.
That’s really nice, but in some cases we don’t want databaseManager.saveData() updating calcs.
Can we get an extra boolean argument that disables the auto-recalc? Like: databaseManager.saveData([foundset or record],false) ?
don’t like that, then people could start using that because it is maybe a bit faster. But then come back to us that calcs are not always up to date (or aggregates return the wrong values and so on)
This is just part of the system so that everything is as consistent as possible
I’m writing this because a routine that in Servoy 3.5 took 2 hours, now in 5.2.4, after conversion the same routine takes 8 hours.
I found out that cutting off databaseManager.saveData takes much much less.
jcompagner:
don’t like that, then people could start using that because it is maybe a bit faster. But then come back to us that calcs are not always up to date (or aggregates return the wrong values and so on)
This is just part of the system so that everything is as consistent as possible
I understand you like to keep things consistent, my policy is the same here.
Setting an extra argument to disable the recalc would be a conscious choice. The auto-recalc costs way more performance now.
For example (following Ionel’s post):
We have a customer solution that has a module with 30 calcs on 1 record. In a for loop it now takes 1 second per record when executing .saveData.
Looping though 55.000 records and storing data using databaseManager.saveData takes more then 8 hours(!). Without the .saveData it takes 2 hours. That’s a huge difference in performance. So we really like you guys to consider implementing the disable boolean.
first are you not using a transaction for this thing?
and are you calling saveData() after every record?
no transaction, and yes after every record
then i am not really surprised that it is really slow…
use a db transaction for these kind of things (so that auto commit is false) and also commit/savedata after every 500 or so records (instead of after every record)
Unfortunately this doesn’t help very much, because saveData after 500 or 1000 records takes very much as long as there are 20 stored calculations for each record. The fields that are filled in the loop, doesn’t affect any calculation, so in this case it is a waist of time, I think we can decide for ourselves if we need or not to recalculate at databaseManager.saveData().
you also use a db transaction for these bulk operations?
You really should do that.
You can create a case, but it won’t be a param of saveData(), but maybe some kind of property that can be set somewhere else that you can switch it of temporally, i don’t think this belongs in the general api.
Johan, could you explain a bit more? I understand the saveData in in 500 peaces.
Why to use db transaction for speed in this? This is new for me…
Does this prevent the execution of the 20 calcs??
Hi Harjo,
If we are talking about PostgreSQL it uses an implicit transaction for every SQL statement you fire at it.
So each saveData() will start a transaction, updates the value and then commits it. Yes, even without using the databaseManager.startTransaction().
This is because how MVCC works.
So in effect each SQL statement has some overhead. If you do a lot of updates/inserts you can disable this implicit transaction by starting one yourself and effectively wrapping all the SQL statements into one single transaction.
So starting a database transaction will speed these bulk updates/inserts up.
Thanks Robert, I know what you mean, but Johan keeps recommending a db transaction, ALSO when you saveData() after every 500 records…
that one I don’t get completely
Hi Harjo,
Because that one single saveDate() every 500 records results into 500 updates/inserts and thus 500 auto-commits. So it’s not related to the saveData() action but to the SQL it results in. One statement equals one auto-commit.
Start your own database transaction and you only have one commit for ALL your insert/updates. That is the difference.
ah got it! Thanks Robert