databaseManager.recalcualte(record) in HC, calcs in module

[jbader]

I am wondering how databaseManager retrieves its list of calculations to recalculate when calling databaseManager.recalculate. Specifically I am wondering about if I am logged into an HC solution, which has no calcs of its own, but that includes modules that do have calcs, and I call databaseManager.recalculate, will Servoy look for calcs in the modules as well (and recalc them), or will it only look for calcs in the main/parent solution?

If it won't look in the modules, how can I recalculate calculations that are in modules of a headless client solution without touching the UI (e.g. controller.* etc.).

Thanks in advance.

[jbader]

This is an urgent question for me. I'd really appreciate understanding the mechanism used by recalculate for gathering which calcs to calc in HC.

Of if this is documented somewhere, please point me to the docs.

Again, thanks in advance.

[rgansevles]

Jeff,

The concept of modules only exists in developer.
Runtime, a solution consists of all code of main solution and modules merged.

So all code looking for solution objects finds stuff regardless of whether it is defined in a module or the main solution.

Hope this helps,

Rob

[jbader]

Hi Rob,

Thanks a lot for the reply.

I understand what you are saying about developer and modules, but I think modules do play a role in the app server b/c if a solution does not include the "module," then obviously none of the modules calcs or codebase is available to the parent solution right?

But in regards to calcs specifically, what I think you are saying is that if a solution includes modules, then all of the calcs both in the solution and any of its modules will recalculate when databaseManager.recalculate is called.

Correct?

[rgansevles]

Jeff,

Yes, obviously the solution cannot execute code from a missing module.

And yes, all loaded calcs (either in solution or module) are executed by databaseManager.recalculate(), are you seeing something else?

Rob

[jbader]

I am having an issue whereby calling dbmgr.recalc from an HC doesn't recalc calcs in the included mods, and no exception is thrown. The same code in smart client works fine.

[rgansevles]

Can you isolate it in a small sample?

Rob

[jbader]

Sure. I'll do my best to replicate although before I do, I assume that there are plenty of Servoy developers that recalcualte calculations in modules from within HC right? Can someone confirm that this works consistently for them? And by this I mean:

Calcualtions are in a module
HC includes the module
HC runs databaseManager.recalculate on records that have calculations that are in the module, and not in the HC solution itself.

This must be common enough. Who has this working from the community?

Thanks

[Robert Huber]

Hi Jeff

We have for a very long time problems executing calcs at all. Not in a HC, but from a SmartClient or a SmartClient started from within the Servoy Developer.
We have a table containing around 13000 records. If we run databaseManager.recalculate(record) in a loop, the first couple of records are calculated about a (stored) calc per second. After already about 50 records, the speed slows down dramatically, going to hours (yes, hours) after having calculated about 1000 records. After running the method for 3 days, we were about at 1200 records!
This is a real problem for us as we absolutly need these calcs.
Currently, the only way we can get these calcs beiing calculated is by doing them in packages of 200, then manually stop the SmartClient, start the SmartClient again and execute the next 200 records. A real pain and in fact unusable, but we didn't get any help till now. If only Servoy guys could explain why calcs get soooo slow after a short time?
Sorry, I can't help you as we need help as well, I just can feel with you. In my opinion, something is severe wrong with calcs in Servoy, I may be wrong, but then I would like to know the solution.
Servoy could just fill a table with enough records and execute some stored calcs and see if it works - I dont think it will.

Regards, Robert

PS: We use stored calcs for example when creating Reports with JasperReports.

I am having an issue whereby calling dbmgr.recalc from an HC doesn't recalc calcs in the included mods, and no exception is thrown. The same code in smart client works fine.

[jcompagner]

robert, what you have is very weird,
that really should go pretty fast, i guess somehow it is hitting a memory limit (that you should be able to see in the about dialog)
What kinds of queries are all generated when you do that loop?

So flush the performance page on the admin pages when it is looping, what do you all see happening?
I guess that it is a bit more then just the update statements for the stored calculated column.

[jbader]

Does anyone use HC to perform recalculations and does it actually work in any consistent way for them?

Anyone?

[Robert Huber]

Hi Johann

robert, what you have is very weird,
that really should go pretty fast, i guess somehow it is hitting a memory limit (that you should be able to see in the about dialog)

I absolutly agree with you. Memory usage is NOT shown while running the loop - only after it is finished the memory is shown. It seems to have 517'760 K allocated - not that much. The machine has 6 GB of physical RAM installed. See attached screenshot.

