Servoy Forum

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.

+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,

Great idea. How would the execption work?

+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

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

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

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?

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'?

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.