Storing calculation based on related sum of aggregate

Classic Servoy
1

Are there any problems with storing a calculation that is based on a related aggregate? My concern is that I will be referencing the stored calculation in SQL queries.

For instance, table one named ‘Orders’ is related to table two named ‘Order Items’ through the column named ‘order_sn’ in a relationship named ‘orders_to_order_items’. ‘Order Items’ has a number column named ‘quantity’ and an aggregate sum of ‘quantity’ named ‘a_sum_quantity’. ‘Orders’ has a stored calculation named ‘c_sum_order_item_quantity’ with the following code:

return ( orders_to_order_items.getSize() > 0 ? orders_to_order_items.a_sum_quantity : 0 );

Does anyone forsee any problems here?

Steve In LA

2

It’s OK to do - but consider when that is going to run - and manage it yourself.

DO NOT use that in a Servoy Calc. If you do, it can go really wrong (performance-wise) - especially over a WAN.

DO use it in a method and set it when you need to (i.e. control it yourself) - so it won’t “fire” all the time (like in a calc).

3

Bob, others,

We’re past the development stage of our Servoy solution, and are in the mature / maintenance stage. We’re looking at ways to optimize and simplify our solution and data structure.

The example CRM uses stored calcs based on aggregates of child items, and so seems to go against Bob’s advice here.

One concern is that we have quite a few stored calculcations based on aggregates - ie, order_total is stored calc based on aggregate sum_order_item_ext which is an aggregate of order_item_ext. Multiply this over several relationships - order_items to products, products to product items, back order status to products, etc. etc., and the number of calcs and aggregates is enormous.

Our DB server is quick, never a query over .150 ms and most around .020 for selects. Servoy, however, is fairly slow just trying to click around the various forms and even moving from record to record in detail view (with several tabs).

This leads to slowdowns in listviews and forms with tabpanels, yet we need those aggregated totals outside of Servoy as we use outside tools to access as well (export to Quickbooks, report writing, intranet, etc.).

I’m trying to find a good way to limit the use of calcs and aggregates as we go forward, but run into the limitations of Servoy.

What would you recommend? And are there any examples of what to do when a solution begins to scale outside the ability of servoy to keep up with simple aggregate / calcs ?

With 3.5.5, I’ve noticed you can attach a method directly to a table based on db triggers. With this, we could trigger updates of parent tables whenever a child table changes, updating totals then. Has anyone done this and does it scale well?

What other ways are people coping with large solutions and the ability to aggregate and calc and not slow things down?

Most of our tables have multiple forms - display, edit, list, list_here, list_there, etc. - and we’re constantly having trouble with calcs updating on one client and not others - even with multipe recalc and savedata types of methods.

The convenience of calcs and not having to attach triggers to every single significant form and field have taken us down the wrong path, yet Servoy documentation and the forums don’t seem to offer much in regards to more elegant ways to accomplish this.

Any ideas on directions to take or examples / tutorials on similar situations?

4

fsciweb:
What would you recommend? And are there any examples of what to do when a solution begins to scale outside the ability of servoy to keep up with simple aggregate / calcs ?

I’d be interested in recommendations too.

fsciweb:
With 3.5.5, I’ve noticed you can attach a method directly to a table based on db triggers. With this, we could trigger updates of parent tables whenever a child table changes, updating totals then. Has anyone done this and does it scale well?

I’m doing this a bit and it seem to work well, except that you cannot use foundsetUpdaters to update your records.

5

I’m doing this a bit and it seem to work well, except that you cannot use foundsetUpdaters to update your records.

Meaning that the foundset in the client is not notified of the changes resulting from table level methods?

6

Yes,

I am very interested in recommended way of handing stored/Un-stored calcs to Aggregates, It is a constant headache.

Servoy please advise.