Servoy connection to the web server

ahmad · December 7, 2004, 3:06am

Hi,

We have developed a CMS in Servoy with MySql as backend. This has been installed on a desktop in Australia
We have the website hosted on a Server in Hong Kong (PHP+ MySql)
To sync the data between the Servoy & Website, we have created the connection for the web database (MySql) in Servoy
We also have the forms for the web tables in Servoy so that we can use them in the methods

To update the Servoy data to the web data, the user clicks a button from Servoy which then loops through the Servoy local form data and creates new records in web forms

Initially when there were less number of data, the update happened in few seconds.

As the database growing with lot of media & other records (the db size is now 200MB) the connection to the web database became extremely slow
It takes more than 10 minutes to connect with the web server after the user clicked the update button from Servoy. The looping process starts after 10 minutes. The update works normally after the connection is done
I think Servoy loads the whole web database locally first and then starts the update process.
This is so time consuming for the user since it takes very long time to update something
I’m not sure what we are doing wrong.

?? Is this the effcient way of updating?

?? Do you have any better thoughts to update the web data in a very fast manner?

Thanks

ROCLASI · December 7, 2004, 3:03pm

ahmad:
?? Is this the effcient way of updating?

I can answer that with one word “NO”.

ahmad:
?? Do you have any better thoughts to update the web data in a very fast manner?

There are several options:

let the Servoy solution edit directly in the database in Hong Kong. Ofcourse your website/solution should be designed for being able to edit items before putting them live.
update only the changed records. Ofcourse you need to track what records have been touched/removed. This can be tricky.
Backup the database and ftp it to the website and restore it there.
Ofcourse you can only do this is the database is an exact copy of the one in Australia.
If the databases are different in design then you can export your data to files, ftp them to the website and import them there.
A local import is MUCH faster then doing it over a network.

HTH

ahmad · December 8, 2004, 8:35am

let the Servoy solution edit directly in the database in Hong Kong. Ofcourse your website/solution should be designed for being able to edit items before putting them live.

Unfortunately this option will be slow between Aus & HK. May be this will work if the webserver is in AU and also the web host aggree to host the servoy

update only the changed records. Ofcourse you need to track what records have been touched/removed. This can be tricky.

I have a field in all the tables called “OK for www”. The user will decide which one to go online giving more flexibilty to take out some records from web but still have it in the Servoy.

When the user starts the update, a servoy method deletes all the records in web first, finds the records which are “OK for www”, loop through them and create on the web forms.

Please note that the media files are stored in a seperate table. The media files are not uploaded everytimes. The system knows which are the new media files and upload them only.

As I said before only text data are flushed out and inserted again.

Backup the database and ftp it to the website and restore it there.
Ofcourse you can only do this is the database is an exact copy of the one in Australia.

This is a tedious process because the database involves huge amount of data and also not all the data need to be updated all the time

If the databases are different in design then you can export your data to files, ftp them to the website and import them there.
A local import is MUCH faster then doing it over a network.

I prefer this method because we used to synch the website data like what you have explained above with FMP. But when posted suggestion in this forum some one suggested to create a connection for the server and have the tables connected to the local forms. I think this is the simple method but unfortuanely the connection to the server takes a long time. After connected then the data update works normally.

So I would like to know what’s the exact reason for the delay in the begining. Does servoy loads all the web data to the local servoy when the connection is made for the first time

Thanks

ROCLASI · December 8, 2004, 10:06am

ahmad:

If the databases are different in design then you can export your data to files, ftp them to the website and import them there.
A local import is MUCH faster then doing it over a network.

I prefer this method because we used to synch the website data like what you have explained above with FMP. But when posted suggestion in this forum some one suggested to create a connection for the server and have the tables connected to the local forms. I think this is the simple method but unfortuanely the connection to the server takes a long time. After connected then the data update works normally.

So I would like to know what’s the exact reason for the delay in the begining. Does servoy loads all the web data to the local servoy when the connection is made for the first time

Thanks

To tell you the exact reason would be a bit hard because I don’t know your solution and circumstances.
Apparently your connection is a huge bottleneck. So thats one reason.

Do the tables of the website have a lot of indexes other than the obvious PK’s and maybe some FK’s ?
Inserts/updates on such a table can be costly because it has to update the indexes on every insert/update. Ofcourse you would only notice this with a LOT of data.

Normally Servoy doesn’t load all the data at once. It would only load the first 200 PK’s of a foundset.
Maybe you can post your method here so we can see what might be the bottleneck.

ahmad · December 8, 2004, 12:07pm

ROCLASI:
Do the tables of the website have a lot of indexes other than the obvious PK’s and maybe some FK’s ?
Inserts/updates on such a table can be costly because it has to update the indexes on every insert/update. Ofcourse you would only notice this with a LOT of data.

