XlsFile.SetNamedRange performance with mono

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

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?

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

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.

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

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.

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.

I have just tested the new version and it works beyond my
expectations, thanks a lot !