Page 2 of 2

Re: performance options

PostPosted: Tue May 04, 2010 10:26 am
by ROCLASI
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.

Re: performance options

PostPosted: Tue May 04, 2010 10:42 am
by lesouef
ok, i'll check my code

Re: performance options

PostPosted: Tue May 04, 2010 10:45 am
by lesouef
I left it w/o argument, so the default may not be 1 but 3, I'll change that and let you know

Re: performance options

PostPosted: Sun May 09, 2010 9:57 pm
by lesouef
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);

Re: performance options

PostPosted: Mon May 10, 2010 11:43 am
by martinh
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 <your table>
SET canalcde = 0
WHERE <conditions that were used to load the foundset>


I've used several times this FoundSetUpdater and didn't notice anything strange in the SQL-query created.

Re: performance options

PostPosted: Mon May 10, 2010 12:19 pm
by lesouef
this is just an example using '0', most of the time, I would refer to another foundset or a related record to get the value, or worst, calculate it from its content, ie if this field contains "ABC" and this other contains BCD in the current foundset, then update this second field with 2.
And i got this example from the dev ref doc, p474, 1st example using "next".

Re: performance options

PostPosted: Mon May 10, 2010 12:22 pm
by martinh
I can imagine that if you make a loop, it will create an update statement for each iteration (which is 70K of update statements)
So in that case I would not use a FoundsetUpdater, but a normal foundset, or maybe even better, try to use rawSQL if you know a better query to update your database

Re: performance options

PostPosted: Mon May 10, 2010 1:09 pm
by ROCLASI
If you look 7 posts up then you see I already stated that the foundsetUpdater won't do Lesouef much good and that the pure SQL approach (see linked thread) already the most efficient option is.

Re: performance options

PostPosted: Mon May 10, 2010 1:38 pm
by lwjwillemsen
ROCLASI wrote:If you look 7 posts up then you see I already stated that the foundsetUpdater won't do Lesouef much good and that the pure SQL approach (see linked thread) already the most efficient option is.


Hi Robert,

I can't find that Lesouef tried it with table tracking off. If table tracking is on you'll always will get individual updates...
Has anyone seen/tested what happens in SQL when using foundsetUpdater.next() and afterwards performUpdate() ?

Re: performance options

PostPosted: Mon May 10, 2010 1:42 pm
by ROCLASI
Hi Lambert,

lwjwillemsen wrote:
ROCLASI wrote:If you look 7 posts up then you see I already stated that the foundsetUpdater won't do Lesouef much good and that the pure SQL approach (see linked thread) already the most efficient option is.


Hi Robert,

I can't find that Lesouef tried it with table tracking off. If table tracking is on you'll always will get individual updates...


You mean this ? From the 3rd post under mine. (emphasis is mine)
lesouef wrote:tried the foundsetupdater with mode 1, table tracking is off, and result is still very poor. endless minutes...


Also he needs individual updates because he uses data from the same record to update other columns within that same record. The most efficient way to do this is with one SQL query and let the database server do it for you.

Re: performance options

PostPosted: Mon May 10, 2010 3:35 pm
by lwjwillemsen
Also he needs individual updates because he uses data from the same record to update other columns within that same record.


If above is the case then there is not much to do within Servoy with regards to performance I think... Maybe a feature request for general foundSetUpdate(r) with other (related) fields ?

Re: performance options

PostPosted: Mon May 10, 2010 3:50 pm
by lesouef
I have been using a SQL query in the end, but I hate to bypass the servoy controller. and this is lightning fast.