What kinds of queries are all generated when you do that loop?

I see nothing except one query in the performance data menu of the Servoy Application Server - see attached screenshot. The server log page has no entry at all.

So flush the performance page on the admin pages when it is looping, what do you all see happening?
I guess that it is a bit more then just the update statements for the stored calculated column.

Where should I be able to see that?

This is our loop:

Code: Select all

function handleCalculateTable() {
   var tableNamesArray = databaseManager.getTableNames(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()));
   var selectedTable = plugins.dialogs.showSelectDialog('Calculations', 'Select a table to execute the Calculations: ', tableNamesArray);

   if (selectedTable) {
      var currentFoundset = databaseManager.getFoundSet(databaseManager.getDataSourceServerName(currentcontroller.getDataSource()), selectedTable);
      currentFoundset.loadAllRecords();
      var countCurrentFoundset = databaseManager.getFoundSetCount(currentFoundset);

      application.output(new Date() + ': Start executing of ' + selectedTable + '(' + countCurrentFoundset + ')');
      
      //for (var j = 0; j < countCurrentFoundset; j += 200) {
         for (var i = 0; i < 400; i++) {
            application.output(new Date() + ': Record ' + (i + 1) + '/' + countCurrentFoundset + ' of table ' + selectedTable);
            var currentRecord = currentFoundset.getRecord(i)
            databaseManager.recalculate(currentRecord);
            if (!databaseManager.saveData(currentRecord)) {
               //var exception = currentRecord.exception.getMessage();
               application.output('Faild-Record: ' + databaseManager.getFailedRecords());
               //application.output('Exception: ' + exception);
            }
         }
      //}
      application.output(new Date() + ': Finished executing of ' + selectedTable + '(' + countCurrentFoundset + ')');
      plugins.dialogs.showInfoDialog('Calculations', 'All Calculations are Executed', 'OK');
   }
}


And this is one of our calcs involved. I should note that there are calc dependencies as the whole thing is a chain for calculating a school promotion for the period of 6 semesters. HS and FS just means Herbstsemester and Frühlingssemester, these are the first and second semesters within a year. The code is here to get an idea that this calcs are not just very simple calcs.

Code: Select all

