option to skip aggregations?

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

option to skip aggregations?

Postby amcgilly » Wed Feb 10, 2010 11:40 pm

Servoy generates queries to retrieve aggregations from a given table anytime you create a related foundset on that table. This happens whether you refer to the related foundset strictly in your code (eg. var fs = record.relation) or in a portal or a related tabpanel. It does this so that the aggregations are available as soon as the foundset it created. Makes sense.

Often there are situations where I am creating lot of related foundsets and I don't need the aggregations, and so performance is being compromised with no real gain for my users.

Would Servoy consider adding an option to the relation editor called 'skip aggregations'?

Obviously you'd have to think about what to do if a user tries to refer to an aggregation via a relation where they said 'skip aggreagations' but that doesn't seem like a huge problem.

Wondering what others think of this idea.
Adrian McGilly
Servoy Developer

Image
amcgilly
 
Posts: 375
Joined: Fri Dec 09, 2005 12:03 am
Location: San Francisco, CA

Re: option to skip aggregations?

Postby ptalbot » Thu Feb 11, 2010 7:40 am

+1
- Everything that helps getting control over optimization is a good thing IMHO.

amcgilly wrote:Obviously you'd have to think about what to do if a user tries to refer to an aggregation via a relation where they said 'skip aggreagations' but that doesn't seem like a huge problem.


Throwing a new kind of Exception would be the best option, probably?

Cheers,
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1654
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: option to skip aggregations?

Postby jcarlos » Thu Feb 11, 2010 8:17 am

Great idea. How would the execption work?
jcarlos
 
Posts: 578
Joined: Thu May 04, 2006 8:55 pm
Location: Palo Alto, California USA

Re: option to skip aggregations?

Postby Harjo » Thu Feb 11, 2010 11:08 am

+1 for me,

Also a switch to enable/disable a calculation, just for debug purposes would be handy.
we now have have to delete completly the calc, or put return, to the first line
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: option to skip aggregations?

Postby rgansevles » Thu Feb 11, 2010 12:06 pm

Adrian,

Aggregates are already loaded 'lazy' in Servoy.
When you touch a related foundset, the related records are loaded but the aggregates defined on it are only loaded when requested.
We do load all aggregates in 1 go when 1 is requested which is also an optimization.

So for example if you load orders2details and no aggregate is used anywhere they are not loaded.
If you touch the orders2details.sum_amount aggregate only then this aggregate and all other aggregates on this foundset are queried from the db and cached.
When you touch the orders2details.count_items later the cached value is used.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: option to skip aggregations?

Postby amcgilly » Thu Feb 11, 2010 9:05 pm

rgansevles wrote:Aggregates are already loaded 'lazy' in Servoy.
When you touch a related foundset, the related records are loaded but the aggregates defined on it are only loaded when requested.


Rob, that sounds like a good strategy but it's not what I'm seeing. I've tested this. Here's how.

1. Open the solution to a firstForm based on orders, making sure it has only a body part.
2. Clear the stats in the performance data.
3. Perform these two lines of code in a form method:

Code: Select all
var fs = orders_to_details
fs.setSelectedIndex(1)

4. Look at the performance data. There I see two queries - one for the rows, and one for the aggregations defined on the details table
Adrian McGilly
Servoy Developer

Image
amcgilly
 
Posts: 375
Joined: Fri Dec 09, 2005 12:03 am
Location: San Francisco, CA

Re: option to skip aggregations?

Postby jcompagner » Mon Feb 15, 2010 6:44 pm

Ha :)

Rob made it that way that for related foundset we bundle loads of queries at once
So that the smart client only does 1 request to the server and it will have many sibling related foundsets (up to 10)
and with every related foundset it queries we also do a aggregate query at once.
So for a max bundle query to the server there could be 20 queries done at once.

This helps performance quite a lot for the smart client, because the compressing sockets make sure that the query to the server
and the data thats returned are compressed quite good (because it is many times the same kind of data) and there is only 1 call..

And there is one thing slow, thats the call to the server, thats where most overhead is sitting in.

But do you have aggregates that are heavy and take a lot of time?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: option to skip aggregations?

Postby amcgilly » Tue Feb 16, 2010 2:55 am

jcompagner wrote:And there is one thing slow, thats the call to the server, thats where most overhead is sitting in.

But do you have aggregates that are heavy and take a lot of time?


The individual aggregates aren't very complex, and the time they take in the performance stats is tiny. What I was concerned about was overhead but it sounds like I don't need to worry about that.

jcompagner wrote:Rob made it that way that for related foundset we bundle loads of queries at once
So that the smart client only does 1 request to the server and it will have many sibling related foundsets (up to 10)
and with every related foundset it queries we also do a aggregate query at once.
So for a max bundle query to the server there could be 20 queries done at once.


Can you clarify what you mean by 'sibling related foundsets'?
Adrian McGilly
Servoy Developer

Image
amcgilly
 
Posts: 375
Joined: Fri Dec 09, 2005 12:03 am
Location: San Francisco, CA

Re: option to skip aggregations?

Postby jcompagner » Tue Feb 16, 2010 2:17 pm

amcgilly wrote:
Can you clarify what you mean by 'sibling related foundsets'?



if you have a orders foundset of 20 order records

and you ask the relation "orders_to_orderdetails" on the first orders record in the orders foundset
then we are going to look down to see if the sibling records of the orders record also do have (different) related foundsets of that orders_to_orderdetail
we do this up to 10 related foundset queries for the sibling records.

so when you ask that relation on the first order record
we query at once also that related foundsets of the 10 next order records in that foundset.
So that are 10 queries being done but only 1 call to the server

if you then also have aggregates we also send in 10 extra queries with that same call to the server (so total of 20 queries are done in 1 call)

So when you go to record 2 of the orders foundset and you display also there the orders_to_orderdetail foundset including an aggregate of that related foundset
no query is done at all up to the 10th record in the orders foundset.
When you hit number 11 we query at once 11-20.

this way we greatly reduce the calls done to the server.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 9 guests