Custom Async Function called too many times

Custom Async Function called too many times

Posted by: gabriel.inzirillo on 13 November 2020, 3:06 am EST

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 13 November 2020, 3:06 am EST

  Hi,

  I have reproduced an issue in a minimalistic situation to show what we are experiencing in a more large scope.

  We have a custom async function, like in this example (https://www.grapecity.com/spreadjs/demos/features/calculation/async-function/purejs).

  In one cell we can chain the call to this custom function like this :
  
  =ASUM(A1,B1)+ASUM(A1,B1)+ASUM(A1,B1)+ASUM(A1,B1)+ASUM(A1,B1)

  In this scenario, we expect the ASUM function to be called 5 times.

  However, we can see that this function is called 10 times on the initial load and if we change the value in A1, we see more than 20 calls. This become exponential more we add ASUM calls in one cell.

  This is problematic because in real life we can have 15+ calls in one cell and this can cause the custom function to be called 100’000+. Our custom function is more complexe than in the example and fetch data in a database.

  Here in attachment, a simple example on how to reproduce this issue. A debugger statement is here to see how many times we execute the custom async function.

  Minimal_spreadJS.zip.

  Best Regards.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 16 November 2020, 10:44 pm EST

  Hi Gabriel,

  We are able to observe the issue at our end hence we have escalated this issue to our devs for further investigation. We will update you regarding this as soon as we get any information. The internal ID for this issue will be SJS-6518.

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 17 November 2020, 3:53 am EST

  Hi,

  Thank you for the rapidity.

  Looking forward to see what the devs can do.

  Best Regards.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 17 November 2020, 3:02 pm EST

  Hi Gabriel,

  The dev team informed us that the Spread V14 supports the LET function which can be used for chaining the formulas. Please refer to the following code snippet and the attached sample for demonstration.

  sheet.setFormula(
      1,
      1,
      "LET(asum_a1b1, ASUM(A1,B1), asum_a1b1+asum_a1b1+asum_a1b1+asum_a1b1+asum_a1b1)"
    );
  

  sample: https://codesandbox.io/s/boring-gauss-j1j1u?file=/src/index.js:1123-1244

  LET Demo: https://www.grapecity.com/spreadjs/demos/features/calculation/let-function/purejs

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 17 November 2020, 9:22 pm EST

  Hi,

  Thank you for the quick answer.

  Sadly this solution cannot be used in our case.

  That is sometime the problem with too much reduced case.

  In the example I show you, I used only 1 async function with always the same data. But in the real life we use the same async function but with data coming from different cells.

  I have updated your example and also uploaded an example that shows that even using the LET function, we still have too much call (15 calls in the case if we use only 5 times the async function)

  Here is what the formula could look like :

  LET(asum1,ASUM(A1,B1),asum2,ASUM(c1,d1),asum3,ASUM(e1,f1),asum4,ASUM(g1,h1),asum5,ASUM(i1,j1),asum1+asum2+asum3+asum4+asum5)

  I have try to push the calls to 10 times and I reach 62 calls to the async function. In fact I have observed something : The first time, it seems to call the async function correctly (almost), then we have a really tiny pause and after we get a lot more calls.

  For 5 calls I got this : 6 calls and then 10 calls
  
  For 10 calls I got this : 10 calls and then 52 calls

  It doesn’t seems to be regular and maybe those numbers can depend on the machine or the browser we use.
  
  I don’t know if this information could help.

  https://codesandbox.io/s/angry-wiles-l10kq?fontsize=14&hidenavigation=1&theme=dark

  Minimal_spreadJS 2.zip

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 18 November 2020, 5:59 pm EST

  Hi Gabriel,

  Thanks for the explanation. We have shared the information with Devs. We will update once we have any information regarding the same.

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 23 November 2020, 3:59 am EST

  Hi Avinash,

  Thanks for the answer.

  Do you think you can solve this issue?

  How could be the delay before we can get a working solution?

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 23 November 2020, 3:29 pm EST

  Hi Gabriel,

  We are sorry but currently, We don’t have any ETA. The Devs are working on this case. They will surely provide you a working solution soon. Further, We have asked Devs about the updates on this issue. We will update you once we have any information.

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 23 November 2020, 8:37 pm EST

  Hi,

  Thank you for the update. I will wait to get more informations

  Best regards,
  
  Gabriel

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 1 December 2020, 10:08 pm EST

  Any news?

• 

  loukalem

  • Post Options:
  • Link
    Copy

  Posted 2 December 2020, 4:16 am EST

  Dear Avinash, please give us some feedback, one of our customer is struggling with this bug, he has documents that can’t be loaded due to the amount of time spent in the unnecessary calls!

  François Marchal (4D SAS)

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 2 December 2020, 3:11 pm EST

  Hi,

  The issue is fixed in our upcoming 14.0.3 build. We will update you here once the build is officially released.

  Regards
  
  Avinash

• 

  loukalem

  • Post Options:
  • Link
    Copy

  Posted 2 December 2020, 7:56 pm EST

  Dear Avanash, thanks a lot for this information!

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 2 December 2020, 8:20 pm EST

  Great news. Can’t wait to test it!

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 6 December 2020, 5:33 pm EST

  Hi,

  The V14.0.3 build is released now. Please update to the latest build and let us know if the issue still persists for you. You may also downloade the latest build from here: http://cdn.grapecity.com/spreadjs/14.0.3/Files/SpreadJS.Release.14.0.3.zip

  Further please refer to the following sample that demonstrates the issue fix:
  
  https://codesandbox.io/s/busy-platform-4ykch?file=/index.html

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 7 December 2020, 8:05 pm EST

  Hello Avinash

  Thank you for the respond.

  The fix seems to work for the little example.

  But sadly it didn’t pass the tests when we apply it to a bigger scale.

  I have updated the example to show what happens when we try to scale :

  I have now, for 1 cell, 15 chained call to the ASUM function (ASUM(…)+ASUM(…)+ASUM(…)+…+…+…+…)

  It takes a lot of time to finish the 15 calls and sometimes it even stop in the middle and we never get the final result, instead we get “#VALUE!”.

  This becomes more problematic when we use that a lot in our sheets.

  In my example I use 6 cells where each cell call 15 times the ASUM function.

  It should log 90 calls to the ASUM function, but it can stop at 29, or sometime around 50 or 60. It is not always the same result. The 90 calls should have be done quickly but we can see that the calculation time is greatly reduced over time.

  This is problematic for our case where we have hundreds of such code in our sheets.

  Here is the test : https://codesandbox.io/s/xenodochial-pond-4jydg?file=/src/index.js

  I have tried to play around with the “sync” function which is blazing fast. I have tried to use an async (JavaScript async/await) call to be able to wrap my async call inside the sync registration of a function with spreadJS. However spread JS doesn’t accept (or doesn’t like) when we return a await (a promise) within a sync function. It always displays “Promise” in the sheet.

  Would it be possible to make the synchronous registration able to accept an await as a return value so we can be able to use asynchronous calls within a synchronous registered function?

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 8 December 2020, 9:06 pm EST

  Hi Gabriel,

  Thanks for the information. We are able to replicate the issue at our end we have forwarded this information to our Devs. We will update once they provide any information.

  Regards
  
  Avinash

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 17 December 2020, 8:27 pm EST

  Hi Gabriel,

  The issue is refixed in our latest V14.0.4 build. Please update to the latest build and let us know if the issue still persists for you. You may also downloade the latest build from here: http://cdn.grapecity.com/spreadjs/14.0.4/Files/SpreadJS.Release.14.0.4.zip

  Further please refer to the following sample that demonstrates the issue fix:
  
  https://codesandbox.io/s/eager-pike-drgdh?file=/src/index.js

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 20 December 2020, 11:09 pm EST

  Hello Avinsh,

  Thanks a lot for the reaction and for providing a fix.

  I have tried on my side and this version make our big and complexe sheet now to work great! So for me this is a good and working solution.

  Something that I still cannot explain, but which doesn’t bring performance problems, is that there is still a slightly difference between the number of occurence of a custom async function and the total count. When I have 5 * 10 calls (5 cells with 10 call to ASUM) I got a total of 54 console logs.

  In my document where I have ~41’000 calls to a custom async function, I get ~47’000 call to my custom function.

  But that is still much better than before where I could reach 8+ millions calls after more than 30 minutes.

  I can now load my sheet in less than 20 secondes.

  So Thanks a lot for the support and thank to the devs!

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 21 December 2020, 7:41 pm EST

  Hi Gabriel,

  We are glad to know that the issue has been fixed for you. Further, we have asked the devs about the reason for this behaviour. We will update you once they provide any information.

  Regards
  
  Avinash

• 

  gabriel.inzirillo

  • Post Options:
  • Link
    Copy

  Posted 21 December 2020, 10:18 pm EST

  Hi,

  Thanks you I will still look here if we get an explanation but it is not a blocker, it is a small detail.

  I will mark the thread as solved !

  Best regards
  
  Gabriel

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 22 December 2020, 4:11 pm EST

  Hi Gabriel,

  The Devs informed us that issue was the expected behavior from the SJS. Actually, Without suspend calc service, every set formula will call the recalcAll and it will erase the mark in the previous cell. This means will call the async function more than expected. For avoiding this you may use the suspendCalcService and resumeClacService method. Please refer to the following code snippet and let us know you face any issues.

  
    spread.suspendCalcService();
    sheet.setFormula(1, 1, "ASUM(1,B1)+ASUM(2,B1)");
    sheet.setFormula(2, 1, "ASUM(3,B1)+ASUM(4,B1)");
    sheet.setFormula(3, 1, "ASUM(5,B1)+ASUM(6,B1)");
    sheet.setFormula(4, 1, "ASUM(7,B1)+ASUM(8,B1)");
    spread.resumeCalcService(); // It will evaluate ASUM 8 times.
  
  

  Regards
  
  Avinash