Performance Issue when upgrading from v4.2.6 to v5.0.5

Performance Issue when upgrading from v4.2.6 to v5.0.5

Posted by: maya.halter on 11 April 2022, 8:12 pm EST

• 

  maya.halter

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 11 April 2022, 8:12 pm EST - Updated 29 September 2022, 6:30 am EST

  Hi all,

  we are experiencing massive performance issues when we upgrading grapeCity from v4.2.6 to v5.0.5.

  The performance of this CalculateValue method changed from ~ 1s (which was also not really good) to now ~37s (which is of course not acceptable).

  
  private string? CalculateValue(IRange cell)
  {
              worksheet.Workbook.EnableCalculation = true;
              if (cell.Value is { } value)
              {
                  return value.ToString();
              }
  
              return null;
  }
  
  

  Could you please help us to solve this issue? Apart from the version upgrade, we haven’t changed anything.

  Thanks in advance!
  
  Maya

  

  

  712×209 7.08 KB

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 12 April 2022, 2:53 pm EST

  Hello Maya,

  Apologies for the inconvenience caused.
  
  We do not face the issue at our end with your given code, please find the attached sample implementing the same. If you are doing anything else then please let us know and modify the sample accordingly so that we can assist you further with this.

  Regards,
  
  Prabhat Sharma.
  
  GcExcel_Performance_Demo.zip

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 12 April 2022, 7:08 pm EST

  Hi Prabhat Sharma,

  we also do not face the issue with your code sample. The problem is that we have a really huge and complex workbook with thousands of formulas, many sheets and cross relations. Unfortunately I am not allowed to send you this excel sheet.

  What we do different is, we set wb.EnableCalculation to false before we insert around 100 values to the excel (if we wouldn’t the setting of the values is really slow). Once we inserted all values we set EnableCalculation to true and this call needs the 37 seconds.

  
  public void SetValues(IDictionary<string, object?> inputs)
  {
  	worksheet.Workbook.EnableCalculation = false;
  	foreach (var (identifier, value) in inputs)
  	{
  		worksheet.Range[identifier].Value = value;
  	}
  }
  
  

  Maybe your developers have an idea what could cause this problem? I don’t know what kind of information is important for you to describe this problem in more detail. But I’m afraid I can’t create a sample demo project.

  This problem is really a blocker on our side.

  Thanks in advance
  
  Maya

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 13 April 2022, 2:43 pm EST

  Hello Maya,

  We still do not face the issue with your given code too so we are discussing this with the developers to get their insights on this.
  
  We will let you know the updates soon.
  
  [Internal Tracking ID: DOCXLS-5822]

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 13 April 2022, 5:29 pm EST

  Some findings that might help you:

  v4.2.6 → v.5.0.0 performance degradation 15% slower
  
  v4.2.6 → v.5.0.1 performance degradation 5 times slower
  
  v4.2.6 → v.5.0.5 performance degradation > 10-20 times slower + wrong calculations in excel sheet

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 18 April 2022, 3:42 pm EST

  Hello Maya,

  As per the developers, the issue can be because of some specific formula in your sheet.
  
  Due to the limited information, we could not give more valuable conjecture.

  It’s better we have the excel file that could reproduce the issue since we did not make major change about calc engine from v4.2 to v5.0

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 19 April 2022, 8:54 pm EST - Updated 29 September 2022, 6:31 am EST

  Hi Prabhat Sharma,

  As i already said, I am not allowed to send you the file.
  
  But we investigated a bit further and tested how the performance improves if we disable the different formulas.
  
  If we run this Clear method for our sheet, the calculation time is reduced from ~20s to ~4s with no difference whether we call cell.Clear() or cell.ClearFormats().

  So our guess is that we have style formats that cause (at least some of) the performance issues.

  
  public void Clear()
          {
              workbook.EnableCalculation = false;
              var worksheetCount = workbook.Worksheets.Count;
  
              for (var sheetIndex = 0; sheetIndex < worksheetCount; sheetIndex++)
              {
                  if (workbook.Worksheets[sheetIndex].UsedRange is not {} usedRange) 
                      continue;
                  
                  var usedRangeCount = usedRange.Cells.Count;
                  for (var cellIndex = 0; cellIndex < usedRangeCount; cellIndex++)
                  {
                      var cell = usedRange.Cells[cellIndex];
                      var oldValue = cell.Value;
                      cell.Clear();
                      // cell.ClearFormats();
                      cell.Value = oldValue;
                  }            
              }
          }
  
  

  Example of our style formats:

  

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 19 April 2022, 9:53 pm EST

  Hello Maya,

  We have forwarded your comments to the development team and will let you know the updates soon.

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 19 April 2022, 11:13 pm EST - Updated 29 September 2022, 6:30 am EST

  Another finding that might be interesting for you and your developers:
  
  After we run the ClearFormats() method for each of our cells, the set_EnableCalculation method takes ~3,7s.
  
  What makes us sceptical is that the method “PrintManager GetSize” needs 3,2s although we do not want to print anything but only calculate the formula of the cell.

  

  883×386 34.1 KB

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 20 April 2022, 9:46 pm EST

  Hello Maya,

  Thank you for your findings.
  
  We have forwarded these comments to the development team and will let you know the updates soon.

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 3 May 2022, 5:26 pm EST

  Hi Prabhat Sharma, do you have any news for us?

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 4 May 2022, 1:41 pm EST

  Hello Maya,

  As per the developers, the PrintManager GetSize method would get the real font file and measure the content, so it would take some time. If you do not want to print anything, do not call it.

  They have few question based on your code:

  1: Why are you calling the below given code in clear();

  workbook.EnableCalculation = false;
  

  2: Why you iterate the usedrange cell? Why not just usedrange.clear()?

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 8 May 2022, 8:10 pm EST - Updated 29 September 2022, 6:30 am EST

  We never explicitly call the PrintManager GetSize method, it appears in the stack trace when we set workbook.EnableCalculation = true - so we were wondering why it is showing up here.

  

  883×386 34.1 KB

  Answers to 1. and 2. : this Clear method was only for debugging the problem. It only helped us to find out that ~80 percent of the performance degradation are related to the format of the cells. I’m sorry to say, but it seems like you have forgotten the topic of this forum post? It’s about huge performance issues when upgrading from grapecity 4.2.6 to 5.0.5 when calling this Calculate method:

  
  
  private string? CalculateValue(IRange cell)
  {
              worksheet.Workbook.EnableCalculation = true;
              if (cell.Value is { } value)
              {
                  return value.ToString();
              }
              return null;
  }
  
  

  We already figured out that this problem is related to the style formats that are used in our excel sheet.

  

  Do you need any more detailed information about these formatting rules? Or can we arrange a meeting with one of your developers?

  Regards,
  
  Maya

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 9 May 2022, 8:18 pm EST

  Hello Maya,

  Thank you for providing the information. We have forwarded this to the developers and also asked them if there is a need to arrange a remote session to speed up the issue.

  We will let you know as soon as we get the update from the devs’ end.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 10 May 2022, 8:24 pm EST

  Hello Maya,

  As per the devs, one of the possible reasons for performance degradation is Linked Picture.

  It would trigger the linked picture to update its content if the CE is disable then enable. That’s why the PrintManager.GetSize is called.

  Could you please let us know if your file contains Linked picture? If so, then delete the linked picture and see if the performance issue still exists.

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 15 May 2022, 5:51 pm EST

  Hi Prabhat Sharma,

  thanks for your response, we actually have a lot of linked pictures in our excel sheet. Could you tell us how we can delete them programmatically?

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 15 May 2022, 6:12 pm EST

  We tried this code, but it makes no difference to the performance.

  
  public void RemoveAllLinkedPictures()
          {
             foreach (var worksheet in workbook.Worksheets)
              {
                  var shapes = worksheet.Shapes;
                  foreach (var shape in shapes)
                  {
                      shape.Delete();
                  }
              }
          }
  
  

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 17 May 2022, 8:26 pm EST

  Hello Maya,

  We have forwarded your comments further to the development team for investigation and will get back to you with the updates soon.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 18 May 2022, 8:36 pm EST

  Hello,

  Please find the code below to delete the linked images and let us know if it improves the performance:

  foreach (var worksheet in workbook.Worksheets)
  {
      foreach (var shape in worksheet.Shapes)
      {
          shape.PictureFormat.Reference = null;
          shape.Delete();
      }
  }
  

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 18 May 2022, 11:16 pm EST

  Hi Prabhat Sharma,

  Thanks for your response. With your code snippet we can reduce the calculation time from 8 seconds to 4 seconds.
  
  So now with ClearFormats() of all UsedRanges and with deleting all linked pictures in our sheet we were able to reduce the calculation time from 37s to 4s in version 5.0.5.

  But we are still far away from our original calculation time of less than one second.

  Is there anything else that can reduce the calculation time?

  Regards,
  
  Maya Halter

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 22 May 2022, 8:47 pm EST

  Hello Maya,

  It is good to hear that the performance has improved from before.
  
  As per the developers’ latest comments, they need the performance stack trace again when deleting all the shapes since we do not have the excel file. And also we need the performance stack trace of the 4.2.6 as well so that we could find something else than the linked picture that caused the degradation.

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 29 May 2022, 5:32 pm EST - Updated 29 September 2022, 6:31 am EST

  Hi Prabhat Sharma,

  here the performance stack trace of both versions:

  v5.0.5 :
  
  

  

  1085×828 74.6 KB

  v4.2.6:
  
  

  

  1071×1126 98.3 KB

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 1 June 2022, 1:32 pm EST

  Hello Maya,

  Thank you for the screenshots.
  
  We have forwarded these comments to the development team and will let you know the updates soon.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 12 June 2022, 10:27 pm EST

  Hello Maya,

  As per the developers, they have made some improvements about the linked pictures in last hotfix, could you please check if 5.1.2 can meet your requirement?

  https://www.nuget.org/packages/GrapeCity.Documents.Excel/

  Regards,
  
  Prabhat Sharma.

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 3 July 2022, 8:53 pm EST

  Hi,

  So the good thing is that with v5.1.2. we do not have to remove all the linked pictures and formats manually to achieve a calculation time of less than three seconds. On the downside, the calculation still takes 2900ms instead of 600ms in v4.2.6.

  Could you please make a suggestion how we can solve this problem in a timely manner? The ticket has been open for three months now and we still do not have an acceptable solution. If necessary, please arrange a meeting for us with one of your developers.

  Regards,
  
  Maya

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 5 July 2022, 7:21 pm EST

  Hello Maya,

  Apologies for the inconvenience caused.
  
  We have forwarded these comments to the development team and will let you know the updates soon.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 6 July 2022, 9:51 pm EST - Updated 29 September 2022, 6:31 am EST

  Hello Maya,

  As per the developers, if you can provide a file reproducing the issue then it will be very helpful. You can also create a support ticket on our private portal as it is completely confidential.
  
  https://www.grapecity.com/my-account/my-support

  The developers noticed the call stack is not the same between 4.2.6/5.0.5. It will be better if you use the exact same code to test the performance.

  5.0.5:
  
  

  

  950×175 70.4 KB

  4.2.6:
  
  

  

  1069×192 81.7 KB

  Regards,
  
  Prabhat Sharma.