Optimizing a Servoy Solution -- Where to start?

[swingman]

Hi all,

I have a large system which has been in production for a couple of years now. We are on Servoy 3.5.4 with 20 to 25 users, some working remotely. Some accounting screens are now taking a while to load so it looks like I need to optimize something.

I have a list screen listing all clients -- we have 10,000+ in total. There is a detail panel showing all invoices and transactions for each client. Some clients now have 100s or 1000s of each. Each invoice sums up the transactions (sales and payments). We sum up the current customer account balance, YTD, last year turnover etc.

The database, PostgreSQL 8.2.x, seems to respond quickly to queries. We can do reporting via custom SQL queries very fast, aged creditors and debtors takes a few secs, including processing the data with Servoy. It looks to me that Servoy Server and/or client is spending too much time processing something.

Possible areas to look at:
- display of unstored calculations from related tables in html areas. Lots of hyperlinks on these screens. Go to invoice, Go to transaction, Go to product etc
- un-stored calculations in general
- aggregations in Servoy

Anyone out there who have had to this?
Any good ideas?
What is likely to give the best results?

[IT2Be]

To be quite honest, when I started building our large solution I tried to stay away from unstored calculations and aggregations as much as possible.

Even with smaller amounts of data they are forced to calculate each and every time.
Although Servoy is optimized considerably since then I still believe the approach should be to solve it in another way when possible.
BTW this has nothing to do with Servoy but should be considered whatever way you develop a solution/application.

So for me the alternatives are either stored calculations or calculations/aggregations per foundset or displayed data.

But that is me...

[swingman]

To be quite honest, when I started building our large solution I tried to stay away from unstored calculations and aggregations as much as possible.

I know, but they were very convenient ;-)

I will try to store some of them to see what speed gains I get...

As for the aggregates, I'm considering creating 'a materialized view' in a new table for some of the info which I can either update nightly via a batch processor or using the new table events in 3.5.x.

Table events are really excellent.

[rodneysieb]

Hi

I have definitely moved away from showing unstored calculations or aggregations per record in list/table view. These can be quite taxing on the system. A noticeable performance gain can be had here by removing them, especially noticeable when scrolling through the list. If the user wishes to see these values they can visit the record in detail.

They are very convenient however....

[sbutler]

Start here: http://www.servoyguy.com/knowledge_base ... w_bandwidt

Specifically, #2, use preloading and it will probably make a big difference.

It is probably not the aggregates and calculations unless you are showing a TON on a form. You can check the DB performance page to see if there are any slow queries relating to that, but I would expect your problems will be solved using Preloading.

[swingman]

Hi Scott,

we have loads of data, have had problems even over high bandwidth ;-)

I have optimized a couple of screens by storing calculations and avoiding direct display of aggregates and my customer now reports the screen loads 'instantly'.

Happy customer.

Now, if I run a DatabaseManager.recalulate(foundset), does that touch the modifiers by and modified date if I have set up auto-enter options?

[IT2Be]

I have optimized a couple of screens by storing calculations and avoiding direct display of aggregates and my customer now reports the screen loads 'instantly'.

Nice...

[Hans Nieuwenhuis]

Marcel, what do you mean by :

calculations/aggregations per foundset or displayed data

?

Regards,

Hans

[fsciweb]

What about relationships, tabpanels etc.?

What triggers a load of related data? Are hidden forms in a tabpanel loaded initially, or only when that particular tab is selected?

Take a given table:

media_id - integer
media_data - media
media_thumb - thumb

with list view of:

media_id media_thumb
(click on thumb opens media_data in formindialog)

Will Servoy load media_data AND media_thumb in listview? or will it wait to load until the actual binary data is shown?

If media is in a different related table on a form where there is:

A custom controller (tabpanel) with list view (does not contain media) with main form as the detail (containing media) does ALL media load when main records load, or only when each particular record loads thus loading the singular related media record?

Is there an easy (ie, not logs, my eyes glaze over when I try to view servoy logs!) way to see each step when a form is opened - ie, form show, trigger method 1, load records, load aggregate, load related foundset, load form in tabpanel, etc.

It would be nice to see these things, and even some analysis of the load / time to execute each step.

I've thought about taking some data off main tables and shunting it into a "meta" related table, thus keeping it from loading initially BUT related tabpanels and more will access such meta data, so is it worth the effort?

This is not due to Servoy being super-slow, but would like to make our solution more scalable as we re-design portions of it.

Thanks for any best practice ideas.

[Jan Blok]

Another tip for making the solution run faster in richclient can be read here:
http://forum.servoy.com/viewtopic.php?p ... ermb#50127

[fsciweb]

Thanks Jan.

I've read that already, but mainly was just trying to understand and prepare for "scaling" of servoy solutions. We're expanding, and just want to anticipate and prepare for the future :-)

Servoy works great for us as is...we notice a few slowdowns on forms with printable quality media and many calculations, but its manageable now.


Any advice on best practices for optimizing data and form loading is appreciated.

One thought, mentioned above, was to shunt all media (except thumbnails, etc.) into a media table and tie to it with relationships.

When is relationship data retrieved - on detail view of parent record, merely when the parent record is loaded, or?

And are blobs retrieved in general when a foundset is retrieved, or merely when the application needs to show the actual binary data?

NCM
FSCI

[Jan Blok]

1) When is relationship data retrieved - on detail view of parent record, merely when the parent record is loaded, or?

2) And are blobs retrieved in general when a foundset is retrieved, or merely when the application needs to show the actual binary data?

1) its retrieved upon usage
2) blobs are lazy loading when asked for

[fsciweb]

Thanks, Jan, but what constitutes "usage" (relationship data) and "asked for" (blobs)?

I'm assuming usage means:

Used in a calc by the parent record (aggregates, sums, etc.), shown in a tabpanel or portal, etc.

So, parent records with an unstored calc based on an aggregate sum of related child records (would stored behave this way, too?) shown in list view would load ALL parent and ALL child records in order to show the parent record with a correct sum.

"Asked for" - I assume means shown on screen or referenced in a method or calc.

Is my understanding or your term usage correct?

[Jan Blok]

My explanation should have bin:
1) record its retrieved upon usage (we have the primary-key already in many cases)
2) blobs are lazy loading inside records when asked for by media field or scripting

[ashutoslenka426]

Hi All ,

What does storing the calculations and avoiding the direct display of aggregations means ? . What actually you have done to fix this problem ? .