Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

XlsFile.SetNamedRange performance with mono

 Post Reply Post Reply
Author
Ghislain Benrais View Drop Down
New Member
New Member
Avatar

Joined: 06 Nov 2014
Posts: 6
Post Options Post Options   Quote Ghislain Benrais Quote  Post ReplyReply Direct Link To This Post Topic: XlsFile.SetNamedRange performance with mono
    Posted: 19 Sep 2016 at 7:02pm
Hello,
I use Flexcel to create workbooks with many names (more than 50 000).

I ran the profiler and I'm a bit surprised by the slowness (10% slower) related to the creation of names.
My method is straghtforward :

    public void AddName(string name, string refersTo, bool hidden)
        {
            TXlsNamedRange range = new TXlsNamedRange(name, 0, 0, "=" + refersTo);
            range.Hidden = hidden;
            Xls.SetNamedRange(range);
        }

Flexcel'version : 6.11 (latest)

Here are visual studio's profiler's inclusive samples :
FlexCel.XlsAdapter.XlsFile.SetNamedRange : 10.59%
FlexCel.XlsAdapter.RecordedEvent.TakePicture : 10.26%

First question : what is this 'FlexCel.XlsAdapter.RecordedEvent.TakePicture' method ? I didn't find it in the sources.

The problem gets worse when run with mono under linux : the run is 75% slower. I didn't install mono's profiling so I can't send a profiler report, but my second and last question : why  should it be slower with mono/linux ?
Regards,
Ghislain

Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 19 Sep 2016 at 7:47pm
Well, 50,000 named ranges is indeed a lot, FlexCel is not optimized for that much names, as it compares the name with all existing names when you add a new one in order to not insert a name twice. This could be made much faster by having a sorted cache/hashtable of existing names, I'll see if it is feasible to add it.(The problem with caches is as always that there is one more thing to keep in synch when you modify names, and really, most files have not an important number of names). But it is doable, I'll let you know when it is working.

There are other issues with such a big number of names though that won't be that easy to solve, like for example the fact that every time you insert a row, you need to check (and potentially update) the 50,000 names to see if they were affected by the inserted row. But that should be slow in Excel itself too.

About the "TakePicture" event, the code in the dll is obfuscated: To profile you should profile over the sources themselves. I believe the actual method in 6.11 is:
FlexCel.XlsAdapter.TOle2Header.Save(System.IO.Stream,)
Which is probably an expected method to be slow as every record (even those 50,000 records from names) has to be saved.

About mono I am not sure, but indeed, mono is slower than windows .net, and not that optimized. I wonder what numbers do you get in .NET Core under linux?

Back to Top
Ghislain Benrais View Drop Down
New Member
New Member
Avatar

Joined: 06 Nov 2014
Posts: 6
Post Options Post Options   Quote Ghislain Benrais Quote  Post ReplyReply Direct Link To This Post Posted: 20 Sep 2016 at 3:38pm
Hello Adrian,
Thanks for your answer.
The purpose is to tag cells for my application, I use names because it is the only workaround I know to add a custom property to a range.
I'll try to cope some other way.
Regards,
Ghislain
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 20 Sep 2016 at 3:55pm
Just to let you know that right now I am changing the sources to behave much better with thousands of names. If you are interested, email me to adrian@tmssoftware.com with your registration email/code and I can send you a version to test as soon as it is ready. (probably by tomorrow).

It will probably fix this issue, but I am still not convinced having so many names is a good design choice, maybe you can use bigger ranges and refer to them in your app via the name and an index?
Say for example instead of defining:
Range0: A5
Range1: A6
Range2: A7

define just a Range02: A5:A7 and in your app use an index, say Range02.Top + index where index is from 0 to 2?

It of course depends in what you are exactly doing, which I am not really sure. I also don't know what you refer with adding a custom property to a range, if you can expand on that maybe I can offer you a better idea.
Back to Top
Ghislain Benrais View Drop Down
New Member
New Member
Avatar

Joined: 06 Nov 2014
Posts: 6
Post Options Post Options   Quote Ghislain Benrais Quote  Post ReplyReply Direct Link To This Post Posted: 21 Sep 2016 at 1:11pm
Just a remark about workbooks with many names, I created a workbook with 100 000 names (from row 1 to 100 000), then I insert rows/columns and noticed it is not slower.
But anyway using names is not a good idea, I'll use a simple hidden worksheet with formulas.
Thanks again,
Ghislain
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 21 Sep 2016 at 1:15pm
I am finishing the tests right now of the fix for multiple names: I tried it with 100,000 names here and it takes less than 1 second to create them.

I just need a little more time to test it isn't breaking anything, but later today or tomorrow I will be able to send you a copy. I could really not dig in the file you sent me yet so I can't really suggest a better solution, but if names is the best for this case, the new release should handle them fine.
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 25 Sep 2016 at 2:33pm
Hi,
Just to let you know that I've sent you a new version by email which should fix this. I've tried it with 150,000 names and it still is very fast. But I have noticed that with 100,000+ names, Excel will not show the "Name manager" dialog, and will do nothing when you go to formulas and press "name manager" (and so you won't be able to manage those names in Excel). I tested it with Excel 2010 and 2016, both fail to show the dialog with 100,000 names.

Back to Top
Ghislain Benrais View Drop Down
New Member
New Member
Avatar

Joined: 06 Nov 2014
Posts: 6
Post Options Post Options   Quote Ghislain Benrais Quote  Post ReplyReply Direct Link To This Post Posted: 03 Oct 2016 at 11:24am
I have just tested the new version and it works beyond my expectations, thanks a lot !
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down