performance options

Discuss all feature requests you have for a new Servoy versions here. Make sure to be clear about what you want, provide an example and indicate how important the feature is for you

Re: performance options

Postby ROCLASI » Tue May 04, 2010 10:26 am

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: performance options

Postby lesouef » Tue May 04, 2010 10:42 am

ok, i'll check my code
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: performance options

Postby lesouef » Tue May 04, 2010 10:45 am

I left it w/o argument, so the default may not be 1 but 3, I'll change that and let you know
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: performance options

Postby lesouef » Sun May 09, 2010 9:57 pm

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);
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: performance options

Postby martinh » Mon May 10, 2010 11:43 am

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.
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: performance options

Postby lesouef » Mon May 10, 2010 12:19 pm

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".
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: performance options

Postby martinh » Mon May 10, 2010 12:22 pm

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
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: performance options

Postby ROCLASI » Mon May 10, 2010 1:09 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: performance options

Postby lwjwillemsen » Mon May 10, 2010 1:38 pm

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() ?
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 626
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: performance options

Postby ROCLASI » Mon May 10, 2010 1:42 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5322
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: performance options

Postby lwjwillemsen » Mon May 10, 2010 3:35 pm

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 ?
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 626
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: performance options

Postby lesouef » Mon May 10, 2010 3:50 pm

I have been using a SQL query in the end, but I hate to bypass the servoy controller. and this is lightning fast.
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Previous

Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 0 guests

cron