What am I doing wrong?

Classic Servoy
1

If I run the following code in Servoy developer, I get a new line in about .5 seconds - no problem

If I run it on my local network, I get a new line in about 1-2 seconds - a little slower - but not a problem.

If I run it over my 3G phone connected to our server with no other traffic, I get a new line in 5 - 10 seconds,

If my customer runs it over their broadband connection in Singapore - connected to our server, it takes up to 40 seconds, and yesterday they were complaining about it taking ‘minutes’.

My code is this:

function new_line_using_group()
{
	var sql_query = "SELECT MAX(estimate_item_id) FROM line_items WHERE line_items.sales_id = ? AND line_items.main_group = ? AND line_items.hidden_from_estimate <> 1";	
	
    var vDataSet = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), sql_query, [forms.sale_master.salesid,globals.g_main_group], 1);
    
    var vthehighestvaluenumber = vDataSet.getValue(1,1);    
    
    if ( globals.g_main_group == 'new group' || globals.g_main_group == 'all groups' || globals.g_main_group == 'ready to invoice') {
	        var thePressedButton = plugins.dialogs.showErrorDialog('No group', 'Please create or choose a group before adding a line','OK');
	        return;
	       }
	      
    else {
        foundset.newRecord(true);
        main_group = globals.g_main_group;
        sales_id = forms.sale_master.salesid;
        contacts_id = forms.sale_master.contacts_id;
        company_foundset_creator = globals.g_my_group
        complete = 0
		hidden_from_estimate = 0
        if ( vthehighestvaluenumber ) {            
            estimate_item_id = vthehighestvaluenumber + 10;
            }
        else{
            estimate_item_id = 10;
            }
       
        
        databaseManager.saveData();
    }
}

SQL performance log indicates that almost nothing takes over 00:00:002 to run, and after adding 10 lines (at 6 - 10 seconds each over my 3G connection), I see that the count of the most frequently used aggregate is 131 (average time 00:00:000 in the performance log).

Therefore my solution is not slow if run locally, SQL is not slow if run remotely, nothing seems to be a problem, yet my solution runs like a dog.

I have had other performance problems with this solution (since moving from Sybase to Postgres), and am firefighting trying to address each. My customer is not very happy, and I am unable to properly deploy my solution.

Any ideas on what I’m doing wrong or how to fix would be very gratefully received…

2

A bit more info,

During the ‘add record’ method, my server’s CPU load for Postgres never goes beyond 1 or 2% and the server is a 2.7Ghz quad corei5 iMac running Mac OS X 10.8 Server, with 20GB ram

3

try debugging the smart-client with CTRL-ALT-SHIFT-T and look what happens on the (network)line!

4

  1. An aside: error checking code should be at the top of the method.

  2. I doubt it is this method by itself (or the database) that is slowing your solution down. Since performance is degrading as the connection degrades, it is most likely an issue with number of round trips or the amount of data returned (or both) by this user action.

5

Hi Harjo and David

I have cleared the log, then done a trace for the process of clicking ‘add record’ - this is the whole thing - attached as ‘trace.txt’ to this post.

The strange thing is that this is a sudden problem. My solution is years old, and is being used - successfully by other clients (on older versions of servoy, and using Sybase in place of Postgres). I am working through trying to remove aggregates of calculations, and trying to work out what is slowing it down - on many fronts, however the solution (in its original - unstreamlined form) is working perfectly happily for some customers.

I am at a loss.

trace.txt (90.5 KB)

6

That is lot of action! Can you do the same and make a screenshot of the performance tab in the servoy-admin page? (clear it first)

7

Exact same action in Server admin log after clearing log first (Screenshot attached…)

Thank you Harjo :)

Screen Shot 2013-11-06 at 09.28.16.png
Screen Shot 2013-11-06 at 09.28.16.png3414×2004 918 KB

8

if that is just from startup and nothing more then that could potentially be 50 calls to the server
Doesn’t have to be that much because it also could be that server does combine some queries (mostly that 15 or 10) those are then only 1 or 2 actual calls.

But in your trace i did see a lot of queries going to the server you should try to minimize that