Time elapsed calculation causing slowness

[sal88]

Hi all

 

I've created a calculation which is on each case record. It displays the time passed, but within the hours of 9-5, monday to friday. e.g. at monday 10am, the time passed will be 2 hours, if that start time was friday 4pm.

The cases are accessed via portals, via a relationship from a single record table.

 

One layout has a portal that shows just 10 records, and another has 8 portals of 15 records each. 

 

We now have a problem where the database simply freezes when trying to access these layouts. It is hosted on Filemaker Server 11.

 

I couldn't find a custom function which would work out hours passed within particular time frames, so had to make my own, utilising some similar custom functions. I suspect it's not very efficient.

 

Perhaps someone knows of a good custom function? Or any advice on the calculation would be very much appreciated.

Many thanks

 

Here is the calc:

 

If (Settings::Disable_Time_Passed = 1; ""; 

Case (Parked = "Parked"; "";Status = "Closed"; ""; Status ≠ "Closed" and Parked ≠ "Parked";

 

Let ([startDate =  Originals::Date;StartTime =  Originals::Time; WDays = WorkingDays ( StartDate; Get (CurrentDate); "")];

Case (WDays = 0; 0;

wdays = 1 ; Case (DayName (Get (CurrentDate)) = "Saturday" or DayName (Get (CurrentDate)) = "Sunday" ; 

 

Case (StartTime  ≤  "09:00:00"; 8;

StartTime  ≥  "17:00:00" ; 0;

StartTime < "17:00:00" and StartTime > "09:00:00" ; (Time(17;0;0) - StartTime)/3600)

;

DayName (StartDate) = "Sunday" or DayName (StartDate) = "Saturday" ; 

 

Case (Get (CurrentTime)  ≤  "09:00:00"; 0;

Get (CurrentTime)  ≥  "17:00:00" ; 8;

Get (CurrentTime) < "17:00:00" and Get (CurrentTime) > "09:00:00" ; (Get (CurrentTime) - Time (9;0;0))/3600)

;

DayName (StartDate) ≠ "Sunday" and DayName (StartDate) ≠ "Saturday" ; 

If (

StartTime ≤ "09:00:00";(Get (CurrentTime) - Time (9;0;0))/3600

)

&

If (StartTime < "17:00:00" and StartTime > "09:00:00"; (Get (CurrentTime) - StartTime)/3600))

;

wdays > 1 ; wdays * 8-

If (DayName (StartDate) ≠ "Saturday" and DayName (StartDate) ≠ "Sunday";

Case (StartTime  ≤  "09:00:00"; 0;

StartTime  ≥  "17:00:00" ; 8;

StartTime < "17:00:00" and StartTime > "09:00:00" ; (StartTime - Time (9;0;0))/3600)

)

-

If (DayName (Get (CurrentDate)) ≠ "Saturday" and DayName (Get(CurrentDate)) ≠ "Sunday";

Case (Get (CurrentTime)  ≤  "09:00:00"; 8;

Get (CurrentTime)  ≥  "17:00:00" ; 0;

Get (CurrentTime) < "17:00:00" and Get (CurrentTime) > "09:00:00" ; (Get (CurrentTime) - Time (9;0;0))/3600))

 

)

)))

 

 

[eos]

 

 

I suspect it's not very efficient.

 

You can say that again … looks like The Calculation from Hell! 

 

Try this, without any guarantees that it'll work; but you see the basic method of declaring not only oft-used values, but Boolean expressions, too, which makes for a much more readable and performant calculation (a win-win scenario for Human and Computer …).

 

Note that I substituted timeResult / 3600 with Hour ( timeResult ); if you're interested in the decimal part of the result, switch back to the division.

