Function takes a very lot of CPU load for long time

Agents, Actions and Automation
1

Dear Forum,

I have written a function that has a bad performance. I use it in an edict but It takes to long to run and sometimes causes Tinderbox to crash when exporting the document. It also seriously slows down or blocks the execution of stamps.

Goal of the function is to calculate the number of working days (excl weekendday’s saturday and sunday).

When starting Tinderbox the function takes about 13 minutes to run over 346 notes. This repeats every hour or so when the edict runs again. Apparently I did something wrong or maybe such a function is not workable in Tinderbox.
The function is as follows:

function fDoorloopTijd() {

var:date vStart = $DatumIn.format("D-M0-y");
var:date vEnd = $DatumUit.format("D-M0-y");
var:date vLoopDate = vStart.format("D-M0-y");
var:num vDagen = days(vStart,vEnd);
var:list vSetDagen=;
var:num vDoorloop=;

vLoopDate = date(vLoopDate + "1 day"); //Dag van ontvangst telt niet mee
vEnd = date(vEnd + "1 day"); //Dag van versturen telt wel mee

// de routine maakt een lijst van de dagen vanaf start tot en met eind
while(vLoopDate.format("D-M0-y")!=vEnd.format("D-M0-y")){
   vSetDagen+=vLoopDate.weekday+";";
   vLoopDate = date(vLoopDate + "1 day");
};

// De doorlooptijd wordt berekend op het aanfal weekdagen
$Doorlooptijd = vSetDagen.count_if(anItem,anItem<6);  //Weekend dagen tellen niet mee
};

Is there a way to improve this function?

Many thanks in advance for your help.
Jan

2

Do the start/end dates in those 346 notes change frequently, or not at all? If the later, why not just calculate work days when the note is created? Calculating the interval between start and end dates, then dividing by 7 and multiplying that result by 5 and then adding +1 should get you what you want.

3

@PaulWalters Thanks for you response.

Good question!
The dates do not change regularly but they CAN change and I want to be sure that the calculated number is correct.
I don’t think your suggestion for calculating the number of working days will work because I specifically do not want to include the Saturdays and Sundays. That’s why I make a list of the weekdays and exclude day 6 and 7 from the calculation.
But maybe I am missing something :slight_smile:

4

Yes, that simple formula works:

interval(beginDate, endDate)/7)*5

The first part calculates the number of 7-day weeks (or fractions) in a period, and then assumes that work weeks are 5-day weeks, i.e., no weekend days, so weeks * 5 is the number of workdays. Of course, the formula doesn’t account for civic or other non-work days or the begin or end days. Adjust as needed.

I doubt you need the function to examine the formats of the dates, since formats are used for human display, while the machine doesn’t care about day names or day format indicators.

5

Fleshing out the above idea:

function fDoorloopTijd() {

// assumption: $DatumIn & $DatumUit are Date-type attributes
// number of whole days between start and end
var:number vDays = days($DatumIn,$DatumUit);

// day of week of start day
var:number vStartDay = $DatumIn.weekday;

// set a result var for number of workdays in start/end duration
var:number vWorkDays=0;

// number of weekdays within whole weeks 
var:number vWorkWeekDays = floor(vDays/7)*5;

// number of days in remaining partial week
var:number vPartialWeekDays = mod(vDays,7);

// work out number of weekdays duration
//  add item variables
var:number vNumber1=0;
var:number vNumber2=0;
if(vPartialWeekDays>0){
  // i.e. 1-6 days of a part week
  vNumber1 = vStartDay+vPartialWeekDays;
  // if interim count 
  if(vNumber1==7){vNumber2 = vNumber1 - 2;};
  if(vNumber1==6){vNumber2 = vNumber1 - 1;};
  vWorkDays = vWorkWeekDays+vNumber2;
}else{
  vWorkDays = vWorkWeekDays;
}
return vWorkDays;
} // END Function

IMPORTANT:

  • the above is not tested (busy on calls) - so other users here please try/correct the code above!
  • the above cannot take account of national holidays, summer saving time, or leap year days!

Beware the siren trap of perfection - the perfect is can be the enemy of progress.