function promotion_code() {
   if (!utils.hasRecords(_class_members_enroled_for_profile_positions)) {
      return null;
   }

   // Should the period promotion be calculated?
   // For the Maturitätsschule this should be done for level_number and fraction_name:
   //       1 HS
   //       1 FS
   //      2 HS
   //      2 FS
   //      3 HS (for negative promoted students only)
   //      3 FS (since a promotion is done for 1 year, the former promotion code is the one a year ago for positive promoted students)
   // For the FMS this should be done for level_number and fraction_name:
   //       1 HS
   //       1 FS
   //      2 HS (for negative promoted students only)
   //      2 FS (since a promotion is done for 1 year, the former promotion code is the one a year ago for positive promoted students)

   var currentLevelAndFractionName = class_level_number + ' ' + class_period_fraction_name;
   var currentProfile = _class_members_enroled_for_profile_positions.profile_positions_referenced_by_profile_definitions.profile_definitions_typified_by_profiles;

   if (!utils.hasRecords(currentProfile.profiles_hosted_by_school_units)) {
      // Since the profile is not hosted by any school_unit, the type of school is not defined.
      // And without type of school the regulation - which is the legal basis - is undefined.
      //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, 'Für das Profil ist kein Schultyp definiert.');
      return null;
   }

   // Find the promotion the student startet this period with:
   // If a promotion_code_start exists, use that one.
   // If we are in 3 FS in MS or 2 FS in FMS
   // - Use promotion_code start if set
   // - Use def if student started last period positive.
   // - Use former promotion code if student started negative.
   // Else
   //  - Check, if a start code exists. If not, use the former promotion code. Or - if set - the overwrite former promotion code.
   var schoolType = currentProfile.profiles_hosted_by_school_units.school_type_code;
   var initialPromotionCode = null;
   if (promotion_code_start) {
      initialPromotionCode = promotion_code_start;
   }
   else {
      initialPromotionCode = _promotion_code_former;
   }

   // Do not promote the student if we are in 3 HS in MS or 2 HS in FMS and the student was positively promoted
   if (schoolType == 'MS') {
      // The basis is Maturitätsschulverordnung
      var periodsForPromotionCalculation = ['1 HS', '1 FS', '2 HS', '2 FS', '3 FS'];
      if (periodsForPromotionCalculation.indexOf(currentLevelAndFractionName) == -1) {
         // The current period is not one of the normal periods where a promotion calculation should be done.
         // Only for negative students in 3 HS a promotion calculation should be done.
         if (currentLevelAndFractionName == '3 HS' && initialPromotionCode != 'def') {
            // Continue. A promotion calculation should be done.
         }
         else {
            // In this period no promotion has to be calculated.
            return null;
         }
      }
   }
   else if (schoolType == 'FMS') {
      // The basis is FMS-Verordnung
      var periodsForPromotionCalculation = ['1 HS', '1 FS', '2 FS'];
      if (periodsForPromotionCalculation.indexOf(currentLevelAndFractionName) == -1) {
         // The current period is not one of the normal periods where a promotion calculation should be done.
         // Only for negative students in 2 HS a promotion calculation should be done.
         if (currentLevelAndFractionName == '2 HS' && initialPromotionCode != 'def') {
            // Continue. A promotion calculation should be done.
         }
         else {
            // In this period no promotion has to be calculated.
            return null;
         }
      }
   }

   // Find the possible promotions for this perdiod
   if (!utils.hasRecords(_class_members_available_profile_promotions)) {
      // No promotions defined for this period
      return null;
   }
   else {
      var possiblePromotions = new Array();
      for (var i = 1; i <= _class_members_available_profile_promotions.getSize(); i++) {
         possiblePromotions.push(_class_members_available_profile_promotions.getRecord(i).promotion_code);
      }
   }

   // Check if all promotion subjects are marked.
   for (var j = 1; j <= _class_members_enroled_for_profile_positions.getSize(); j++) {
      var profilePosition = _class_members_enroled_for_profile_positions.getRecord(j);
      if (!profilePosition.is_dispensed
      && profilePosition.profile_positions_referenced_by_profile_definitions.allow_assessment
      && profilePosition.profile_positions_referenced_by_profile_definitions.significance
      && !profilePosition.mark) {
         // Profile position found without mark.
         return null;
      }
   }

   // Start the evaluation of the achievement. For this, compare the different limits
   // defined in the profile with the current marks, avarage and balance.
   var achievementSufficient = true;

   if (currentProfile.min_average_of_promotion_marks != null) {
      // Check average of promotion marks
      if (_average_of_promotion_marks < currentProfile.min_average_of_promotion_marks) {
         achievementSufficient = false;
      }
   }

   if (currentProfile.max_number_of_insufficient_marks != null) {
      // Check number of unsufficient marks
      if (_number_of_insufficient_promotion_marks > currentProfile.max_number_of_insufficient_marks) {
         achievementSufficient = false;
      }
   }

   if (currentProfile.balance_calculation_promotion != null) {
      if (_balance_of_promotion_marks < currentProfile.min_balance_of_promotion_marks) {
         achievementSufficient = false;
      }
   }

   var promotionCode = null;
   // Depending on previous promotion(s) find current promotion to return

   if (initialPromotionCode == 'def') {
      if (achievementSufficient) {
         if (possiblePromotions.indexOf('def') != -1) {
            promotionCode = 'def';
         }
         else {
            //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['def']));
         }
      }
      else if (!achievementSufficient) {
         if ( (schoolType == 'MS' && currentLevelAndFractionName == '3 FS')
         || (schoolType == 'FMS' && currentLevelAndFractionName == '2 FS')) {
            // The next level cannot be started with a negative promotion. The student has to repeat the class.
            // Or, if the maximum number of remotions is reached, he/she gets promotion code 'aus' and will
            // be excluded from the school.
            if (_number_of_remotions < currentProfile.max_number_of_remotions) {
               if (possiblePromotions.indexOf('rem') != -1) {
                  promotionCode = 'rem';
               }
               else {
                  //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['rem']));
               }
            }
            else if (_number_of_remotions >= currentProfile.max_number_of_remotions) {
               if (possiblePromotions.indexOf('aus') != -1) {
                  promotionCode = 'aus';
               }
               else {
                  //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['aus']));
               }
            }
         }
         else {
            if (possiblePromotions.indexOf('prov') != -1) {
               promotionCode = 'prov';
            }
            else {
               //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['prov']));
            }
         }
      }
   }
   else if (initialPromotionCode == 'prov' || initialPromotionCode == 'vprov') {
      if (achievementSufficient) {
         if (possiblePromotions.indexOf('def') != -1) {
            promotionCode = 'def';
         }
         else {
            //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['def']));
         }
      }
      else if (!achievementSufficient) {
         if (currentLevelAndFractionName == '1 HS') {
            if (possiblePromotions.indexOf('pzNeg') != -1) {
               promotionCode = 'pzNeg';
            }
            else {
               //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['pzNeg']));
            }
         }
         else if (_number_of_remotions < currentProfile.max_number_of_remotions) {
            if (possiblePromotions.indexOf('rem') != -1) {
               promotionCode = 'rem';
            }
            else {
               //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['rem']));
            }
         }
         else if (_number_of_remotions >= currentProfile.max_number_of_remotions) {
            if (possiblePromotions.indexOf('aus') != -1) {
               promotionCode = 'aus';
            }
            else {
               //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['aus']));
            }
         }
      }
   }
   else if (initialPromotionCode == 'vpz') {
      if (achievementSufficient) {
         if (possiblePromotions.indexOf('def') != -1) {
            promotionCode = 'def';
         }
         else {
            //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, 'Die berechnete Promotion def ist für diese Periode nicht definiert.');
         }
      }
      else if (!achievementSufficient) {
         if (possiblePromotions.indexOf('pzNeg') != -1) {
            promotionCode = 'pzNeg';
         }
         else {
            //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['pzNeg']));
         }
      }
   }
   else if (initialPromotionCode == 'rem') {
      if (achievementSufficient) {
         if (possiblePromotions.indexOf('def') != -1) {
            promotionCode = 'def';
         }
         else {
            //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['def']));
         }
      }
      else if (!achievementSufficient) {
         if (possiblePromotions.indexOf('aus') != -1) {
            promotionCode = 'aus';
         }
         else {
            //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningCalculatedPromotionNotDefined', ['aus']));
         }
      }
   }
   else {
      // The initialPromotionCode is not expected
      //globals.protocolWarning(i18n.getI18NMessage('hades.ach.protocol.title.promotion.ucl'), _as_string, i18n.getI18NMessage('hades.ach.protocol.msg.warningNoInitialPromotionCode'));
   }

   return promotionCode;
}