I have indexed only the PKs

I have attached here the code I have used. I don’t know how much it can help you.

Frankly, the media table on the web has only about 100 records (nearly 200MBs). I have a form in Servoy for that table. When I refer any one of the web forms in my method, the initial connection time takes 10mins and then the rest of the method is processed

Thanks

note: The attachment option is not visible in this forum. So I just paste here. Sorry..

application.setErrorCapture(true);
globals.errorCode = application.getLastErrorCode();


if (globals.stopUpload == 1 || globals.errorCode != 0 )return;

var btnName       = arguments[0];
var param1        = arguments[1];
var param2        = arguments[2];
var param3        = arguments[3];

case "btnUploadAll":
    uploadToWeb('uploadContact');
    uploadToWeb('uploadProject');
    uploadToWeb('uploadDocument');
    uploadToWeb('uploadMedia','document_id_media_to_document');
    uploadToWeb('deleteWebMedia');

if (globals.stopUpload != 1 && globals.errorCode == 0 )  {
   plugins.dialogs.showInfoDialog('Update Result', 'Successfully Uploaded !');
}
else if (globals.stopUpload == 1  ) {
   plugins.dialogs.showErrorDialog("Upload Cancelled ","Please Upload again for proper website operation ! ");
}
else if (globals.errorCode == 1  ) {
   plugins.dialogs.showErrorDialog("Upload Error ","Please Check your internet connection and Upload again ! ");
}

globals.nav_closeFormInDialog();
 break;


case "uploadContact":
   var frmLocal = forms.frmContactList;
   var frmWeb   = forms.frmWebContact;
   uploadToWeb('checkOkForWeb',frmLocal);
   uploadToWeb('upload',frmLocal,frmWeb);
   break;

case "uploadProject":
   var frmLocal = forms.frmProjectList;
   var frmWeb   = forms.frmWebProject;
   uploadToWeb('checkOkForWeb',frmLocal);
   uploadToWeb('upload',frmLocal,frmWeb);
   break;

case "uploadDocument":
   var frmLocal = forms.frmDocumentList;
   var frmWeb   = forms.frmWebDocument;
   uploadToWeb('checkOkForWeb',frmLocal);
   uploadToWeb('upload',frmLocal,frmWeb);
   break;


case "uploadMedia":
  var frmLocal   = forms.frmMediaList;
  var frmWeb     = forms.frmWebMedia;
  var relation   = param1;
  frmLocal.controller.find();
  frmLocal.uploaded    = 0;
  frmLocal[relation].ok_for_web   = 1;
  globals.newWebRecordCount = frmLocal.controller.search();

  uploadToWeb('upload',frmLocal,frmWeb);
  break;


case "checkOkForWeb":
  var frmLocal = param1;
  frmLocal.controller.find();
  frmLocal.ok_for_web = "1";
  globals.newWebRecordCount = frmLocal.controller.search();
  break;


case "upload":
  var frmLocal = param1;
  var frmWeb   = param2;
  var tableName = frmLocal.controller.getTableName()
  var table =databaseManager.getTable(frmLocal.controller.getServerName(),tableName);
  var columnNameArray = table.getColumnNames();

  //*********** THE COLUMN NAMES ARE SAME ON BOTH LOCAL & WEB SERVER **********//

  if ( globals.newWebRecordCount > 0 ){

    if (frmLocal != forms.frmMediaList){
    	frmWeb.controller.deleteAllRecords();
    }

    globals.utilities('setProgressionPresets', forms.frmWebUpdateProgression, 'progress', globals.newWebRecordCount);

       for ( var i = 1 ; i <= globals.newWebRecordCount ; i++ ) { 
          if (globals.stopUpload == 1  || application.getLastErrorCode() != 0){
             globals.errorCode = application.getLastErrorCode()
           break;
          }
          var record = frmLocal.foundset.getRecord(i);

          if (frmWeb == forms.frmWebMedia){
            frmWeb.controller.find();
            frmWeb.media_id = record['media_id'];
            var duplicate = frmWeb.controller.search();
            if (duplicate > 0){
             frmWeb.controller.deleteRecord();
            }
          } 
              frmWeb.controller.newRecord(); 
   	          for(var j=0;j<columnNameArray.length;j++){
	             frmWeb[columnNameArray[j]] = record[columnNameArray[j]];
	          }
	
     	    if (frmLocal == forms.frmMediaList){
    	        record['uploaded'] = 1;
            }

	        globals.utilities('increaseProgression', forms.frmWebUpdateProgression, 'progress', i+1, frmWeb.controller.getName().substring(6));
		
	   frmWeb.controller.saveData();
	}
} 
break; 