Case (

 not ( Settings::Disable_Time_Passed or Parked = "Parked" or Status = "Closed" ) ;

 Let ( [

  cd = Get ( CurrentDate ) ;
  ct = Get ( CurrentTime ) ;

  startDate =  Originals::Date;
  startTime =  Originals::Time; 

  wDays = WorkingDays ( StartDate; cd ; "" )

  dayNameStart = DayName ( StartDate ) ;
  startIsWeekend = DayOfWeek ( StartDate ) = 1 or DayOfWeek ( StartDate ) = 7 ;
  todayIsWeekend = DayOfWeek ( cd ) = 1 or  DayOfWeek ( cd ) = 7 ;
  ~9h = Time ( 9 ; 0 ; 0 ) ;
  ~17h = Time ( 17 ; 0 ; 0 )

  startUntil9 = startTime  ≤ ~9h ;
  startFrom17 = startTime  ≥  ~17h ;
  startB9And17 = StartTime < ~17h and StartTime > ~9h ;

  nowUntil9 = ct  ≤ ~9h ;
  nowFrom17 = ct  ≥  ~17h ;
  nowB9And17 = ct < ~17h and ct > ~9h

  ] ;

  Case (

   wDays = 1 ;

      Case ( 

         todayIsWeekend ; 

            Case (
              startUntil9 ; 8 ;
              startFrom17 ; 0 ;
              startBetween9And17 ; Hour ( ~17h - StartTime ) // or ( ~17h - StartTime ) / 3600
             ) ;

        startIsWeekend ; 

           Case ( 
            nowUntil9 ; 0 ;
            nowFrom17 ; 8 ;
            nowB9And17 ; Hour ( ct - ~9h )
           ) ;

       not startIsWeekend ;

          Case ( startUntil9 ; Hour ( ct - ~9h ) ) & Case ( startB9And17 ; Hour ( ct - StartTime ) )
   
     ;
 
     wDays > 1 ; 

       wDays * 8 

       -

       Case ( not startIsWeekend;
         Case (
           startUntil9 ; 0;
           startFrom17 ; 8 ;
           startB9And17 ; Hour ( StartTime - ~9h )
            ) )
      -

       Case ( not todayIsWeekend ;
         Case (
           nowUntil9 ; 8 ;
           nowFrom17 ; 0 ;
           nowB9And17 ; Hour ( ct - ~9h )
           ) )
      ; 0 // wDays = 0

     ) // end wDays Case
  ) // end Let
) // end outer Case

[sal88]

I knew one day you'd come 

 

Thank you so much for that, it appears to have done the trick and I've learned a lot about neater programming. I did opt for timeResult / 3600 in the end.

 

To count the working days I am also using this custom function http://www.briandunning.com/cf/1189 instead of this one http://www.briandunning.com/cf/1182. Can you see if one might be better than the other?

 

Do you think having Current (date) and Current (time) might cause performance issues? Perhaps I should replace it with a field(s) that are updated via a triggered/scheduled script?

Thanks again

[eos]

Can you see if one might be better than the other?

 

Not really, only that one lets you specify the kind of days to include, and the other can use an exception list. You could combine both, if required …

 

But the guy who could answer that question more profoundly seems to be (most unusually) on temporary leave …

 

Do you think having Current (date) and Current (time) might cause performance issues? Perhaps I should replace it with a field(s) that are updated via a triggered/scheduled script?

 

No.

 

I knew one day you'd come 

 

You should tell that your spouse/GF/BF … 

[sal88]

LOL! Cheers, BTW when it comes to undoing the substitution of timeResult / 3600 with Hour ( timeResult ), is that just where you've put the comment, or for all instances of Hour? // or ( ~17h - StartTime ) / 3600 ?

Thanks

[eos]

For all instances.

[comment]

Do you think having Current (date) and Current (time) might cause performance issues? Perhaps I should replace it with a field(s) that are updated via a triggered/scheduled script?

 

It depends on how many of these (and other unstored) fields do you plan on displaying at any one time. I must say that I would be surprised if your formula - no matter how convoluted - turned out to be the cause of the problem you described.

 

That said, if some of those custom functions are recursive, and they - or your implementation thereof - run away with no reasonable exit clause other than Filemaker's built-in protection, the expected result would be very much like what you experienced.

[sal88]

It depends on how many of these (and other unstored) fields do you plan on displaying at any one time. I must say that I would be surprised if your formula - no matter how convoluted - turned out to be the cause of the problem you described.

 

That said, if some of those custom functions are recursive, and they - or your implementation thereof - run away with no reasonable exit clause other than Filemaker's built-in protection, the expected result would be very much like what you experienced.

 

On one of the layouts it could be up to 50 instances of that field being displayed, on the other layout, 10, but was still slow on the latter (not anymore with the tidier formula though)

 

Where can I learn about recursive functions (it's a new term for me) and how things can run away with no reasonable exit clause?

Thanks

[comment]

Where can I learn about recursive functions (it's a new term for me)

 

Mostly here... In a nutshell, a recursive custom function is one that calls itself in a loop.

 

 

how things can run away with no reasonable exit clause?

 

Easily - all you need is a sloppy programmer that did not provide a reasonable exit condition for the recursive loop. In such case, Filemaker will run the function 10,000 (in some cases, 50,000) times. If you have only 10 instances on the layout, that's either 100k or 500k loops to perform.

 

Even with decent programming, a recursive custom function may take a while, if the number of required loops is large, e.g. iterating over a period of several years day-by-day.

[sal88]

Would you say this CF is recursive? As far as I can see it does not refer to itself:

 

Case(

DateStart > DateEnd ; "" ;

Let ( 

$counter = $counter + ( Mod ( DateStart - 1  ; 7 ) < 5 and IsEmpty ( FilterValues ( DateStart ; HolidayList ) ) ) ;

Case(

DateStart = DateEnd ; $counter & Let( $counter = "" ; "" ) ;

workingdays ( DateStart + 1 ; DateEnd ; HolidayList )

)))

[eos]

Where can I learn about recursive functions (it's a new term for me) 

 

Mostly here...

 

And of course there: http://en.wikipedia.org/wiki/Recursion

[eos]

Would you say this CF is recursive? As far as I can see it does not refer to itself:

 

You didn't give the CF's name, but this seems to be the recursive call:

 

workingdays ( DateStart + 1 ; DateEnd ; HolidayList )

 

in which the incremented argument lets the CF work its way towards the exit condition (dateStart = dateEnd).

[sal88]

Yes that's the name. I see now. It looks like I might need an exit clause then. What would that look like?

[eos]

It looks like I might need an exit clause then. What would that look like?

 

There is already one, otherwise the CF wouldn't work (properly):

 

in which the incremented argument lets the CF work its way towards the exit condition (dateStart = dateEnd).

 

If you get into the habit of formatting your (or any) calculations, you can see it more easily:

Case (
  DateStart > DateEnd ; "" ;
  Let ( 
   $counter = $counter + ( Mod ( DateStart - 1  ; 7 ) < 5 and IsEmpty ( FilterValues ( DateStart ; HolidayList ) ) ) ;
   Case (
    DateStart = DateEnd ; // exit condition; now ...
    $counter & Let ( $counter = "" ; "" ) ; // ... use current value as final result and reset/delete variable 
    workingdays ( DateStart + 1 ; DateEnd ; HolidayList ) // otherwise call recursively with incremented argument
    )
  )
)

[sal88]

Ah i see! Understood.

 

Thanks for your assistance guys

[sal88]

Here's my slightly revised code, hope it's of some use to someone

Case (
 not ( Settings::Disable_Time_Passed or Parked = "Parked" or Status = "Closed" ) ;
 Let ( [
  cd = Get ( CurrentDate ) ;
  ct = Get ( CurrentTime ) ;
  startDate =  Originals::Date;
  startTime =  Originals::Time; 
  wDays = WorkingDays ( StartDate; cd ; "" );
  dayNameStart = DayName ( StartDate ) ;
  startIsWeekend = DayOfWeek ( StartDate ) = 1 or DayOfWeek ( StartDate ) = 7 ;
  todayIsWeekend = DayOfWeek ( cd ) = 1 or  DayOfWeek ( cd ) = 7 ;
  ~9h = Time ( 9 ; 0 ; 0 ) ;
  ~17h = Time ( 17 ; 0 ; 0 );
  startUntil9 = startTime  ≤ ~9h ;
  startFrom17 = startTime  ≥  ~17h ;
  startB9And17 = StartTime < ~17h and StartTime > ~9h ;
  nowUntil9 = ct  ≤ ~9h ;
  nowFrom17 = ct  ≥  ~17h ;
  nowB9And17 = ct < ~17h and ct > ~9h
  ] ;
  Case (
   wDays = 1 ;  //if wdays is 1
      Case ( 
         todayIsWeekend; 
            Case (
              startUntil9 ; 8 ;
              startFrom17 ; 0 ;
              startB9And17 ; ( ~17h - StartTime )/3600 
             ) ;
        startIsWeekend; 
           Case ( 
            nowUntil9 ; 0 ;
            nowFrom17 ; 8 ;
            nowB9And17 ; ( ct - ~9h )/3600
           ) ;
       not startIsWeekend ;
Case ( startUntil9 and ct>~9h ; ( ct - ~9h )/3600 ) & Case ( startB9And17 ; ( ct - StartTime )/3600 ) & Case ( startUntil9 and ct ≤ ~9h ; 0 )
   )
   ;	     
	  wDays > 1 ; //if wdays is more than 1
       wDays * 8 
       -
       Case ( not startIsWeekend;
         Case (
           startUntil9 ; 0;
           startFrom17 ; 8 ;
           startB9And17 ; ( StartTime - ~9h )/3600
            ) )
      -
       Case ( not todayIsWeekend ;
         Case (
           nowUntil9 ; 8 ;
           nowFrom17 ; 0 ;
           nowB9And17 ; (~17h - ct)/3600
           ) )
      ; wDays = 0 or wDays = "";0 // wDays = 0 or nothing
     ) // end wDays Case
  ) // end Let
) // end outer Case

[sal88]

Only took me 2 years to notice this error 

In the case of Wdays = 1 and not startIsWeekend it was indifferent to whether the current time is after 5pm.

Therefore instead of 

Case ( startUntil9 and ct>~9h ; ( ct - ~9h )/3600 ) & Case ( startB9And17 ; ( ct - StartTime )/3600 ) 
& Case ( startUntil9 and ct ≤ ~9h ; 0 )

It should be

Case ( startUntil9 and ct>~9h ; ( if (nowFrom17;~17h;ct) - ~9h )/3600 ) & 
Case ( startB9And17 ; ( if (nowFrom17;~17h;ct) - StartTime )/3600 ) & 
Case ( startUntil9 and ct ≤ ~9h ; 0 ) & 
Case ( startFrom17;0)