Now I am courious on your answer.

Best regards, Robert

[jcompagner]

it is not a matter how much memory the client machine has..
It is how much it can take from that 6GB

What is the memory setting that you push the the client, what did you configure on the admin page for that?

If you show the dialog from the beginning, maybe you can do a few application.updateUI() calls now and then then it should update.

But that is everything you get in the performance log? So nothing is really written to the database at all? So nothing is really changed? (you dont see any update statements)

Then it has to be a almost run of of memory issue.

edit: quickly looking at your calc then you load in quite a lot of relations (or is that currentprofile relation pretty much the same for all the record you test?)

[Robert Huber]

it is not a matter how much memory the client machine has..
It is how much it can take from that 6GB

What is the memory setting that you push the the client, what did you configure on the admin page for that?

See attached file - servoy.maxClientHeap = 1536. If I set it higher to 2 GB, the SmartClient doesn't start anymore!? Although as said the machine has 6 GB of RAM.
I always thought this memory settings affect only the SmartClient, but not the one started from within the Servoy Developer!?

If you show the dialog from the beginning, maybe you can do a few application.updateUI() calls now and then then it should update.

No it doesn't, unfortunatly

But that is everything you get in the performance log? So nothing is really written to the database at all? So nothing is really changed? (you dont see any update statements)

Then it has to be a almost run of of memory issue.

But then which setting does help solve this issue?

edit: quickly looking at your calc then you load in quite a lot of relations (or is that currentprofile relation pretty much the same for all the record you test?)

Mostly, except where it is enhanced by another relationship part as in schoolType for example.

No I am looking forward what nice memory settings I can make to make it work at all.

Best regards, Robert

PS: If I run the loop, after recalculating the first 1000 records of 13'000 records, it takes hours to calculate the next record. So I can NEVER recalculate the 13'000, it's just NOT possible. On the other hand, I need to have these calcs correct, otherwise the whole promotion breaks down and gives only incorrect results - and we can't stem against all the students if they get wrong results

[jcompagner]

are we looking at a client running inside developer? or a real smart client?

Then the admin page maxHeap doesn't do anything then it is your servoy.ini file that does it.

You can't make it more the 2GB if your client vm where you smart client runs in is 32Bit, you need a 64bit jvm for that.

But still what i don't get is that you dont see loads of queries generated in the performance log...
You should see relation load queries of all the relations you touch, you should see normal foundset queries to get the next block of records..
that all besides the update queries of all the stored calcs that you update.. (why else would you run it)