// DELETE THE MEDIA FILES IN WEB WHICH ARE NO MORE NEEDED ON THE WEB
case "deleteWebMedia":
   //========================================================//

   var frmMediaLocal = forms.frmMediaList;
   frmMediaLocal.controller.loadAllRecords();
   var dataArray  = databaseManager.getFoundSetDataProviderAsArray(frmMediaLocal.foundset, "media_id");
   var idString = "";
   var totalRows = dataArray.length;
   globals.utilities('setProgressionPresets', forms.frmWebUpdateProgression, 'progress', totalRows);

   for (i=0; i<totalRows ; i++){
       idString += "'" + dataArray[i] + "',";
   }
   if ( utils.stringRight(idString,1) == ","){
      idString = utils.stringMiddle(idString,1, idString.length -1)
   }
   //========================================================//

   var SQL = "SELECT media_id " +
                    "FROM media WHERE media_id NOT IN (" + idString + ")";

   var dataset   = databaseManager.getDataSetByQuery(globals.webServerName, SQL, null, 10000000);
   forms.frmWebMedia.controller.loadRecords(dataset);
   forms.frmWebMedia.controller.deleteAllRecords();
   globals.utilities('increaseProgression', forms.frmWebUpdateProgression, 'progress', totalRows, 'Deleting Media');

break;
}

ROCLASI · December 8, 2004, 2:12pm

Interesting method.
A few comments/questions:

I see you use CASE statements. I don’t think that is part of the JavaScript syntax. At least I can’t find any reference of it in any JS manual.- Those webforms you refer to in your method. Do they contain the (heavy) media fields?- Are those webforms used for other than this upload method?

faheemhameed · December 8, 2004, 3:17pm

ROCLASI:
I see you use CASE statements. I don’t think that is part of the JavaScript syntax. At least I can’t find any reference of it in any JS manual

I referred from http://www.devguru.com/Technologies/ecm … witch.html

Those webforms you refer to in your method. Do they contain the (heavy) media fields?

The media table has the heavy files of (~5MB each) but the field is not displayed on the form

Are those webforms used for other than this upload method?

No.. They referred only in the upload method

Cheers

ROCLASI · December 8, 2004, 6:36pm

faheemhameed:

ROCLASI:
I see you use CASE statements. I don’t think that is part of the JavaScript syntax. At least I can’t find any reference of it in any JS manual

I referred from http://www.devguru.com/Technologies/ecm … witch.html

The Switch statement I know of. I guess the reason for my confusion is that there is no Switch statement in your script. Just the cases.
You might have copy/pasted an incomplete method.

faheemhameed:

Those webforms you refer to in your method. Do they contain the (heavy) media fields?

The media table has the heavy files of (~5MB each) but the field is not displayed on the form

Are those webforms used for other than this upload method?

No.. They referred only in the upload method

That’s interesting.
Time to do some ‘bug hunting’ I’d say. Are you familiar with the application.output() command ?
I would use that to track parts of your method to see how long certain things take.

I suggest you write a global method that outputs the time it took from the last time you called this global method.
This way you can see what parts of your method is so costly.

david · December 8, 2004, 9:27pm

To begin with, even if your web server were on a local connection, this process would take a long time to negotiate with Servoy as the intermediary. Servoy’s strength is not batch processing a large number of updates at once.

The solution is to handle the transaction outside of Servoy. A couple of ways I’ve done this:

Method 1

Create a text file with all of your data wrapped in sql insert statements. Add in drop and create table statements to clear your web tables. Use an ftp bean to drop this file into a directory on your web server. Set up a cron job on your web server to run a shell script that updates your web database from this new file on a periodic basis.

If you need the update to happen exactly when the user specifies, set up a web page that triggers the shell script. Call this web page with one of Servoy’s http functions.

Method 2

Create a couple of web pages to delete all records in a table and add records to the table. First call the “delete” web page to clear the table. Then for each record to add, create a post URL string with all the column name/data pairs and send this string to the “add” web page. You will need to encode the URL string before passing it along.

David
Soltex

ahmad · December 9, 2004, 12:45pm

david:
Create a text file with all of your data wrapped in sql insert statements. Add in drop and create table statements to clear your web tables. Use an ftp bean to drop this file into a directory on your web server. Set up a cron job on your web server to run a shell script that updates your web database from this new file on a periodic basis.

Thanks David & ROCLASI,

I think I have to go with David’s method 1 which is similar to ROCLASI’s 4th option.

Thanks again for your assistance

Topic		Replies	Views
Overall slowness with Servoy Client Classic Servoy	5	2835	December 9, 2004
Servoy performance Classic Servoy	3	5662	September 22, 2003
Exporting data from web & import into servoy Classic Servoy	5	3658	February 12, 2005
Synching with web inserted data -will this work? Classic Servoy	3	2739	October 4, 2004
Delay from remote server INSERT and web display Classic Servoy	8	4578	May 19, 2005

Servoy connection to the web server

Related topics