Speed issue in huge files

Context : I use LO Calc with huge files, often larger than 10 MB. One is 43 MB, some are over 20 MB. Usually with many files at the same time and for a long time, i.e. I can open some files for some days, save some and re-open them, etc. Windows 7.

I switched from 3.4 to 4.4 and found that almost every thing was now very slow after some editing.

  • Sorting inside a huge file even for 10 lines is 10 ten slower. But in a newly read file, sort on all lines can be very fast.
  • Saving a CSV file (I see no reason for that since there is no formatting at all). The progression line stops after a short time, like if Calc was looking for lines after the last one. I suppose this would be faster if the last line was computed BEFORE saving, this even if the file was modified.
  • Searching a string by column. I suspect Calc to scan the 1000000 lines even if only 60,000 are part of the file. I would suspect this would be faster if the last line was computed BEFORE saving. But fast with a newly read file.

Temporary solution : using 4.3 (3.4 likely no more available). However, it seems this version has already the problem of not limiting search and CSV save to defined cells. Reducing files’ size, trying to be under or near 20 MB or under 45,000 lines. Thinking to use OpenOffice instead…

Final solution : I decided to uninstall 4.3 and to get back to 3.4. In my opinion, 4.4 and 4.3 are definitively not made to edit many huge files at the same tile. Waiting 1 minute when looking for an not-existing string is a very bad issue. The only good improvements I see over 3.4 were the new open menu, a faster reading of huge files (but saving a huge CSV file is a nightmare) and reading a file not taking the focus of windows. If at least someone could indicate some limit to avoid Calc working with 1 M-lines actions instead of 65 k-lines. I reduced my files but don’t know what would be a good target.

What you can try first is resetting the user profile. https://wiki.documentfoundation.org/UserProfile

There is a bug that makes calc very slow with search functions using regular expressions like VLOOKUP(), when the option Menu/Tools/Options/LIbreOffice calc/Calculate - ‘Search creteria = and <> must apply to whole cells’ is not marked and the next option ‘Enable regular expressions in formulas’ is marked.


Not the case here, i.e. no regular expression. Looking for a name in a list of say 40,000 lines (searching “John” for example, not even “John.+”), and there is no change if whole cell or not.

Hi DenisB :slight_smile: I was facing the same challenge with intermittent slow spreadsheets. I found an easy fix. Which might work for your issue. Steps:

  1. Simply close that slow spreadsheet. No need to close other spreadsheets if any are already open.
  2. Re-open that spreadsheet. Try to edit it again. It might be faster now.

It’s unclear what cause that issue, somehow it seems to be cause after doing extensive edits to a large spreadsheet. Often with edits related to adding, editing, moving, removing columns or rows.

How many a memory in your PC? Try to use 64-bit OS and 64-bit LibreOffice last stable version 5.3.3