Force Calc to Use 100% CPU

[3VA41570]

Hello all,
I am delaing with a spreadsheet that handles 10000 values from a logging for 6 channels.
This means I am ussing 60000 cells.
There is a library written to make the FFT of this values.
It takes a lot of time for calc but I have an Intel 5820 and 32Gb of RAM.
The system has a lot of free power not used by Calc.
How can I force Calc to perform a better use of the mem and CPU %? At this point is using when calculating only 1 CPU (I have 8 cores).
Thank you!

[John_Ha]

Welcome to the forum.

There is nothing you can do to "tune" AOO. The Calc software, or perhaps just the function you are using, may be written to use only one CPU.

Use Google to find a (free) FFT application - it will be much faster. For example, see FFTW - it is a subroutine but there is a Python wrapper for it. Command line FFT runs it from the command line.

If this solves the problem, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[keme]

Spreadsheets typically contain heavily interdependent elements. A dependency line must be resolved in the correct order. Cyclical dependencies must be reliably handled. Rewriting/-configuring the software to manage this safely using parallel processing threads is not a trivial task, but probably requires a rewrite of the calculation engine from scratch, more or less. I would guess that the kind of programming resource required (a small team of programmers skilled in developing multithreaded solutions, dedicated to this task for a significant period of time) is currently not available to the OpenOffice project. Sometimes money matters ...

[3VA41570]

Hello,
Thank you both for your comments. I agree wth you that multpiple threaths would require special programing, but...why at least is not using 100% of capacity of one single core?
Regards.

[John_Ha]

Probably because the CPU is having to wait for data to arrive.

[jrkrideau]

I second John-Ha's suggestion. A spreadsheet is a blunt instrument for something like this.

[JamesR]

Edit: My apologies for unintentional thread hijacking (see later comments). So that other readers are not confused, I am making this "SOLVED", but not because any problem in the software has been solved, but rather because the consensus is "Don't do that with Calc."

I know this, and related, issues have been discussed for years, but it seems they have still not been resolved. It would really be nice to be able to handle larger data sets in Calc.

In my case, I have 100,000 rows of data. Just 1 column! I am not trying to do any calculations at all, just plot the data. It freezes Calc, with 100% of 1 Hyperthreaded core being used. I have 12 physical cores = 24 HyperThread cores, so Calc is only using about 4% of the available processor (on an operation that is presumably easily done in parallel, unlike some situations with dependencies).

I have tried all the suggested fixes to make Calc plot faster. I've greatly increased the video memory allocations, tried turning OpenGL (and OpenCL) on, turned anti-aliasing off... nothing helps. Calc is essentially useless for a data set with this many rows, yet Excel handles the same data with ease on a machine that is MUCH less powerful.

What makes this extremely frustrating is that I am using Calc on a fairly monstrous machine (see sig block). But, none of that power helps.

I've read a lot of threads about this topic, and when other people report this problem, I keep seeing comments like "Well that's a large spreadsheet", or "Calc isn't designed for large data sets." But, this complaint with Calc has existed for years and years. And the definition of a "large" data set changes over time. And, other programs, including Excel, handle such data without any problem. So, instead of saying "Calc wasn't designed for that," it would be great if someone could figure out the reason for it being so slow and fix it. Then it WOULD be designed for that and it would be a much more useful program! I know that's easier said than done, but Calc chokes on files that Excel could handle 10 years ago. Something is wrong.

James

[RusselB]

@JamesR : OpenOffice is a 32 bit program, not 64 bit, which, to the best of my knowledge is the realistic (though not literal) minimum bit code to be able to use multiple cores.
I see that your signature indicates that you are using LibreOffice, rather than OpenOffice, but the basis may the same.

Calc chokes on files that Excel could handle 10 years ago. Something is wrong.

It's easy to say something is wrong, it's harder to identify just what the problem actually is, and harder still to come up with a solution,
The developer's are always looking for help with the development of these projects, but most (if not all) of them don't visit these forums... so the best way to get this to their attention is to use the developer's mailing list.... and if you have the skills to identify the problem area and/or suggest a potential solution, it would make the job of the developer's (who are doing this voluntarily) that much easier.

[JamesR]

Believe, I know it's not easy to identify and fix such problems, that's why I prefaced that statement with "I know that's easier said than done..." People that work on open source projects of this complexity have my thanks and admiration.

I suspect that if the programmers can't identify the issues, my meager programming skills would not be of much use, but I'd be happy to profile the code and see if I can find anything. I'll look up the mailing list you mention. Thank you for the information!

[John_Ha]

What makes this extremely frustrating is that I am using Calc on a fairly monstrous machine (see sig block). But, none of that power helps.

Correct. Using a spreadsheet to calculate an FFT is like using a hammer to drive a screw - it's not a very good match.

Spreadsheets are designed for calculating "financial balance sheet" type calculations and are therefore very inefficient computational processors because they are optimised for simple input and easy to read layout. The data is very badly organised for computation.

There are typically four things which limit a computer's performance: CPU power (number of instructions per second), I/O latency and bandwidth, available memory and memory bandwidth. If any one of these reaches its limit none of the others will do so. CPU utilisation is a very blunt measure of whether the PC is running at its limit. As I said above, a CPU running at 30% is spending 70% of its time waiting for the data it needs to calculate to arrive or to be written.

You will be far better to use a dedicated FFT program where ease of input is sacrificed for speed and efficiency of computation. A program with good data management should achieve a higher CPU utilisation but never 100%.

[John_Ha]

It really annoys me when I waste my time responding to a cross post hijacked thread.

Edit: Edited to correct cross post > hijacked thread

In my case, I have 100,000 rows of data. Just 1 column! I am not trying to do any calculations at all, just plot the data. It freezes Calc, with 100% of 1 Hyperthreaded core being used.

Something is wrong.

Your approach to your problem is wrong. Calc is not designed to plot 100,000 data points in a graph and it will not work efficiently if you ask it to do so. Reduce your data before plotting and plot the reduced data.

[JamesR]

Cross post? This is the only place I posted.

Anyway, "reduce your data" is certainly one solution. Another is perhaps to realize that since a monitor doesn't have anywhere near 100,000 pixels across, only a tiny subset of the data can be plotted anyway and come up with an efficient algorithm to decide what to plot. I don't know if that's the difference between Calc and, e.g., Excel, but Excel can do in a couple seconds what Calc can't do in 5+ minutes (or ever).

[John_Ha]

Cross post? This is the only place I posted.

Sorry - you are correct as it is not a cross post. I meant hijack a thread. See Survival Guide for the forum which says:

One issue, one thread
...
Don't hijack the thread of other users: if you're not absolutely sure that the issue is the same, don't post in an existing thread. The problems may be different as would be the solutions. If you want to reference another topic to tell what you've tried for example, add a link to it ...

The solution for your problem is completely different to the solution to the Original Poster's problem.

Excel can do in a couple seconds what Calc can't do in 5+ minutes (or ever).

Excel has an army of no doubt well paid developers and programmers and users pay for the program. AOO and LO are created by a few unpaid volunteers and the product is free.

Try Googling graph utility thousands data points - you will quickly find sites like Welcome to Bokeh which says:

Bokeh is an interactive visualization library that targets modern web browsers for presentation. Its goal is to provide elegant, concise construction of versatile graphics, and to extend this capability with high-performance interactivity over very large or streaming datasets.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[Villeroy]

Put it in a database from where you can query aggregated subsets of your data within a second or two.
Then you may decide to use a Base report with a chart or link a spreadsheet range to the query and plot from the linked range.