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

performance options

Postby lesouef » Fri Apr 30, 2010 10:16 am

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

Re: performance options

Postby jcompagner » Mon May 03, 2010 2:23 pm

See JSFoundSetUpdater that will trigger 1 sql update statement if configured/called correctly
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: performance options

Postby lesouef » Mon May 03, 2010 4:39 pm

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

Re: performance options

Postby jcompagner » Mon May 03, 2010 5:15 pm

what did jsfoundset updater send to the database then?
Because it should just send 1 update statement, and then of course a flush.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: performance options

Postby lesouef » Mon May 03, 2010 5:24 pm

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

Re: performance options

Postby lwjwillemsen » Mon May 03, 2010 8:29 pm

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

Re: performance options

Postby lesouef » Mon May 03, 2010 9:55 pm

I am not using this, so I guess it should issue one update for the whole foundset?
lesouef
 
Posts: 420
Joined: Thu Oct 20, 2005 8:13 pm
Location: Strasbourg & Paris - France

Re: performance options

Postby lwjwillemsen » Mon May 03, 2010 10:48 pm

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

Re: performance options

Postby lesouef » Mon May 03, 2010 10:52 pm

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

Re: performance options

Postby lwjwillemsen » Mon May 03, 2010 11:44 pm

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

Re: performance options

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

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

Re: performance options

Postby lwjwillemsen » Tue May 04, 2010 8:22 am

Does another Servoy rollback feature exists besides using databaseManager.startTransaction() and databaseManager.rollBackTransaction() ?
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: performance options

Postby ROCLASI » Tue May 04, 2010 8:42 am

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

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

Re: performance options

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

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

Re: performance options

Postby lwjwillemsen » Tue May 04, 2010 10:14 am

Thanks Robert for the info.

lesouef : According to Johan : See JSFoundSetUpdater that will trigger 1 sql update statement if configured/called correctly
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Next

Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 5 guests