After quite a few tests, unless I am wrong, the way servoy generates it sql commands slows down a lot of operations when working on big foundsets.
The only reason for this is probably the mechanism which is used to be able to rollback records changes.
To overcome this, I am now making all big inserts and updates using the raw sql plugin to make direct changes in the db. and the speed difference is enormous.
example: modifying 2 field on 100k lines takes ages with the controller, minutes with the foundset even using transactions and a few sesonds using direct sql update.
I know this may create problems if other users would use the db at the same time, but in my case, this is done in a temp table and it is then pushed to a “shared” table.
so the feature request would be: could we have a mode which make direct changes but still using the servoy language instead of having to embed sql queries, a kind of flag which would bypass all the rollback mechanism, even if tihs means to put all users in hold mode in the given table?
See JSFoundSetUpdater that will trigger 1 sql update statement if configured/called correctly
I have tried that already.
But unless, I have been using it the wrong way, this is way slower than making direct SQL updates. Of course, this does not take into account the time which is necessary to update the current foundset after a direct SQL update (often longer than the modification itself), but worth doing it as the “reload” is a constant time for a given amount of records while the update depends on the mod done.
what did jsfoundset updater send to the database then?
Because it should just send 1 update statement, and then of course a flush.
no idea, I did not check, just checked the time spent on 200k lines compared to a pure sql udpate via the rawsql plugin or the mssql studio manager (equivalent timings), so that’s a test I have to do again. so I’ll return here with figures I can spend some time to test this.
Hi ,
When you have data logging enabled (via security.setSecuritySettings()) the foundsetUpdater won’t fire one update statement for the whole selection but one update statement per record in the foundset…
Regards,
I am not using this, so I guess it should issue one update for the whole foundset?
lesouef:
I am not using this, so I guess it should issue one update for the whole foundset?
Yep, that’s what Johan once told me… (The Servoy-wiki/docu would expand maybe ten times if all of this kind of valuable background information was added !)
I’ll check again, just in case I had not use the mode ‘1’, but as far as I remember, it could nto compte with the rawSQL update
lesouef:
I’ll check again, just in case I had not use the mode ‘1’, but as far as I remember, it could nto compte with the rawSQL update
Yes, I think so too. I assume Servoy appl. server must check and data-broadcast for every record involved in the update and that takes (some) time…
gosh, tired, many mistakes, but you had guessed, “not compete”…
broadcast? with a single user, that should not be that much, it is the same as reloading the founset.
I think it is their “rollback feature” which collapses the stuff, they probably make a temp table in case you’d like to undo changes?
they should be a way to disable this, and end up with a simple SQL update performance, then notify other clients.
at the moment, this is what I am doing, and this is the fastest by far. but true, it bypasses current foundsets for other users, but this is done upon import, so litlle chance another user sees the imported records before they get updated. and doing it on the fly during the initial insert does not use looked up values unless I do a query per dataset line, so processing insert all records + updating afterwards gives the fastest way for me, especially when using related values where SQL outperforms servoy 100 times.
Does another Servoy rollback feature exists besides using databaseManager.startTransaction() and databaseManager.rollBackTransaction() ?
Hi Lambert,
databaseManager.startTransaction() and databaseManager.rollBackTransaction() are database transactions. The only other rollback mechanism that Servoy has is databaseManager.rollbackEditedRecords() to rollback any NON-saved records. So this is only useful for when you use autosave off (or when a save failed).
Hope this helps.
not as far as I know, but even using it, if you check the sql commands generated behind the scene, you’ll see that this is not a simple sql update
Thanks Robert for the info.
lesouef : According to Johan : See JSFoundSetUpdater that will trigger 1 sql update statement if configured/called correctly
The FoundSetUpdater won’t do lesouef much good in this case. He needs to use record data to update other columns in the same record.
(See previous thread: viewtopic.php?f=4&t=14006)
So this requires a loop with 2 queries per record (select and update) when done in Servoy (or any other in-client solution). So not very efficient.
The most efficient way to do such a thing is on the database server itself using one pure SQL query. Of course you could use a stored procedure as well but pure SQL is already quite sufficient.
ok, i’ll check my code
I left it w/o argument, so the default may not be 1 but 3, I’ll change that and let you know
tried the foundsetupdater with mode 1, table tracking is off, and result is still very poor. endless minutes…
and this update is simply setting a field = 0, so not calling any related items, etc…
can anyone check how this normally last to update a field with 0 instead of any number on 70k lines? sql takes so little time that I can measure it, 1s or so.
the loop is:
var count = 0 ;
var $rec = databaseManager.getFoundSetUpdater(foundset);
while($rec.next())
{ $rec.setColumn(‘canalcde’,0);
count++;
if (count % 10000 == 0 ) application.output("updated " + count +“recs.”;
}
$rec.performUpdate(1);
Why are you using a loop here
Is the following not enough?
var $rec = databaseManager.getFoundSetUpdater(foundset);
$rec.setColumn(‘canalcde’,0);
$rec.performUpdate()
This will generate the SQL-update:
UPDATE
SET canalcde = 0
WHERE
I’ve used several times this FoundSetUpdater and didn’t notice anything strange in the SQL-query created.