5 ms·

Could someone report how feasible it is to run this in LibreOffice Calc?

ryanjshaw 7y ago
It's in the linked readme: "While the files open in LibreOffice (tested in version 6.4.0.3 (x64)), it is slow to the level of being unusable. We have not tested in Apache OpenOffice."
- whereistimbo 7y ago
  In my opinion this might because of xlsx compatibility overhead. Using ods format might speed up a bit.
  - dclusin 7y ago
    Microsoft has had really sharp people working on spreadsheet performance for many years. I remember reading a blog post from I believe Joel Spolsky or someone talking about what excel is doing behind the scenes to achieve high performance and I was pretty impressed. One example that comes to mind was that spreadsheets are just memory mapped files and the layout of the file on disk is identical to the data structures in memory. This allows them eschew translation to a data interchange format. So they got performance at the cost of interoperability, which is probably what's hampering open office & friends.
    - blattimwind 7y ago
      > One example that comes to mind was that spreadsheets are just memory mapped files and the layout of the file on disk is identical to the data structures in memory. This allows them eschew translation to a data interchange format. So they got performance at the cost of interoperability, which is probably what's hampering open office & friends. That improves save/restore performance, but in and of itself doesn't do much about execution performance of macros.
      - dclusin 7y ago
        It would help program runtime as well because the macro could be operating on data not loaded in memory. Not having to marshal and un-marshal data can save lots of execution time, especially in the face of non-contiguous reads/writes. Databases store information using B-Tree's for example so that they can calculate the offset of the data and jump directly to it. It would probably take a lot of gymnastics to get this from an XML or JSON file interchange format. I'm sure they could probably come up with something that is both portable and performant but it's probably not a big priority at Microsoft.
    - derefr 7y ago
      IMHO there's no reason to memory-map the interchange format itself. I would predict/expect that both LibreOffice and MS Office (with their modern XML-based formats) are actually mmap(2)ing some temp file and treating it as an "on-disk working-state heap", and then importing from interchange formats by allocating from that heap / exporting to interchange formats by chasing pointers that end up inside that heap. (This is, after all, what every RDBMS does for its working state. It's pretty optimal.) Even if you have a memory-mapped interchange format, I'd still expect them to have a separate disk-backed working heap for all the stuff that doesn't belong in the file but is nevertheless very large (e.g. cached intermediary computation results of spreadsheet cells); and, if they have it, they may as well just use it for most things by default. Thus, I would expect that even in old versions of MS Office, the in-memory data structures were actually an interchange format of sorts—not the ones being updated with each keystroke, but rather ones that'd be memcpy(2)ed into on export. (This also prevents you from either having to add a page-table structure to your file, or else constantly "defragment" it as data structures change.)
      - dclusin 7y ago
        Not sure why you were getting downvoted these are pretty reasonable comments. The database example and use of B-Trees you mentioned is a good one. It’s definitely possible to create a performant and portable document specification and others have. I just strongly suspect that the performance issue that libre office and others have is more of a manpower issue and not having equivalent resources and knowledge of the excel formats rather than some shortcoming in ms’ own file formats.
    - Someone 7y ago
      That’s certainly history, if you use a modern file format such as .xlsx, and, likely, also if you use the old format. Microsoft likely changed several in memory structures when Excel went 64-bit, if not earlier. One thing that Execl does is multi-threaded recalculation (https://docs.microsoft.com/en-us/office/client-developer/excel/multithreaded-recalculation-in-excel https://docs.microsoft.com/en-us/office/client-developer/exc...)
      - saber6 7y ago
        > Microsoft Office Excel 2007 was the first version of Excel to use multithreaded recalculation (MTR) of worksheets. You can configure Excel to use up to 1024 concurrent threads when recalculating, regardless of the number of processors or processor cores on the computer. Somewhere, there is probably someone running hundreds of threads for excel (likely in a beefy VM/VDI). It is probably wired so deep into their business that they are afraid to move to other methods (that are more scalable). But such is the power of excel. What you see is what you get is not to be underestimated.
  - zweep 7y ago
    Also Microsoft makes tens of billions of dollars per year from Excel and even if only 1% of that goes back into performance, that’s a lot of optimization.
    - harry8 7y ago
      Yeah! Shame it's actually 0% :-(
      - jdsully 7y ago
        Having been paid to do that task in a prior life, I can assure you its greater than 0%.
        harry8 7y ago
        prior life, I would believe. "was greater than" might have more accuracy, right?
        jdsully 7y ago
        Its not like I stopped all contact with friends there. They still have people working on performance.
        harry8 7y ago
        I'm not trying to be rude to you. I'll believe you that you worked there, worked on excel and maintain contact with people still htere and that people are "working on performance" and you can believe me that we just can't see the results of that work. But yes, I'm a bit jaded. I think you'll understand why that is too. The vast fortune in revenue that doesn't fix bugs put me off excel in a big, big way. That was 2008 or so. Here's Andrew Gelman in 2013 on the topic: "Microsoft has lots of top researchers so it’s hard for me to understand how Excel can remain so crappy. I mean, sure, I understand in some general way that they have a large user base, it’s hard to maintain backward compatibility, there’s feature creep, and, besides all that, lots of people have different preferences in data analysis than I do. But still, it’s such a joke. Word has problems too, but I can see how these problems arise from its desirable features. The disaster that is Excel seems like more of a mystery." https://statmodeling.stat.columbia.edu/2013/04/17/excel-bashing/ https://statmodeling.stat.columbia.edu/2013/04/17/excel-bash... We've heard from microsoft so many times they have people working on all those bugs too. I remember the sheer disappointment in testing the newly fixed rand() function after all the fanfare by filling a page with =rand(), conditionally formatting when negative to be red and seeing the page turn largely red after a couple of F9 re-calculates. I simply don't believe that excel programmers are idiots and have been for 15 years. That leaves "they say they're working on it but they are not, not really." At the top of my alternatives. Is there a better one you can suggest? Enlighten me please, how does this epic cluster of fudge happen? You were probably there while it was ongoing. Parts of excel work well, /all/ the stats should have been removed 15 years ago as unfit for purpose and zero will or ability or effectiveness in getting a fix and making good. Excel, just don't. That is a pretty reasonable response, don't you think? Yeah it's sad. I don't relish it but let's not pretend it's all ok, yeah? But I guess the masses of revenue keep coming in so I guess it is all ok from microsoft's point of view. Are you ok with that yourself?
  - alok-g 7y ago
    OP co-author here. It could be worth a shot to try (I'll do when time permits). However, I am doubting it would help given how often LibreOffice was freezing with it (without crashing). My suspicion is that the formula recalculation is triggering more often that it should, and I could not find any way to prevent it. Excel provides options for manual triggers; if Calc does too, that might just solve the problem. PS: I am not deeply familiar with LibreOffice. :-)
- akavel 7y ago
  I suppose it could be used as a cool benchmark for trying to optimize LibreOffice
- Shorel 7y ago
  Woah. What about WPS Spreadsheets? I perceive it to be much faster than LibreOffice, and just a bit slower than MS Office.

It's in the linked readme: "While the files open in LibreOffice (tested in version 6.4.0.3 (x64)), it is slow to the level of being unusable. We have not tested in Apache OpenOffice."

whereistimbo 7y ago
In my opinion this might because of xlsx compatibility overhead. Using ods format might speed up a bit.
- dclusin 7y ago
  Microsoft has had really sharp people working on spreadsheet performance for many years. I remember reading a blog post from I believe Joel Spolsky or someone talking about what excel is doing behind the scenes to achieve high performance and I was pretty impressed. One example that comes to mind was that spreadsheets are just memory mapped files and the layout of the file on disk is identical to the data structures in memory. This allows them eschew translation to a data interchange format. So they got performance at the cost of interoperability, which is probably what's hampering open office & friends.
  - blattimwind 7y ago
    > One example that comes to mind was that spreadsheets are just memory mapped files and the layout of the file on disk is identical to the data structures in memory. This allows them eschew translation to a data interchange format. So they got performance at the cost of interoperability, which is probably what's hampering open office & friends. That improves save/restore performance, but in and of itself doesn't do much about execution performance of macros.
    - dclusin 7y ago
      It would help program runtime as well because the macro could be operating on data not loaded in memory. Not having to marshal and un-marshal data can save lots of execution time, especially in the face of non-contiguous reads/writes. Databases store information using B-Tree's for example so that they can calculate the offset of the data and jump directly to it. It would probably take a lot of gymnastics to get this from an XML or JSON file interchange format. I'm sure they could probably come up with something that is both portable and performant but it's probably not a big priority at Microsoft.
  - derefr 7y ago
    IMHO there's no reason to memory-map the interchange format itself. I would predict/expect that both LibreOffice and MS Office (with their modern XML-based formats) are actually mmap(2)ing some temp file and treating it as an "on-disk working-state heap", and then importing from interchange formats by allocating from that heap / exporting to interchange formats by chasing pointers that end up inside that heap. (This is, after all, what every RDBMS does for its working state. It's pretty optimal.) Even if you have a memory-mapped interchange format, I'd still expect them to have a separate disk-backed working heap for all the stuff that doesn't belong in the file but is nevertheless very large (e.g. cached intermediary computation results of spreadsheet cells); and, if they have it, they may as well just use it for most things by default. Thus, I would expect that even in old versions of MS Office, the in-memory data structures were actually an interchange format of sorts—not the ones being updated with each keystroke, but rather ones that'd be memcpy(2)ed into on export. (This also prevents you from either having to add a page-table structure to your file, or else constantly "defragment" it as data structures change.)
    - dclusin 7y ago
      Not sure why you were getting downvoted these are pretty reasonable comments. The database example and use of B-Trees you mentioned is a good one. It’s definitely possible to create a performant and portable document specification and others have. I just strongly suspect that the performance issue that libre office and others have is more of a manpower issue and not having equivalent resources and knowledge of the excel formats rather than some shortcoming in ms’ own file formats.
  - Someone 7y ago
    That’s certainly history, if you use a modern file format such as .xlsx, and, likely, also if you use the old format. Microsoft likely changed several in memory structures when Excel went 64-bit, if not earlier. One thing that Execl does is multi-threaded recalculation (https://docs.microsoft.com/en-us/office/client-developer/excel/multithreaded-recalculation-in-excel https://docs.microsoft.com/en-us/office/client-developer/exc...)
    - saber6 7y ago
      > Microsoft Office Excel 2007 was the first version of Excel to use multithreaded recalculation (MTR) of worksheets. You can configure Excel to use up to 1024 concurrent threads when recalculating, regardless of the number of processors or processor cores on the computer. Somewhere, there is probably someone running hundreds of threads for excel (likely in a beefy VM/VDI). It is probably wired so deep into their business that they are afraid to move to other methods (that are more scalable). But such is the power of excel. What you see is what you get is not to be underestimated.
- zweep 7y ago
  Also Microsoft makes tens of billions of dollars per year from Excel and even if only 1% of that goes back into performance, that’s a lot of optimization.
  - harry8 7y ago
    Yeah! Shame it's actually 0% :-(
    - jdsully 7y ago
      Having been paid to do that task in a prior life, I can assure you its greater than 0%.
      - harry8 7y ago
        prior life, I would believe. "was greater than" might have more accuracy, right?
        jdsully 7y ago
        Its not like I stopped all contact with friends there. They still have people working on performance.
        harry8 7y ago
        I'm not trying to be rude to you. I'll believe you that you worked there, worked on excel and maintain contact with people still htere and that people are "working on performance" and you can believe me that we just can't see the results of that work. But yes, I'm a bit jaded. I think you'll understand why that is too. The vast fortune in revenue that doesn't fix bugs put me off excel in a big, big way. That was 2008 or so. Here's Andrew Gelman in 2013 on the topic: "Microsoft has lots of top researchers so it’s hard for me to understand how Excel can remain so crappy. I mean, sure, I understand in some general way that they have a large user base, it’s hard to maintain backward compatibility, there’s feature creep, and, besides all that, lots of people have different preferences in data analysis than I do. But still, it’s such a joke. Word has problems too, but I can see how these problems arise from its desirable features. The disaster that is Excel seems like more of a mystery." https://statmodeling.stat.columbia.edu/2013/04/17/excel-bashing/ https://statmodeling.stat.columbia.edu/2013/04/17/excel-bash... We've heard from microsoft so many times they have people working on all those bugs too. I remember the sheer disappointment in testing the newly fixed rand() function after all the fanfare by filling a page with =rand(), conditionally formatting when negative to be red and seeing the page turn largely red after a couple of F9 re-calculates. I simply don't believe that excel programmers are idiots and have been for 15 years. That leaves "they say they're working on it but they are not, not really." At the top of my alternatives. Is there a better one you can suggest? Enlighten me please, how does this epic cluster of fudge happen? You were probably there while it was ongoing. Parts of excel work well, /all/ the stats should have been removed 15 years ago as unfit for purpose and zero will or ability or effectiveness in getting a fix and making good. Excel, just don't. That is a pretty reasonable response, don't you think? Yeah it's sad. I don't relish it but let's not pretend it's all ok, yeah? But I guess the masses of revenue keep coming in so I guess it is all ok from microsoft's point of view. Are you ok with that yourself?
- alok-g 7y ago
  OP co-author here. It could be worth a shot to try (I'll do when time permits). However, I am doubting it would help given how often LibreOffice was freezing with it (without crashing). My suspicion is that the formula recalculation is triggering more often that it should, and I could not find any way to prevent it. Excel provides options for manual triggers; if Calc does too, that might just solve the problem. PS: I am not deeply familiar with LibreOffice. :-)
akavel 7y ago
I suppose it could be used as a cool benchmark for trying to optimize LibreOffice
Shorel 7y ago
Woah. What about WPS Spreadsheets? I perceive it to be much faster than LibreOffice, and just a bit slower than MS Office.