bugGNU Octave - Bugs: bug #59277, xls2oct and/or openxls behave...

 
 

bug #59277: xls2oct and/or openxls behave unexpected

Submitted by:  Dennis <dizze>
Submitted on:  Thu 15 Oct 2020 04:46:26 PM UTC  
 
Category:  Octave Forge Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Performance
Status:  Confirmed Assigned to:  Philip Nienhuis <philipnienhuis>
Originator Name:  Open/Closed:  Open
Release:  other Operating System:  Any

Add a New Comment (Rich Markup)
   

You are not logged in

Please log in, so followups can be emailed to you.

 

( Jump to the original submission)

Tue 20 Oct 2020 07:35:21 AM UTC, comment #16: 

(Dennis, this comment just in between the scenes, I'm heavily occupied by other things)
I looked a bit in your __OCT_xlsx2oct__.m changes.
At first sight it boils down to just eliminating the for loop; that can explain a LOT of performance gain - for loops are known to grind execution speed down excessively.
(Hmmm I wonder how/why that for loop came there (or stayed there) in the first place.)

What you can also do is (as I suggested earlier) replace
cellfun (@function, ...)
by
cellfun ("function", ...)
(e.g., L.152 in your the __OCT_xlsx2oct__.m you uploaded lastly, in comment #14, and see if it has any significant effect.
(see https://savannah.gnu.org/bugs/?58445, look in comment #11 there)
I need to replace all cellfun invocations that way but lack time ATM.

(OK now I really need to get (paid) work done :-) )

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 19 Oct 2020 08:28:31 PM UTC, comment #15: 

I tested the supposed fix a bit further with the real dataset that I use in one of my projects. We can ignore the section in which I propose to apply regexp to the complete string cell array. It doesn't work.

So back to the solution I previously proposed. That too works with my formula test sheet (Excel2019 stringTest2.xlsx).

I can't think of a situation where the output of the regexp would be a cell array with multiple indices. The proposed solution simply takes the first index, which so far seems to work in every test sheet I have tried. As far as I can tell, the bug report bug #51512 doesn't seem to provide any reason to think this may not work. Philip, do you have any examples which would yield an output of the regexp with multiple cell array indices?

Dennis <dizze>
Mon 19 Oct 2020 07:44:15 PM UTC, comment #14: 

Philip,

I have read the bug report and comments of bug #51512 and looked for useable Excel files in that thread. Unfortunately I can't find any. Doesn't matter, I can make my own :-).

First test: an excel sheet with 1000x10 unique strings (see attachment 1 for excel (Excel2019 stringTest1.xlsx) and attachment 2 for _OCT_xls2oct_.m). Original code (executing line 146) yields:

Elapsed time is 20.6734 seconds.
   #         Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------------
  32         cell2mat             9.210      45.08        20006
  23           regexp             6.132      30.02        10006
  13 __OCT_xlsx2oct__             2.054      10.06            1
  24          cellfun             0.864       4.23        90034
  40       str2double             0.505       2.47            3

Second try, commenting line 146, uncommenting line 147:

Elapsed time is 8.37976 seconds.
   #         Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------------
  23           regexp             5.304      63.57        10006
  13 __OCT_xlsx2oct__             1.647      19.73            1
  32       str2double             0.439       5.26            3
  48           strrep             0.231       2.77            5
  26              cat             0.218       2.61            7

This is much faster, as previously concluded.

Try 3: commenting lines 145-148, uncommenting new line 149. This applies regexp to the complete cell array strings at once. This yields:

Elapsed time is 4.11125 seconds.
   #   Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------
  93     regexp             3.161      69.93           30
114 str2double             0.259       5.73            6
112        cat             0.183       4.04           14
  49     strrep             0.178       3.95          100
103     system             0.122       2.69            1

This is even faster. Since this implementation uses nested cell2mat as in the original code, it seems to me that this should be acceptable. The only difference is that it cuts out the for loop.

In order to put this to the test a bit more, I applied the code with a LibreOffice6 file with identical contents (attachment 3 (LibreOffice6 stringTest1.xlsx)). The performance is similar (perhaps even a bit faster). The spreadsheet content is correctly reflected in rawarr.

Finally, I created an Excel file with a number of formulas that results in strings. Some point to each other, or even with multilevel references. Others use the indirect function, and yet others concat two strings. See attachment 4 (Excel2019 stringTest2.xlsx). Using the fastest option (regexp applied to the complete string cellarray), functions correctly and the results are properly shown in rawarr.

I tend to conclude that this is a proper performance fix.

(file #50017, file #50018, file #50019, file #50020)

Dennis <dizze>
Sun 18 Oct 2020 07:28:20 PM UTC, comment #13: 

Thanks for testing xlsopen.m; so one issue got fixed.
In hindsight it was a bit of a dumb error by me, but I also remembered how and why I came there.

I dug up the bug report about formulas in sharedStrings.xml,
https://savannah.gnu.org/bugs/?51512
There's a fair amount of profiling involved there, maybe it can help you. The issue of shared formulas was touched there (and postponed, with good reason).
IIRC that bug report was also about, or a prelude to, other report(s) about complex strings. Maybe later on I'll stumble upon it/those.

As to your code:
The aim of cell2mat() is to get rid of extra cell "wrapping". Applying cellfun to cell2mat output looks like it is re-wrapped again.
No need to enclose the regexp call in parens, it is already enclosed in commas.

Maybe experimenting with vertcat or horzcat can help with unwrapping regexp's output as well, but I am not so familiar with those functions.

I'll set bug status provisionally back to "confirmed" until I have more time to look into it and/or help you recoding string reading in OCT interface.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 18 Oct 2020 05:52:45 PM UTC, comment #12: 

I tested this line of code:
ctext{n, 1} = cellfun (@cell2mat, (regexp (strings{1, n}, '<t[^>]*>(.*?)</t>', "tokens")), 'UniformOutput', false);

It produces errors. I do not know how to use cellfun(@cell2mat,. ..) in this context such that it produces the correct output.

Dennis <dizze>
Sun 18 Oct 2020 05:22:37 PM UTC, comment #11: 

> Can you pleas try again with a patched xlsopen.m from here:


I tested it. First reinstated my java library and checked the functioning of the old xlsopen with my test script (previous comments). Indeed it did use UNO the first run, as previously. Next, I copied the new xlsopen, unloaded io and loaded io, and reran the test script. This time it uses OCT, as specified.

I next changed the script to specify using UNO, and that works as well: it now uses UNO. Since these are the only two interfaces I have available on my machine, I can't test xlsopen for the other interfaces, but based on my limited testing it seems to work as it should now.

Comming back to your question in comment #9:

> In your workflow, can you write spreadsheet contents to .csv files before processing?


I am afraid not. I am using Excel sheet to store some data, and created an Octave script that is actually being used as a API in a webservice. The excel file uses some simple formualas and formatting to properly check the validity of the data in it. I surely know that a dbase would be better, but that takes quite a bit implementation. Moreover, the Excel doesn't actually contain that much data. It is mostly strings and some numbers. The problem is that the commands used for the API cannot depend on previously loaded data, so every API call the excel is opened and loaded again. Consequently, there are quite a few loads, and the cell2mat used for SharedString really takes it's toll in performance. I will try and see if cellfun works better.

Dennis <dizze>
Sun 18 Oct 2020 03:57:07 PM UTC, comment #10: 

Can you pleas try again with a patched xlsopen.m from here:

http://hg.code.sf.net/p/octave/io/file/tip/inst/xlsopen.m

(I need to click "raw" in left column to init download)

just to check that you only get the interface you explicitly select?
Speed/performance will be tackled later.

Thanks

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 17 Oct 2020 11:09:58 AM UTC, comment #9: 

Dennis,

Yeah your initial bug report comprised a few issues, no worries, that often happens. I started to cherry pick the easiest one, interface issues, which as you described also gave rise to slow behavior and was reported elsewhere as well.
IMO proper functionality is always priority #1, speed comes later.

Then, in my comment #7:
"cell2mat (...)"
should read
"cellfun (...)"
(I shouldn't keep going on and on after midnight :-( )

"Diligence" - there's a reason that the current sharedStrings access code is what it is; and I must have various test spreadsheets lying around somewhere to check with. But again, presently I lack time to sort this all out, even to make it explicit, I simply have other priorities.
Later on I'm quite prepared (and curious) to pick it up.

As to speed / COM / databases / etc.:
In your workflow, can you write spreadsheet contents to .csv files before processing?
In the io package there's also csv2cell() that can read mixed .csv files (numeric and text) and does so blazingly fast. In fact it is what I use at work to read & process e.g., database exports of millions of lines (far beyond the limits of even .xlsx files). Once in a cell array data processing is usually very fast and can even be done in parallel using e.g., parcellfun.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 16 Oct 2020 10:41:37 PM UTC, comment #8: 

You are right, there is some thread drift. My apologies for that. Do note that my initial motivation to dig into this was slow behavior. This was due to both reasons that you summarize.

Having said that, it is good to here that you have a fix for the first issue (using UNO interface when OCT is specified). I can't use the COM interface, because the code must run on a Unix system as well.

With regard to issue 2, I understand that proper diligence is required. Can we make it explicit what that means? What should be tested in order to be sure that the new code works properly in all cases?

In addition, I am certainly willing to check the cell2mat("<function name>", <cell array>, ...) option, but I need some directions. In your code cell2mat is used for a simple cell array, and no function name is entered. So how does this apply to this specific usage?

Dennis <dizze>
Fri 16 Oct 2020 08:40:32 PM UTC, comment #7: 

@Dennis,
your bug report title "behave unexpected" allows some thread drift and that is happening a bit here.
Up till now I see 2 issues:

1. Interface trouble
You note the UNO interface being activated.
I think I found out how that can happen, and I have a fix as a corollary of bug #59273 (needs verification over there). Bug #59273 is partly another manifestation of the same bug affecting you here.
Bug status "confirmed" applies to this issue.

2. Speed (lack of)
IIRC the sharedStrings XML can contain nested or at least very complicated strings; IIRC there was even a bug report about it 2 or 3 years ago. So simplifying the code that handles it needs proper diligence.
Note that there are also "shared formulas", we haven't implemented support for that yet but that will also rely on how we read & update sharedStrings. (Most users just use cached cell values but other interfaces allow to uncover the underlying formulas rather than their results and that doesn't fully work yet in OCT.)

I'll soon come up with a patch for the interface issues, hopefully you also want to test that for me (like the one who entered bug #59273). That issue by itself warrants a bug fix release 2.6.3 of the io package.
I'm sorry you didn't report it earlier on.

As to speed, I doubt if I have enough time to dive into it now, but that will happen. The nice thing about Octave is that it is open source so you don't need to wait until it's fixed in a next release. As I understand it you've already fixed it for yourself, hopefully if you test that a bit more I can accept it with more confidence.
It recently emerged that function calls in cell2mat work faster as follows:
cell2mat ("<function name>", <cell array>, ...)
then
cell2mat (@<function name>, <cell array., ...)
so perhaps you can shave off some more execution time.

BTW if you happen to have Excel installed on your machine, the COM interface may work up to 5 or more times faster than OCT - but for large spreadsheets it may fill up RAM very soon.
The regular expressions that OCT uses are known to be slow for very very long strings.

For really large datasets I'd advise to reconsider if you really need them in spreadsheets. Dedicated databases usually outperform any spreadsheet and because of more rigid business rules will save you from many sneaky errors commonly seen in spreadsheets like incomplete cell references, badly copied cells, dangling cell cross-references, etc etc. Not to mention the stuff that Excel and even LibreOffice do, or don't, behind your back (e.g., recalculation and cell content updates, implicit formatting affecting content formats).

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 16 Oct 2020 02:53:06 PM UTC, comment #6: 

As I have been really troubled by speed issues, I dived into the problem some further. I know understand why additional sheets can have impact on the performance. This is due to the way xlsx sheets are constructed. All plain strings of all worksheets are stored in one xml file. When this gets big, many cell2mat calls are made.

I have tested a very simple solution. In '__OCT_xlsx2oct__' I have replaced a single line of code, namely line 146 by the new line 147 (see attached). This line uses two cell2mat calls, and is part of a for loop. However, as far as I can tell, the list of strings that is being processed here ALWAYS contains single cells. That is why we could simply use {1}{1} instead of cell2mat(cell2mat()). This is MUCH faster.

Using the attached test script and Excel, and uncommenting line 146 (with line 147 commented) (i.e. the original code), the result is:

Elapsed time is 0.393326 seconds.
   #         Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------------
  10         cell2mat             0.156      41.26          537
   9           regexp             0.088      23.23          285
  24 __OCT_xlsx2oct__             0.050      13.20            3
  14          cellfun             0.017       4.38         2439
   2          xls2oct             0.015       3.92            3
  36       str2double             0.009       2.35           18
  50   parse_sp_range             0.008       2.15            3
  29            fread             0.005       1.34            6
  33              cat             0.004       1.15          285
   4         prefix !             0.002       0.62         2487
  27            fopen             0.002       0.61            6
  19             sort             0.002       0.60          255
  44          col2num             0.002       0.55          742
  15              all             0.002       0.50         1384
   7           ischar             0.002       0.44         1563
  57           strrep             0.001       0.39           18
  16             size             0.001       0.38          546
  40          reshape             0.001       0.29          282
  11           nargin             0.001       0.26          559
  63           strtok             0.001       0.22            3

Commenting line 146 and uncommenting line 147 in '__OCT_xlsx2oct__' yields:

Elapsed time is 0.174529 seconds.
   #         Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------------
   9           regexp             0.079      47.75          285
  24 __OCT_xlsx2oct__             0.028      17.02            3
   2          xls2oct             0.013       7.55            3
  36       str2double             0.008       5.07           18
  50   parse_sp_range             0.006       3.88            3
  10         cell2mat             0.006       3.77           21
  14          cellfun             0.005       3.12          117
  29            fread             0.004       2.23            6
  33              cat             0.003       2.02           21
  27            fopen             0.002       1.24            6
  44          col2num             0.002       1.05          742
   7           ischar             0.001       0.86         1563
  57           strrep             0.001       0.77           18
  63           strtok             0.001       0.51            3
  41            clear             0.000       0.28            3
  51          deblank             0.000       0.26            3
  58            index             0.000       0.24            3
  31           fclose             0.000       0.24            6
   8       chknmrange             0.000       0.23            3
  35          strncmp             0.000       0.18           12

In my real life script, which uses a bigger Excel sheet, differences are much more pronounced. As an example, using the original '__OCT_xlsx2oct__' code yields:

Elapsed time is 10.1298 seconds.
   #                             Function Attr     Time (s)   Time (%)        Calls
-----------------------------------------------------------------------------------
  81                             cell2mat             5.105      51.74        20903
101                     __OCT_xlsx2oct__             1.249      12.66           15
  80                               regexp             1.148      11.64        10657
  29                              cellfun             0.434       4.39        94317
  73                               system             0.402       4.07            1

While the newly proposed code yields:

Elapsed time is 3.81288 seconds.
   #                             Function Attr     Time (s)   Time (%)        Calls
-----------------------------------------------------------------------------------
  80                               regexp             1.252      33.20        10657
101                     __OCT_xlsx2oct__             0.775      20.55           15
  73                               system             0.404      10.71            1
143               @Recipe/calc_magistral             0.169       4.48            1
  28                                 load             0.145       3.84           16

That really makes a difference, the duration went from 10s to 7s.

@philipnienhuis, can you please check that this single updated line can be implemented (i.e. the cell with strings indeed always contains a single nested cell)? If so, could you please release a new version of io as soon as possible?

NB: this solves the issue with additional sheets having an effect on speed, but it doesn't solve the issue that xls2oct sometime uses UNO even tough OCT is specified.

(file #49996, file #49997, file #49998)

Dennis <dizze>
Fri 16 Oct 2020 12:23:52 PM UTC, comment #5: 

As an example of the behavior described in comment #4, I have attached the same dummy excel file, but with a 'payload' worksheet added.

In addition, I renamed the java folders (see the pathnames mentioned in comment #3), so that the UNO interface does not work. This solves both the error, as well as the fact that xls2oct uses the UNO interface.

So, when I run the test script (comment #3) with the 'payload' sheet, the profile output is as follows:

   #         Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------------
   9         cell2mat             0.226      43.94          537
   8           regexp             0.111      21.66          285
  23 __OCT_xlsx2oct__             0.065      12.72            3
  13          cellfun             0.025       4.95         2439
   1          xls2oct             0.021       4.03            3
  49   parse_sp_range             0.009       1.80            3
  35       str2double             0.009       1.70           18
  32              cat             0.006       1.09          285
   6           ischar             0.004       0.77         1563
  43          col2num             0.004       0.73          742
  28            fread             0.003       0.64            6
  56           strrep             0.003       0.62           18
  14              all             0.003       0.62         1384
   3         prefix !             0.003       0.60         2487
  18             sort             0.003       0.60          255
  15             size             0.002       0.45          546
  26            fopen             0.002       0.31            6
  39          reshape             0.002       0.30          282
  10           nargin             0.001       0.28          559
   5          isempty             0.001       0.26          606

When that sheet is deleted, the profile is as follows:

   #         Function Attr     Time (s)   Time (%)        Calls
---------------------------------------------------------------
   8           regexp             0.116      35.95          111
   9         cell2mat             0.095      29.62          189
  23 __OCT_xlsx2oct__             0.034      10.68            3
  13          cellfun             0.017       5.23          873
  35       str2double             0.011       3.43           18
   1          xls2oct             0.007       2.32            3
  32              cat             0.005       1.62          111
   6           ischar             0.004       1.32         1563
  28            fread             0.004       1.15            6
  56           strrep             0.003       1.08           18
  43          col2num             0.003       1.02          742
  49   parse_sp_range             0.003       0.89            3
  26            fopen             0.002       0.59            6
  14              all             0.002       0.48          514
   3         prefix !             0.002       0.47          921
  62           strtok             0.001       0.45            3
  18             sort             0.001       0.37           81
  15             size             0.001       0.30          198
  50          deblank             0.001       0.29            3
  57            index             0.001       0.27            3

The time spend by cell2mat more than halves! I don't understand what causes this, as xls2oct is called with a specific sheet. Moreover, _OCT_xlsx2oct_ only extract the specified sheet. What may cause this interference? Note: this seems to be much worse when the cell contents are plain text urls (as in the example).

To summarize, in my attempt to speed up things I have encountered two issues:
- xls2oct using UNO when OCT is specified
- the time to extract data from one worksheet is influenced by the presence (and contents) of another worksheet, especially when it contains urls

I hope these issues can be solved.

(file #49993)

Dennis <dizze>
Fri 16 Oct 2020 10:28:55 AM UTC, comment #4: 

Here is another thing that is bothering me. I was wondering why there is a huge difference between the profile results of my dummy case and the real sheet that I use. I am referring to the second call of the test script (when it uses OCT). The most time consuming function is cell2mat. But why the difference between my original sheet and the dummy?

To test this, I have stripped my real Excel sheet step by step. First removed references between sheets, removed conditional formatting, etc. That all didn't do much. What really made a big difference was removing other sheets in the workbook that contained quite a bit of text. For some reason the presence or absence of these other sheets had a huge impact on the time used by cell2mat. This seems strange to me, because the xls2oct function gets a specific worksheet as input. Why would the presence or absence of other worksheets make any difference on the time spend on cell2mat?

Dennis <dizze>
Fri 16 Oct 2020 08:01:18 AM UTC, comment #3: 

Here are the answers to your questions:

> I suspect this is partly or wholly a duplicate of bug #59273.


May not be the case, as I noticed the same behavior with v2.6.1 too.

> 1. What is the output of
> javaclasspath
> ?


>> javaclasspath

   STATIC JAVA PATH

      - empty -

   DYNAMIC JAVA PATH

      C:\Program Files\LibreOffice\program
      C:\Program Files\LibreOffice\program\classes\unoil.jar
      C:\Program Files\LibreOffice\program\classes\jurt.jar
      C:\Program Files\LibreOffice\program\classes\juh.jar
      C:\Program Files\LibreOffice\program\classes\unoloader.jar
      C:\Program Files\LibreOffice\program\classes\ridl.jar

> 2. What is the output of
> chk_spreadsheet_support ('', 3)
> ?


>> chk_spreadsheet_support ('', 3)


(OCT interface... OK, included in io package)

Checking Excel/ActiveX/COM... not working.
1. Checking Octave's Java support... OK.
2. Checking Java dependencies...
  Checking Java JRE presence.... OK, found one.
  Java seems to work OK.
  Java (version 1.8.0_261) seems OK.
  Maximum JVM memory:  1794 MiB; sufficient.
Checking javaclasspath for .jar class libraries needed for spreadsheet I/O...:

Basic POI (.xls) <poi-3> <poi-ooxml>:
  apache-poi./poi-3/poi-4....jar missing
  apache-poi-ooxml./poi-ooxml-3/poi-ooxml-4....jar missing
  => Not all classes (.jar) required for POI in classpath

POI OOXML (.xlsx) <xbean/xmlbean> <poi-ooxml-schemas> <dom4j>:
  xbean/xmlbean....jar missing
  apache-poi-ooxml-schemas/poi-ooxml-schemas....jar missing
  dom4j....jar missing
  commons-collections4....jar missing
  commons-compress-1.18....jar missing
  => Some classes for POI OOXML support missing

JExcelAPI (.xls (incl. BIFF5 read)) <jxl>:
  jxl....jar missing
  => Not all required classes (.jar) for JXL in classpath

OpenXLS (.xls - BIFF8 & .xlsx - OOXML) <OpenXLS>:
  OpenXLS....jar missing
  gwt-servlet-deps....jar missing

ODF Toolkit (.ods) <odfdom> <xercesImpl>:
  odfdom....jar missing
  xerces....jar missing
  xml-apis/xml-commons-apis....jar missing
  => Not all required classes (.jar) in classpath for OTK

jOpenDocument (.ods + experimental .sxc readonly) <jOpendocument>:
  jOpenDocument....jar missing
  => Not all required classes (.jar) for JOD in classpath

UNO/Java (.ods, .xls, .xlsx, .sxc) <OpenOffice.org>:
  - program OK
  - unoil.jar OK
  - jurt.jar OK
  - juh.jar OK
  - unoloader.jar OK
  - ridl.jar OK
  => UNO (OOo) OK
Some class libs lacking yet...

ans =  128

> 3. Exactly what commands do you use to read your spreadsheet?


I have attached a dummy example. This has exactly the same behavior: first read it uses UNO (even though I explicitly call for the "OCT" interface), second time it uses OCT and is much faster.

> Maybe it is from some of the Java interfaces or LibreOffice complaining about invalid XML? (L.O. is called when the UNO interface is invoked)


Could be the case, but I don't want it to use UNO and explicitly call for OCT.

> If you don't want UNO to be invoked you can just do
> chk_spreadsheet_support ('', -1)
>
> to wipe all Java class libs loaded by io package's initialization script from the Java class path.


This doesn't change anything. If I clear all, then use the above mention function call, and then call my script, it still uses UNO.

(file #49989, file #49990)

Dennis <dizze>
Thu 15 Oct 2020 05:57:48 PM UTC, comment #2: 

Thank for your bug report.

I suspect this is partly or wholly a duplicate of bug #59273.

But just to be sure:

1. What is the output of
javaclasspath
?

2. What is the output of
chk_spreadsheet_support ('', 3)
?

3. Exactly what commands do you use to read your spreadsheet?

The error

"CE> func=xmlSecCheckVersionExt:file=..\src\xmlsec.c:line=188:obj=unknown:subj=unknown:error=19:invalid version:mode=abi compatible;expected minor version=2;real minor version=2;expected subminor version=25;real subminor version=26"

... looks completely unfamiliar to me.
Maybe it is from some of the Java interfaces or LibreOffice complaining about invalid XML? (L.O. is called when the UNO interface is invoked)

If you don't want UNO to be invoked you can just do

chk_spreadsheet_support ('', -1)

to wipe all Java class libs loaded by io package's initialization script from the Java class path.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Thu 15 Oct 2020 04:50:12 PM UTC, comment #1: 

Forgot to mention:
- this also occured in v2.6.1
- Every first call this error is shown in the command window:

CE> func=xmlSecCheckVersionExt:file=..\src\xmlsec.c:line=188:obj=unknown:subj=unknown:error=19:invalid version:mode=abi compatible;expected minor version=2;real minor version=2;expected subminor version=25;real subminor version=26

Dennis <dizze>
Thu 15 Oct 2020 04:46:26 PM UTC, original submission:  

In a script that uses many reads (no writes) from an Excel sheet, I am trying to optimize for speed. For that reason I do not use xlsread, but xlsopen/xls2oct/xlsclose. I call xlsopen using "OCT" as the defined interface.

I noticed that every first time a sheet is read, it takes a long time. Using the profiler I found out that this first time, the xls functions seem to use the UNO interface.

After the first run, the profile looks like this:
   #                             Function Attr     Time (s)   Time (%)        Calls
-----------------------------------------------------------------------------------
  59                     _UNO_spsh2oct_             8.068      90.04            3
  54                             cell2mat             0.355       3.97          892
   1 @Recipe/read_magistral_relationships             0.105       1.17            1
  62                             strsplit             0.066       0.74          105
  74                       parse_sp_range             0.050       0.55           99
  72                 _UNO_getusedrange_             0.042       0.47            3
  83                               strtok             0.035       0.39           99
  23                              cellfun             0.024       0.26         3680
  81                                index             0.019       0.21           99
  22                                 load             0.018       0.21            2
  75                              deblank             0.018       0.20           99
  53                               regexp             0.016       0.17          306
  50                              xls2oct             0.015       0.17            3
  21                   installed_packages             0.012       0.13            1
  37                                 sort             0.009       0.10          711
  63                          parseparams             0.009       0.10          105
  56                                  all             0.008       0.09         2123
   8                             prefix !             0.007       0.08         4633
  61                           javaObject             0.006       0.07          193
  13                              isempty             0.005       0.06         2568

The second time, it looks like this:
   #                             Function Attr     Time (s)   Time (%)        Calls
-----------------------------------------------------------------------------------
  54                             cell2mat             2.121      55.11         5152
  53                               regexp             0.638      16.59         2148
  60                     _OCT_xlsx2oct_             0.408      10.59            3
  23                              cellfun             0.201       5.22        22781
   1 @Recipe/read_magistral_relationships             0.096       2.50            1
  70                           str2double             0.039       1.02           18
  37                                 sort             0.033       0.86         2820
   8                             prefix !             0.033       0.85        22819
  67                                  cat             0.031       0.79         2354
  56                                  all             0.030       0.78        12776
  75                              col2num             0.022       0.57         3428
  49                                 size             0.021       0.55         5210
  28                               ischar             0.021       0.55        10630
  50                              xls2oct             0.019       0.49            3
  55                               iscell             0.016       0.43         5158
  22                                 load             0.016       0.41            2
  72                              reshape             0.013       0.34         2333
   4                               nargin             0.013       0.33         5179
  13                              isempty             0.012       0.32         6022
  21                   installed_packages             0.012       0.32            1

Note that the function '__UNO_spsh2oct__' no longer appears in the list. This second time is much faster as well.

Note 1: after 'clear functions' the profile looks like the first.
Note 2: I am using an xlsx spreadsheet. No clue why UNO is called.
Note 3: I use io package version 2.6.2

Dennis <dizze>

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

Attached Files
file #50017:  Excel2019@stringTest1.xlsx added by dizze (74KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #50018:  __OCT_xlsx2oct__.m added by dizze (10KiB - application/octet-stream)
file #50019:  LibreOffice6@stringTest1.xlsx added by dizze (129KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #50020:  Excel2019@stringTest2.xlsx added by dizze (12KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #49996:  __OCT_xlsx2oct__.m added by dizze (10KiB - application/octet-stream)
file #49997:  BLAAAAA.m added by dizze (599B - application/octet-stream)
file #49998:  test.xlsx added by dizze (65KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #49993:  test.xlsx added by dizze (65KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #49989:  test.xlsx added by dizze (44KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #49990:  BLAAAAA.m added by dizze (571B - application/octet-stream)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by dizze (Submitted the item)
  • -email is unavailable- added by dizze (This concerns the IO package)
  •  

    Do you think this task is very important?
    If so, you can add your encouragement to it.
    This task has 0 encouragements so far.

    Only project members can vote.

     

     

     

    Follow 18 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2020-10-19 dizze Attached File- => Added Excel2019@stringTest1.xlsx, #50017
        Attached File- => Added __OCT_xlsx2oct__.m, #50018
        Attached File- => Added LibreOffice6@stringTest1.xlsx, #50019
        Attached File- => Added Excel2019@stringTest2.xlsx, #50020
    2020-10-18 philipnienhuis StatusNeed Info => Confirmed
    2020-10-18 philipnienhuis StatusConfirmed => Need Info
    2020-10-16 philipnienhuis StatusNeed Info => Confirmed
        Assigned toNone => philipnienhuis
        Release5.2.0 => other
        Operating SystemMicrosoft Windows => Any
    2020-10-16 dizze Attached File- => Added __OCT_xlsx2oct__.m, #49996
        Attached File- => Added BLAAAAA.m, #49997
        Attached File- => Added test.xlsx, #49998
    2020-10-16 dizze Attached File- => Added test.xlsx, #49993
    2020-10-16 dizze Attached File- => Added test.xlsx, #49989
        Attached File- => Added BLAAAAA.m, #49990
    2020-10-15 philipnienhuis StatusNone => Need Info
    2020-10-15 dizze Carbon-Copy- => Added prnienhuis@users.sf.net

    Back to the top


    Powered by Savane 3.5