What can be done to radically speed up LibreOffice

[dengar81]

I frequently use large files with over 200k lines and 10+ columns in spreadsheets. In what I do I often modify the data or audit cells, so I need to be able to filter, sort and run vlookup or index / match type functions.
The speed between LO and Excel is a difference between day and night. Libre cannot handle the sorting of large files and is almost guaranteed to be unresponsive for minutes on end. Vlookups of this size normally means that the application will crash.

What could potentially be done to speed up the application.

Please don’t suggest the memory settings. This approach makes no difference.

[dengar81]

The issue can be observed on my Macbook Pro 2.3 Ghz, 8MB RAM as well as my Lenovo 2.3Ghz i5, 8 GB of RAM.

[Lupp]

Of course, any speeding up will be appreciated, but …
Spreadcsheets are supposed to primarily handle numeric data. This should not need so much auditing, MATCH, LOOUP. In addition LibO Calc is accelarating vectorisable calculations for > 100 arrays via OpenCL (if the graphics system is capable of this kind of service).
Huge sets of non-numeric data needing a lot of sorting should be considered the domain of databases.

[Lupp]

I am not a dev, of course, but I suppose the fundamental design of LibO, and in specific of Calc is basically not optimised for speed. (That’s from SUN times?) And the many adaptions made for the sake of being/getting (a bit better) compatible with a relevant competitor’s software did not actually well fit into the different architecture. (Just a lot of guessing, I know.)
I would not expect Calc to achieve supersonic speed within the next few months.

[mariosv]

There is an issue (https://bugs.documentfoundation.org/show_bug.cgi?id=79892) with some functions like VLOOKUP() searching for strings when the option:

Menu/Tools/Options/LibreOffice calc/Calculate - Searc criteria = and <> must apply to whole cells.

is not enable.

[mark_t]

Avoid use of VLOOKUP in tables with large number of columns. Using MATCH and INDEX reduces the size of data read to memory to evaluate the function.

Restrict the rows of the sheet used in the VLOOKUP or MATCH functions, avoid $A:$C as table range.

If possible sort the order of the table in ascending or descending order of the key and set the correct sort order in the VLOOKUP or MATCH functions.

When using multiple target columns that are selected using VLOOKUP or MATCH from the same source table and key, use a helper column for the MATCH function and only use INDEX in the target cells.

[tap]

I don’t have an answer, but I would like to add to dengar81’s original question and hope this would spur some answers. When I use spreadsheets, mine tend to get huge and really slow. I have not used Excel since 2005 so I have no idea what the experience is there. I think it is really funny that Libre Office Calc has 1,048,576 rows and columns out to AMJ (1024 columns). If anyone ever needed all of that, I am pretty sure you would never get a result.

I am currently working on one spreadsheet that has “only” 2000 rows and out to Column DG. No Vlookups, but lots of calculations and only one graph, but that graph looks at all rows of data and 5 columns of data, with 5 power-law regressions that is using the automatic filter function, where I filter out several aspects to focus on the effect of one parameter on the data using 5 different correlations. There are no more inputs, and so there should be no active calculations going on. Just filtering data and looking at it. This can take a full minute to respond when I change a filter. Originally I had 7 different graphs looking at various parameters on the X-axis and when I wanted to make one change to one filter, I literally could eat breakfast before there was a change. Changing one input (edit) took about 1 minute to respond. I agree with dengar81’s comment about memory. I have mine set to 256 MB (the max) for LibreOffice use, and 124 MB per object, and 200 objects. Made no dent from the default values. My computer has 32 Xeon processors, 256 GB RAM, and several TB of disk space.

I have another Calc that has 10 pages and many vlookups, hlookups, and lots of calculations. One lookup table is 2500 rows long and just 12 columns wide. The main calculation sheet is just 114 rows long but goes out to column LX. No graphs. One edit takes about 30s, so I turn off automatic recalculation and do all my edits before pressing F9.

Would it be at least possible to open two or more instances of Libre Calc, each with its own processor, so that when I need to look at multiple large spreadsheets at the same time, they don’t all have to share the resources of just one processor? I am not a programmer, but that seems to be to be a fairly easy fix.

[Ndomo]

When both file are open, it will be unresponsive or mybe hang.
Try to close file data sources for your lookup before you copy down your formula.

i have 10K lines and 90 of colums for data sources. When both file are open, LO always hang and unresponsive for a minutes. But when i close the lookup data source and refresh vlookup again, it just like a magic, just a second to completed.

hope this make the problem solved.