If you need really accurate counts, I’m sure there will be a command line tool you can probably use. So, if the data changes infrequently, doing a command line (i.e runCommand() call) per note might be more efficient.

Edit: fixed stray space in code sample casuing non-function: var: number → var:number
Edit #2: mislabelled var, should be: vNumber1 = vStartDay+vPartialWeekDays;
Edit #3: mislabelled var, should be: vWorkDays = vWorkWeekDays+vNumber2;

6

For very accurate results use Excel’s NETWORKDAYS("begin","end") function.

I suppose we could ask @Eastgate to build the function into Tinderbox. I don’t recall seeing much overt demand for this, but maybe the silent majority is yearning for the feature.

1 Like
7

I’m somewhat tied up at the moment, but I’d like to point out some possible inefficiencies in the original function.

  1. The function isn’t guarded against the start date or end date being ‘never’. That shouldn’t happen, but if it did, the loop would run for a very long time.

  2. var:date vStart = $DatumIn.format("D-M0-y");. I think formatting the date as a string isn’t necessary. Why not simple var:date vStart = $DatumIn.; ? Similarly elsewhere.

  3. Two dates are == if they are on the same calendar day, so the loop condition could be while(vLoopDate!=vend).

  4. Adding “+ 1 day” to a date is slower than adding an interval.

var:interval aDay=interval("24:00");
...
   vLoopDate=vLoopDate+aDay;
  1. You’re building a list, but only because you want to count its elements. Consider simply counting:
$Doorlooptijd =0;
while(vLoopDate<vEnd) {
     if (vLoopDate.weekday<6) { $Doorlooptijd += 1;}
    }

None of this is tested; just some things that occur to me.

8

OK, I think I’ve ironed out the bugs in my earlier code (test file below). The function doing the work:

function fNumberWorkDays(iStart:date,iEnd:date){

var:number vDays = days(iStart,iEnd);

// day of week of start day
var:number vStartDay = iStart.weekday;

// set a result var for number of workdays in start/end duration
var:number vWorkDays=0;

// number of weekdays within whole weeks 
var:number vWorkWeekDays = floor(vDays/7)*5;

// number of days in remaining partial week
var:number vPartialWeekDays = mod(vDays,7);

// work out number of weekdays duration
//  add item variables
var:number vNumber1=0;
var:number vNumber2=0;

if(vWorkWeekDays==0){
  vNumber1 = vStartDay+vPartialWeekDays;
  if(vNumber1>7&vStartDay<6){vNumber2 = vNumber1-vStartDay-2;};
  if(vNumber1==7){vNumber2 = vNumber1-2;};
  if(vNumber1==6){vNumber2 = vNumber1-1;};
  vWorkDays = vNumber2;
}else{
  if(vPartialWeekDays>0){
    // i.e. 1-6 days of a part week
    vNumber1 = vStartDay+vPartialWeekDays;
    // if interim count 
    if(vNumber1>7&vStartDay<6){vNumber2 = vNumber1-vStartDay-2;};
    if(vNumber1==7){vNumber2 = vNumber1-2;};
    if(vNumber1==6){vNumber2 = vNumber1-1;};
    vWorkDays = vWorkWeekDays+vNumber2;
  }else{
    vWorkDays = vWorkWeekDays;
  };
};

return vWorkDays;
}; ///END

In the test file, in the library note for the above function there is a copy of the function called fDebugNumberWorkDays. It does the same as the above but logs some of the interim variables into a note at path /log.

The function is called for note at path /Text via this edict:

$MyNumber = fNumberWorkDays($StartDate,$EndDate);

Buy in actual use I’d put this as an $Edict of a prototype (and setting the prototype’s $EdictDisabled to true).

Here is the file: ElaspedWorkDays.tbx (173.2 KB)

WARNING. This code cannot account for:

  • national/public holidays
  • winter/summer time shift using the tested period
  • leap years
  • … any other weird calendar edge cases!

Command line. A quick web search suggests my hunch was wrong about a ‘simple’ command line solution. However, some built-in code languages like Python appear to be able to do this sort of calculation.

I’d be interested to know how this works for you (and any bugs if found, outside the known warnings above).

1 Like
9

