tipps and tricks to reduce the loading time

Posted by: hugoslav on 8 September 2017, 1:23 pm EST

  • Posted 8 September 2017, 1:23 pm EST

    i have the problem, that some of my farpoint files are about 20-40mb. the loading-time varies between 30-60sec.

    can somebody give me some tipps and trick to reduce the loading time? can i delete some information out of the farpointfiles without the lost of necessary information?

    please help me, because a loading-time over 5 sec for a workbook is useless ...

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    It sounds like you have used the Save method to save the Spread control to a file and now you are trying to open that file. The issue is the file is using XML serialization which is slow. If you were to save the Spread (or at least the parts of the Spread you want to keep) using Binary serialization instead, this will make your file being saved smaller and load time much faster.

  • Replied 8 September 2017, 1:23 pm EST

    Did you try calling the SuspendLayout method and the ResumeLayout method?  This will help reduce the loading time.  You can also turn off the AutoUpdateNotes and AutoCalculation.  Also, if you are using the Text property to set the data in a cell then use the Value property instead.  If you are assigning celltypes to alot of cells, see if there is a way to set the celltype to the entire column or row if possible.
  • Replied 8 September 2017, 1:23 pm EST

    hmmm, i tested SuspendLayout, ResumeLayout, AutoUpdateNotes and AutoCalculation, but de loadingtime is the same as before ...
  • Replied 8 September 2017, 1:23 pm EST

    ok, the trick with binary serialization is good. a file with 24mb is loading 14sec instead of 50sec. but 14sec is still too much.

    are there any other ideas to reduce the loading-time? ;)

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    There is not really anything to do to make this faster without seriailizing exactly what you want out if you do not need the entire SheetView object. One thing to try before implementing your own seriailization would be to deserialize each SheetView object and store all SheetView objects into an array. This way you can load all SheetView objects into the Spread book at once (using the AddRange method). This should be faster.

  • Replied 8 September 2017, 1:23 pm EST

     Hello,


    If you need to seriailize the StyleModel as well, you may not be able to get this much faster. Have you tried running the application outside the development environment in a runtime environment to see if running it outside a debugger makes the peformance better? If you are still having issues, could you post something for us to debug the issue to see if we can find any way to make this perform faster?

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    After changing to use binary serialization the size of the outputted file is still 24MB? If this is the case, then there is not a way to load the file any faster than what you are seeing. What code are you using now for saving and loading the file with the serialized code?

  • Replied 8 September 2017, 1:23 pm EST

    hi,

    thx for the tipps. but this  takes also about 14-15sec, the same time like i serialize and deserialize the whole book or the single sheets.

     which information of a sheet can i ignore (set to null ...),  to make it faster? but i don't want to loose necessary information ;)

     

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    This depends on what you need. If you only need the data, notes and formulas, but do not need styles (like BackColor), you can only seriailize the DefautlSheetDataModel.

  • Replied 8 September 2017, 1:23 pm EST

    i need some styleinfos ... i think i don't get this books loading faster :(
  • Replied 8 September 2017, 1:23 pm EST

    the hole file is now about 5mb (before 24mb). but the loading time is still 14sec! now i tried to split the sheets and serialize them alone. i have 4 sheets and the loading time for one 3 sheets is about 1-2sec and for one sheet de loading-process needs 12sec.

     

     FarPoint.Win.Spread.SheetView sheet = null;            
     System.Runtime.Serialization.Formatters.Binary.BinaryFormatter b = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

    FarPoint.Win.Spread.FpSpread book = new FarPoint.Win.Spread.FpSpread();

    .........

    for (int i = 0; i < book.Sheets.Count; i++)
    {

       using (FileStream fs = new FileStream(filename + i + ".xml", FileMode.Create))
       {
          b.Serialize(fs, book.SheetsIdea);
       }

    }

    .........

    for (int i=0; i < 4; i++)

     {
          using (FileStream fs = new FileStream("C:/Dokumente und Einstellungen/AKummer/Desktop/superfile" + i + ".xml", FileMode.Open))
           {
                sheet = (FarPoint.Win.Spread.SheetView)b.Deserialize(fs);
                book.Sheets.Add(sheet);
           }     
     }

  • Replied 8 September 2017, 1:23 pm EST

    hi,

    i zipped my file with 7-zip and changed the filetype from .7z to .zip ;)

    (bigFarpointFile.7z -> bigFarpointFile.zip)

    i hope you can unzipp the file!


    2008/08/bigFarpointFile.zip
  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    I was able to unzip the XML file and noticed right away it looks as if this got generated by loading an Excel file. The RowCount and ColumnCount were much larger than they needed to be, so I fixed that to the correct rowcount and column count by loading the XML, setting the properties to NonEmptyRowCount and NonEmptyColumnCount and saving the file back out.


    This, as expected did not help much, so I loaded the XML and saved the SheetView objects to binary. This took my load time from about 120 seconds to about 45 seconds. I then compiled it to an executable and ran the project outside the debugger. The load time went to just under 5 seconds. I think you should see acceptable results as soon as you compile to release and run the executable outside the debugger.

  • Replied 8 September 2017, 1:23 pm EST

    hello,

    thanks a lot. the loadingtime is about 3sec now.

    that's a good performance.

  • Replied 8 September 2017, 1:23 pm EST

    should i send you an email with the farpoint-file? the fastest loading-time for an 24mb file is 14sec for me. maybe you can load this faster without information-lost ...
  • Replied 8 September 2017, 1:23 pm EST

    hello again ;)

    its really fast now, but i'm loosing information!

    i'm loosing the cell-referencing-information ... for example:

    before serializing:

    c6=F48/C5*100

    after serializing:

    c6=#NUM!

    have you got this problem too?

     

     

  • Replied 8 September 2017, 1:23 pm EST

    ok, i resolve the problem on my own ;)

    after deserialzisation use book.LoadFormulas(true); .............. then the formulas are working fine ^^

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    Yes. Could you post your spreadsheet file you serialized for us to debug with?

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    An unrelated thought, could you put some of your loading on a separate thread and hide it behind some kind of splash screen to buy a few seconds so it won't look so long?

  • Replied 8 September 2017, 1:23 pm EST

    hi,

    thank you for your help! i find out, that the problem is the serialization and not the deserialization. when i have a serialized file from 4.0.3503 it is deserializing in 4.0.3503 and 4.0.3508 the same time. but it is a difference between the serialized filed from 4.0.3503 and 4.0.3508. in the older version my 35mb file is serialized to a 5mb file and with the 4.0.3508 i get a 14mb file. i use the same code like above to serialize the only difference is, that i use one time the 4.0.3503 and one time the 4.0.3508 dlls. 

    i think there is a update in de serialization-model of the spread in the newer version, because the file is much greater ...

  • Replied 8 September 2017, 1:23 pm EST

    hi,

    but you have to serialize the file with the dll, you use to deserialize it!

    my code to serialize:

                    System.Runtime.Serialization.Formatters.Binary.BinaryFormatter b = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
                    using (FileStream fss = new FileStream("test_farpoint_binaer_serialisiert2.xml", FileMode.Create))
                    {
                        b.Serialize(fss, spread.Sheets);                   
                    }                

  • Replied 8 September 2017, 1:23 pm EST

    hi,

    i have a problem to zip my big testfile > 300 kb ... ;) 

    but you can use the bigFarpointFile.zip, that i already load up in this thread!

     my code:

             private static void BinaryLoad()
             {
                 FarPoint.Win.Spread.FpSpread spread = new FarPoint.Win.Spread.FpSpread();
                 FarPoint.Win.Spread.SheetViewCollection sc = null;
                 System.Runtime.Serialization.Formatters.Binary.BinaryFormatter b = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

                 System.Console.WriteLine("***** LoadFarpointFileBinary *****");
                 System.Diagnostics.Stopwatch swAll = new System.Diagnostics.Stopwatch();
                 swAll.Start();
                 System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
                 sw.Start();

                 using (FileStream fs = new FileStream("test_farpoint_binaer_serialisiert.xml", FileMode.Open))
                 {
                     object obj = b.Deserialize(fs);

                     sw.Stop();
                     System.Console.WriteLine("Deserialize: " + sw.ElapsedMilliseconds / 1000M);
                     sw.Reset();
                     sw.Start();

                     sc = (FarPoint.Win.Spread.SheetViewCollection)obj;

                     sw.Stop();
                     System.Console.WriteLine("Cast to SheetViewCollection: " + sw.ElapsedMilliseconds / 1000M);
                     sw.Reset();
                     sw.Start();
                 }
                 FarPoint.Win.Spread.FpSpread book = new FarPoint.Win.Spread.FpSpread();
                 book.Sheets.Count = 0;
                 foreach (FarPoint.Win.Spread.SheetView sheet in sc)
                 {
                     book.Sheets.Add(sheet);
                 }

                 sw.Stop();
                 System.Console.WriteLine("Add Sheets to Spread: " + sw.ElapsedMilliseconds / 1000M);
                 sw.Reset();
                 sw.Start();

                 book.LoadFormulas(true);

                 sw.Stop();
                 swAll.Stop();
                 System.Console.WriteLine("Load Formulas: " + sw.ElapsedMilliseconds / 1000M);
                 System.Console.WriteLine("***** finish: " + swAll.ElapsedMilliseconds / 1000M + " *****");
             }

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    I do not know of anything that may cause this issue. Could you send your file you are trying to deserialize for us to debug?

  • Replied 8 September 2017, 1:23 pm EST

    hi,

    i have this performance-problem again :(

    with the old version, the big farpoint-file is loading with the binary-serialization in 5 seconds. with the newest version of farpoint the loading time with binary-serialization is about 20 seconds ...

    in detail:

    ==================================

     version 3503 ... loadingTime: 4.588 sec

     - Deserialize: 3.673 sec

    - Cast to ShetViewCollection: 0 sec

    - Add Sheets to Spread: 0.007 sec

    - Load Formulas: 0,907

     ==================================

    version 3508 ... loadingTime: 21,978 sec

     - Deserialize: 18.937 sec

    - Cast to ShetViewCollection: 0 sec

    - Add Sheets to Spread: 0.01 sec

    - Load Formulas: 3,027

     ==================================

     Please help me. why is the newest version so slow? can i increase the performance? loading times over 5 seconds are useless ...

     

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    We are constantly looking for ways for increasing the performance of the spreadsheet. So, are you still having issues with the Spread being slow?

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    I tested this with the 4.0.3503, 4.0.3508 and the 4.0.3509 (we are getting ready to release) and with each version the load time was the same. I am not seeing the extra time loading until I loaded in debug mode from running the application in the IDE. When I ran the release application, the load time was exactly the same for all versions. Could you let me know how to reproduce this issue?

  • Replied 8 September 2017, 1:23 pm EST

    Hello,


    I used the bigFarPointFile you sent and loading this into Spread using versions 4.0.3503 and 4.0.3508. Saving using the code you provided for serializing the SheetView collection yielding a 9.55MB file no matter which version of the Spread assemblies I used. Could you post a small zipped project reproducing this issue? Or you can email this to us if it is too large for the forums.

  • Replied 8 September 2017, 1:23 pm EST

    hello,

    when i serialize the spread.Sheets with 3503 i get a file with the size of 5mb, when i serialize the spread.Sheets with 3508 i get a file with 14mb. and so the deserializing-speed of the smaller file is shorter .... the 5mb file form 3503 deserializes in 3sec and the 14mb form 3508 deserializes in >15sec.

    the longer loading-time results of the bigger serialized-file in the version 3508.

    thx for help

     

     

Need extra support?

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

Learn More

Forum Channels