Servoy solution is becoming unbearably slow as it grows...

Classic Servoy
1

As our solution has developed, it’s become increasingly slow, to the point that it is now slower than the filemaker system that spawned it. Can anyone offer some insight on what makes a Servoy solution slow and what we can do to speed it up?

Here’s at least one likely culprit:

We have several calculated fields in our orders tables that determine total revenue, cost, and GP for the order. most of them call a global method that runs a SQL query. For example, here’s our revenue calculated field:

return globals.getCustomerInvoiceTotal(jobid);

where globals.getCustomerInvoiceTotal() is:

var thejobid = arguments[0];
if (!thejobid) return null;
var query = 'SELECT SUM(invoicetotal) FROM customerinvoices '
query += 'WHERE invoicedate IS NOT NULL AND invoicedate != \'\'';
query += 'AND jobid = ' + thejobid

dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, 1000)

return dataset.getValue(1,1);
[CODE]

These calculated fields work fine as far as I can tell.  I prefer calling SQL statements in the calculated fields to working with complex relationships I build in Servoy, since it helps keep down the servoy relationship clutter.

We need to store these calculations so we can access them externally.  The problem is, when I make them store calculations, the solution becomes unbearably slow.  It even slows down the solution on forms that don't have these fields!

There must be ways to speed this thing up...?

Thanks,

Andrew
2

Hi Andrew,

DO NOT EVER use SQL in calc fields! In fact, if you notice, the database manager node is not even visible in calcs. There is a reason for this.

If you use SQL, then use event to get/set the data.

3

Bob,

First of all, out of curiosity, why can’t I use SQL in the calc fields?

I changed the calculations to use Servoy relationships, and it’s much faster. Thanks.

4

Hi Andrew,

The reason is that those calcs evaluate more often than you think. And that means that there is lots of overhead. That’s why the database manager functions don’t show in the calc engine anymore.

In general - the ONLY functions we recommend are VISIBLE in the calc engine tree. Everything else - can cause performance issues.

Hope this helps.