Thank you all for all very much for all the effort that you put into my problem. I much appreciate that !
I’ve tested all your suggestions and compared it with a manual count from the calendar:

April 2024
mo tue wed thu fri sat sun mo tue wed thu fri sat sun mo tue wed thu fri sat sun mo tue wed thu fri sat sun
 1   2   3   4   5   6   7  8   9  10  11  12  13  14 15  16  17  18  19  20  21 22  23  24  25  26  27  28

@PaulWalters
Your code is simple. I like that but alas… the results are not what I was hoping for:

$MyNumber=(interval($DatumIn,$DatumUit)/7)*5;

22-4-2024 - 26-04-2024 = 2 (Should be 4, (1st day counts)
18-04-2024 - 26-04-2024 = 5 (Should be 6)
01-04-2024 - 26-04-2024 = 17 (should be 19)

I think the cause of this is that every period of 7 days is recalculated to 5. When there are no or more weekend(s) involved the calculation fails.

@eastgate
Very nice and simple code and formatting the date was indeed unnecessary. unfortunately I didn’t get the code to work.

function fDoorloopTijd2() {

var:date vStart = $DatumIn; //format("D-M0-y");
var:date vEnd = $DatumUit; //.format("D-M0-y");
var:date vLoopDate = vStart; //.format("D-M0-y");
var:num vDagen = days(vStart,vEnd);
var:list vSetDagen=;
var:num vDoorloop=;
var:interval vDay=interval("24:00");

vLoopDate = vLoopDate + vDay; //Dag van ontvangst telt niet mee

	while(vLoopDate<vEnd){
		if(vLoopDate.weekday<6) {vDoorloop+=1;};
		$Doorlooptijd= vDoorloop;
	};
};

I changed the last line {$Doorlooptijd+=1} and hope that I interpreted the rest of your code correctly.
But this code gives constant different outcomes. I will take some time this weekend to figure out what is happening. :slight_smile:

@mwra
You made a beautiful piece of code. I very much like the way you have set it up!
I will need some time to comprehend what exactly is happening. But also this code doesn’t give me the outcome I am looking for .

22-4-2024 - 26-04-2024 = 0 (Should be 4)
18-04-2024 - 26-04-2024 = 5 (Should be 6)
01-04-2024 - 26-04-2024 = 15 (should be 19)

For now I think I will make the calculation in Excel as @PaulWalters suggested and take some time to figure out what the right approach is.
Of course I would love to hear if anyone has a nice solution, but for now thanks again for your efforts.

10

Because I provided a simple example, with caveats, to demonstrate the math. It is pseudo-code, not production code. @mwra expanded on the idea, and gave you production-ready code that addresses your major issues. However, as we both noted, the function does not take into account holidays or other non-working periods that we don’t know about.

11

Ups, my bad. I misinterpreted :see_no_evil:

I noticed that one and I don’t mind. I just do not want to count the Saturday’s and Sunday’s

Thanks for your clarification.

12

Oh dear, that is the case. My mistake!

So, I started over with different logic. See this re-coded file: ElaspedWorkDays2.tbx (193.1 KB)

The approach is braodly this:

  • we work out the next weekday-number after the start date whose weekday number matches that of the end date. We make a new date for the ‘offset’ start. That allows us to deal with complete weeks and get a 5 day/week day count back. If the start/end are already the same weekday number job is complete.
  • otherwise we work of the number of workdays in the ‘remainder’ from the real start date to the offset start date. Add that to the whole-week derived count and we are done.

…see the code in the “Work Days’” library note.

I’ve used more functions here and tried to add meaningful comments. Note the use of a list to general day-scoped intervals for 1 through 7 day intervals, noting @eastgate’s point about as adding an interval to a date being faster than adding a ‘N day’ string.

The above errors are now fixed and I hope there are no new ones.

Save caveats apply re holidays, summer time, etc.

I hope this helps :slight_smile:

13

Thank you for this new set of code. The results are exactly what I had in mind. It taught me a few new things such as using a function within a function.

Don’t get me wrong but isn’t it a lot of code for a (in my mind) relative simple calculation?
Maybe I got it all wrong but I experimented with my old code and some of the ideas I took from the various tips here and came to this:

function fDoorloopTijd() {

var:date vStart = $DatumIn; //theStartdate
var:date vEnd = $DatumUit; //the Enddate
var:date vLoopDate = $DatumIn; //set the start of the loop
var:num vDagen = days(vStart,vEnd); //Count the total number of days between start and enddate
var:list vSetDagen =; //variable to hold the weekdays from start to end
var:num vTeller =; //counter used for the loop
var:interval vDay = interval("24:00:00"); //used to increment the loopdate

// The routine lists the days of the week from start to finish
	while(vTeller<vDagen){
		vSetDagen+=vLoopDate.weekday;
		vTeller+=1;
		vLoopDate+=vDay;
		$Doorlooptijd = vSetDagen.count_if(anItem,anItem<6);  //do not count Weekenddays 
	};
};

I changed the vDay interval to interval(“24:00:00”), I noticed an near endless loop when I used “24:00”. Probably because with “24:00” it counted the minutes (?)

So it seems that both functions produce the right outcome and -most importantly- Tinderbox doesn’t stall by the edict when you open the document, nor does it stall during the periodic updates from the edicts.

Learned a lot :slight_smile:

14

A lot of code? I’ve stripped out some duplication and it now looks like:

function fNumberWorkDays(iStart:date,iEnd:date){
  var:number vStartDay = iStart.weekday;
  var:number vEndDay = iEnd.weekday;
  var:number vStartDaysOffset =;
  var:date vOffsetStartDate =;
  var:number vWorkWeekDays = 0;
  if(vStartDay==vEndDay){
    vWorkWeekDays = fGetWorkDaysOnly(iStart,iEnd);
    return vWorkWeekDays;
  };
  var:number vRemainderWorkDays = 0;
  if(vStartDay<vEndDay){vStartDaysOffset = vEndDay - vStartDay;};
  if(vStartDay>EndDay){vStartDaysOffset = (vEndDay +7) - vStartDay;};
  vOffsetStartDate = iStart + fDaysAsInteval(vStartDaysOffset);
  vWorkWeekDays = fGetWorkDaysOnly(vOffsetStartDate,iEnd);
   vRemainderWorkDays = fGetRemainderWorkDays(vStartDay,vEndDay);
  vWorkWeekDays += vRemainderWorkDays;
  return vWorkWeekDays;
};////END

function fGetWorkDaysOnly(iStart:date, iEnd:date){
  var:number vDays = days(iStart,iEnd);
  vDays = floor(vDays/7)*5;
  return vDays;
} ////END

function fDaysAsInterval(iDays:number){
  var:list vDayList =[1 day;2 days;3 days;4 days;5 days;6 days;7 days];
  var:interval vInt = (vDayList.at(iDays-1));
  return vInt;
} ////END

function fGetRemainderWorkDays(iStartDay:number,iEndDay:number){
  if(iStartDay>5){iStartDay = 1;};
  if(iEndDay>5){iEndDay = 5;};
  var:number vOffset = 0;
  if(iStartDay<iEndDay){vOffset = iEndDay - iStartDay;};
  if(iStartDay>iEndDay){vOffset = (iEndDay + 5) - iStartDay;};
  return vOffset;
} ////END

Indeed, some of these could be further compressed, e.g. to lose a function:

vWorkWeekDays = fGetWorkDaysOnly(vOffsetStartDate,iEnd);

becoming:

vWorkWeekDays = floor(days(vOffsetStartDate,iEnd)/7)*5;

but is it easier to understand? I’m sure I could make the above shorter (and less easily understood) if code length were a stylistic criteria. We can lose another functions by replacing :

vOffsetStartDate = iStart + fDaysAsInteval(vStartDaysOffset);

with

vOffsetStartDate = iStart + interval(vStartDaysOffset+" days");

I’m not an expert coder but fewer lines does not always mean more efficient plus, code in conditional branch is only run if the opening condition is met.

A point to consider is scale. For instance, the code using a while() loop takes longer as the duration of the task gets longer as each day within the task has to be evaluated in turn (more days, more loops run). Then again, generally, if the document is not slow running, then good enough is good enough as once the code runs correctly/to satisfaction there is little value if re-editing it except for learning purposes.

15

True … it’s all from a certain point of view. :smiley:

Good point! To be honest I didn’t think of that. Usually the dates are within a range of 2-14 days so the impact won’t be great. But still…

16

So squeezing out expanded bits of code and comment we get:

function fNumberWorkDays(iStart:date,iEnd:date){
  var:number vStartDay = iStart.weekday;
  var:number vEndDay = iEnd.weekday;
  var:number vStartDaysOffset =;
  var:date vOffsetStartDate =;
  var:number vWorkWeekDays = 0;
  if(vStartDay==vEndDay){ return fGetWorkDaysOnly(iStart,iEnd);};
  var:number vRemainderWorkDays = 0;
  if(vStartDay<vEndDay){vStartDaysOffset = vEndDay - vStartDay;};
  if(vStartDay>EndDay){vStartDaysOffset = (vEndDay +7) - vStartDay;};
  vOffsetStartDate = iStart + interval(vStartDaysOffset+" days");
  vWorkWeekDays = floor(days(vOffsetStartDate,iEnd)/7)*5;
  vRemainderWorkDays = fGetRemainderWorkDays(vStartDay,vEndDay,0);
  return (vWorkWeekDays + vRemainderWorkDays);
};

function fGetRemainderWorkDays(iStartDay:number,iEndDay:number,iOffset:number){
  if(iStartDay>5){iStartDay = 1;};
  if(iEndDay>5){iEndDay = 5;};
  if(iStartDay<iEndDay){iOffset = iEndDay - iStartDay;};
  if(iStartDay>iEndDay){iOffset = (iEndDay + 5) - iStartDay;};
  return iOffset;
};

Of which 2 lines are also essentially conditional branches (i.e. the same code cannot match both queries, even though the code is expressed as 2x if() and not an if()...else

So 22 lines to your 15, about a third more. :slight_smile:

It does occur to me that leap years aren’t a problem as they simply sit in the calendar without distorting the weekday numbering. Summer time is also not a problem as we aren’t checking times. So what about public holidays? … see next post.

1 Like
17

:ok_hand: :+1: :smiley:

Public Holidays… That would be a nice extension!

18

For holidays, there are probably c.6–10 (varies by country) a year and we only care about those falling on weekdays, and only those falling within the earliest/latest date of our project. We could store them like so (I’m making up the dates and assuming they are weekdays):

var:list vHolidays = [2024-04-01;2024-05-01;2024-05-31];

Now in your loop, you insert†:

if(vHoliday.count_if(aDay,aDay==(vLoopDate.format("==")))==1){$Doorlooptijd-=1;};

As you loop you check the yyy-mm-dd string of vLoopDate. If the latter matches a holiday a count of 1 arises, and the if() is matched and we de-increment $Doorlooptijd by 1.

For my solution we’d have to use a list of Date object and then do something like:

var:number vCount=0;
vHolidays.each(aDay){
  if(aDay>=iStart & aDay<=iEnd){vCount-=1;};
};
// then use vCount to decrement out overall workday tally.

This is all a bit baroque, but possible, I think. Note though: I have not tested either of these two holiday test code samples. Also, I don’t think there is an automated way to set up the list of holiday dates.

As all this code is running in a function, you could add an extra boolean argument to the outer functions allowing you to decide whether to do the extra overhead of holiday checking or not by placing the holiday code inside an if() test.

†. Not being tested, I’m not sure if you can use Date.format() inside a query.

19

Terrific! I’m going to continue experimenting with that.

I continued testing Your and My code with dates that crosses a year. It looks like something goes wrong in both.

To be continued…

1 Like
20

FWIW, here is my solution reduced down to just 2 functions, in case anyone wants to use that solution:
ElaspedWorkDays3.tbx (186.2 KB) error in code (reported down-thread) Fixed: ElaspedWorkDays3.tbx (186.4 KB)

N.B. not tested for the crossing-year-boundary just reported above
Testing, the count seems correct across a year end. 21 Dec 2023 - 03 Jan 2024 gives 9 (ignoring actual public holidays). The year end tends to have a good number of public holidays so an option for that might be good!