Slow performance when visible area of spread is large

Posted by: makis1970 on 8 September 2017, 2:05 pm EST

  • Posted 8 September 2017, 2:05 pm EST

    When the visible area of a spread I load from an excel file is large, the performance gets really bad. Clicking though the cells takes a couple of seconds and scrolling is slow also. But if I shrink the application window so that the visible area of the spread gets quite small, then it performs fine. If I maximise again, the performance gets worst again.
     
    I'm using FarPoint Spead for WinForms 4.0.2012 (.NET 2.0). Here is a zip file with a simple app to load and view the spread and a example excel file that I would like you to test with. I have done tests with a single sheet of the attached excel and also with half the sheet. No improvement.
     
    Example solution and excel: http://dl.getdropbox.com/u/365164/example.zip 
     
    Any advice would be really welcome. Thanks a lot.
  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    I tried your sample project and the difference i noticed in terms of performance is not as bad. There is a delay(depending upon the visible spread area,but not alarming delay), and this could be attributed to the facts that more number of cells are getting repainted.There is not much he can do if there are a lot of cells that are being painted
    in a view. I also tried loading your excel file using OpenExcel method and did not notice any sluggish performance.

    As a suggestion, you may try referencing the TextRenderer dll (FarPoint.Win.TextRenderer) in your project, which allows to use GDI(instead of GDI+) for drawing text in Visual Studio 2005. Thanks

  • Replied 8 September 2017, 2:05 pm EST

    Thanks for the reply. I tried your suggestion about TextRenderer, but I don't see any performance improvement. I tested in a release build, and run it outside of visual studio. I noticed also that the performance issue exists only when scrolling and selecting cells with the mouse. When navigating the cells with the keyboard, there is no performance issue.

    The example excel file is made up of around 80 columns and 160 rows, and has quite a few merged cells, in order to create a form layout. Also some basic styling like borders and background cell color. The only strange thing that this spread has is that the columns are quite dense, 0.25cm in width, in order to create a grid where a form layout is created. I don't consider the example to be a large spread though. Using the example excel file with ms office or openoffice, has no issue with scrolling or mouse selection.

    Also, when I say that performance is good when the visible size of the spread is small, the size I'm referring to is just something like 6x6cells. Anything larger seems slow. You can imagine that working with this size constrain is not acceptable.

    Finally, if you still can't notice the sluggish performance, I could record a small screencast demonstrating the problem.

    Waiting for suggestions. Thanks in advance.

    Update:

    I have tried the same app and excel file with ver. 2.5 of FP Spread and the performance issue does not exist. But I need to use the newer version because of memory leaks with the 2.5 version.

  • Replied 8 September 2017, 2:05 pm EST

    Non of the above helped. Is there anything else that I could try?

    It's strange that you don't see the same performance issue with the excel file and sample app that I have send above. I have tried it in 3 different pcs at work, just to make sure that its not something wrong with my configuration, and on all of them demonstrate the same performance problem.

  • Replied 8 September 2017, 2:05 pm EST

    Hi,

    Here are some more suggestions to improve performance:

      Set FpSpread.Skin to DefaultSpreadSkins.Classic to remove the InterfaceRenderer and make the control paint using the old style, like v3.

      Turn off VisualStyles for the FpSpread to eliminate the extra time required to paint using the UXTheme API for the Windows XP themed appearance.

      Set SelectionStyle for the SheetView to use SelectionStyles.SelectionColors instead of painting the selection with alpha blending.


  • Replied 8 September 2017, 2:05 pm EST

    As I have stated in my first message, the problem occurs with Spread4 only (this is the version I'm trying to use currently). I tested with Spread3, just to see if there are any differences, and I don't have the same performace issue. So what do you advice to do to improve the performace with Spread4? Spread4 is the version I would like to use.

    Thanks 

  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    You may try upgrading to Spread3 or Spread4 and observe the reported issue. Since no maintenance release for Spread2.5 is scheduled,and  we are only releasing maintenance release of Spread3 & Spread4. The available version of Spread 2.5 is 2.5.2015.2005 (in case you are not using older version that this), you can donwload it from following location:

    http://www.clubfarpoint.com/FarPointSupportSite/Modules/Download/updates.aspx

    Thanks

  • Replied 8 September 2017, 2:05 pm EST

    Yes, the formatting of the document is the problem. When using ExcelOpenFlags.DataAndFormulasOnly the spread behaves really good. For my application though, I need to use this kind of formatting. Is there anything that can be done to improve the performance with the kind of formatting used in my example files?

    Thanks

  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    I tried with many other Excel file of size more than 500 kb and it all worked fine.There seems to be some issue with the Excel file that is being used by you as when I tried to load the Excel file you mentioned I see that the Spread works extremely slow.

    I tried to open your Excel file and I see that it is taking almost the same time to open and scrolling there in Excel.

    I have attached a Jpeg image of the form that is there in the first ExcelSheet in present in the Excel file which seems to be causing the slow speed.

     

    Thanks,

     

     

     


    2009/10/ExampleImage.JPG
  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    I tested the behavior with the Excel file posted by you in the last post and the I found out that the Excel file is opening in about half a second with Spread and working properly.I have attached an image which shows the loading of ExcelFile in Spread and total time taken is less than 1 second. I used the following code to achieve this on button Click.

            Label1.Text = Now.Second
            FpSpread1.OpenExcel("D:\example-no-vba-single-sheet\example-no-vba-single-sheet.xls")
            Label2.Text = Now.Second

    I am using Spread v4.0.2012.2005 please test the issue with the same and let me know if are still facing the isssue.

     

    Thanks,

     

     

     

     


    2009/10/example-no-vba-single-sheet.rar
  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    To see the difference you may Import the Excel file using "FpSpread1.OpenExcel(Str, FarPoint.Excel.ExcelOpenFlags.DataAndFormulasOnly".

    It will tell you if the Formatting of cells is a problem.

    Thanks,

  • Replied 8 September 2017, 2:05 pm EST

    Please test with the attached file. I have removed all vba code and macros that I found. I also left only one sheet. Opening the attached file with farpoint still performs bad. Opening the attached file with MS Excel is extremely fast (was fast for me even with the vba code included). Opening the attached file is also very fast even with openoffice and the spreadsheet gear control.

    Thanks


    2009/10/example-no-vba-single-sheet.zip
  • Replied 8 September 2017, 2:05 pm EST

    Yes, the problem exists only with the kind of layout used in the example spread. The example spread uses a lot of small columns to create a grid and then lots of cell merges to create a form like document. The problem is that this is the kind of document what I need to use. I have lots of spreads similar to the one in the example, that I need to use in my application.

    So, any more suggestions to get around of this regression in the latest farpoint spread?

     Thanks 

  • Replied 8 September 2017, 2:05 pm EST

    Sorry for the wrong wording. In my last message I was referring to usage speed (try selecting cell after cell with the mouse and notice the time it takes in between, also try scrolling with mouse). The excel file loading speed is not an issue. See also my previous messages.

     Thanks 

  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    I am able to see the performance of the Spread(ver. 4.0.2012.2005) while selecting cells and scrolling.However I tested the same thing with so many other Excel file and found that it works fine.

    It more looks like Excel File specific you may also test the same thing with different Excel file and I would appreciate if you share your observations with us.

     

    Thanks,

     

     

  • Replied 8 September 2017, 2:05 pm EST

    Is there any updates regarding this post,

    I have similar problem slow performance when scrolling. I'm using Version 5.0.3514.2008 for windows forms.

    I have my form built in Spread Designer 200 Rows 40 Columns

    I appreciate any response/help on this.

    Thanks

    John

  • Replied 8 September 2017, 2:05 pm EST

    Is there any update on this problem? Is there a issue report that I can follow, outside the forum? My project is currently stalled, waiting on this problem being fixed.

    Thanks

  • Replied 8 September 2017, 2:05 pm EST

    Hi,

    I have forwarded the issue to the development team to investigate.  I think the performance of the scrolling is as expected (there are very many cells which need painted in the row or column scrolled into view), but the performance of simply changing the active cell seems too slow.

  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    I am discussing this issue with the concerned team to get to know if there is any available way to do this.I will inform you as soon as we reach any conclusion.

     

    Thanks,

     

  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    I have tested the performance while scrolling it seems to be absolutely fine with 200 rows and 40 columns. I am using spread 5.0.3518.2008. If possible could you please provide me a video that shows the performance while scrolling

     

    Thanks,

     

  • Replied 8 September 2017, 2:05 pm EST

    Hi,


    I'm sorry it is taking so long.  The developers are very busy with v5, but your issue is definitely very important to us.  I have asked the developer to give me a progress report on this issue, and I should have more information soon.

  • Replied 8 September 2017, 2:05 pm EST

    Hello,

    If you reply to a post there is an Options tab at the top of the message
    under the header.  If you click that then you can add the attachment. 
    Make sure it is zipped and less than 256k. If its more than that then you can mail us the same at powersupport@grapecity.com with forum post Id in subject.

    Thanks.

  • Replied 8 September 2017, 2:05 pm EST

    is there a way for me to attach a video on this forum?
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels