slow performance in calculations and aggregations.

Classic Servoy
1

Hi All,

I am showing a form . It is a detail view . There are few calculations using aggregation . This is a Smart client Application in Servoy 7.2 and Postgres 9.2 . The page is loading slowly . The calculations are unstored . Will stored calculation improve the loading of the form ? . What I can do in case of aggregation ? .

2

Hi Ashutos,

ashutoslenka426:
I am showing a form . It is a detail view . There are few calculations using aggregation . This is a Smart client Application in Servoy 7.2 and Postgres 9.2 . The page is loading slowly . The calculations are unstored . Will stored calculation improve the loading of the form ? . What I can do in case of aggregation ? .

You need to provide a little more information to be able to give you any answer. You say the form loads slow (how slow?). Is it always slow or only the first time you show the form?
Do you see slow queries in the performance page on the server or are these all fast (i.e. a few milliseconds).
Also how many fields do you have on your form and how much and what type of data do you show (large text/images/etc) ?
You say you have calculations and aggregates? What are the calculations doing? Can you show some code that you think is slowing things down? And what aggregates (and how many) are you using on the table(s) you are showing on the form?
Also do you show any related data or is it just the one table that is in the context of the form.

3

Hi Robert,

Thanks for your reply . We are having a update UI method . This is using some relations to update the UI of the buttons . I have confirmed this from the performance data in the Servoy admin page . This is showing relations with count . I guess it is called multiple times . The first entry in admin page is Relation with count 10 and total time 00:01:322 . How can I optimise the relationships ? . Will Indexing work ? . Please provide some suggestions.
L ?
I am getting this in the performance page . I am not getting any clue from where it is coming . What is the cause of this aggregate SQL ? . Please provide some suggestion.

select sum(XXXX) as sum_XXXXX, sum(XXX) as sum_XXXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXx, count(XX) as count_XXXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXXX from XXX where (XXXX = ? and XXX= ?) and (XXX= ? or XXis null) limit ?
4

Hi All ,

Anybody please provide some suggestion on this .

5

ashutoslenka426:
We are having a update UI method . This is using some relations to update the UI of the buttons . I have confirmed this from the performance data in the Servoy admin page . This is showing relations with count . I guess it is called multiple times . The first entry in admin page is Relation with count 10 and total time 00:01:322 . How can I optimise the relationships ? . Will Indexing work ? .

So your relation takes about an average of about 100 ms to load the data. That is kinda slow. I would certainly look at indexing.

ashutoslenka426:
I am getting this in the performance page . I am not getting any clue from where it is coming . What is the cause of this aggregate SQL ? . Please provide some suggestion.

select sum(XXXX) as sum_XXXXX, sum(XXX) as sum_XXXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXx, count(XX) as count_XXXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXX, sum(XXX) as sum_XXXX from XXX where (XXXX = ? and XXX= ?) and (XXX= ? or XXis null) limit ?

When you have any aggregates defined on a table in Servoy then Servoy will call them all in one single query. That is what you see in the performance tab.

Hope this helps.

6

Hi Robert ,

Thanks for your reply . How we can optimise the aggregates ? . Or we cannot do anything with it .

7

Hi Ashutos,

You can see when you put indexes on the columns that you filter on (the WHERE clause) if that makes things faster.
If not, remove the applied indexes again since managing these indexes has a cost.

Hope this helps.

8

Hi Robert ,

Yes I got your point in terms of indexes . But what can I do with aggregation ? . How can I optimise it ?. This Aggregate SQL is called multiple times .

9

Hi Ashutos,

My last point was about the aggregates.
As for the aggregate definitions in Servoy. Where are you using them? Could they be refactored in a simple method with a query then you call when you need it?
Only then you have full control over when and how many times it gets called.

Hope this helps.