Persistent cache of large lists for re-use sheetwide?

English
#1

Hi All,

I’m not sure how to describe this.

Is anyone here aware of a way in LO Calc to load and retain a cache’d list of values (an array/table held purely in memory, instead of on-sheet) that can be accessed over and over again on various sheets?

In other words, trigger a “pre-load” of a certain table of numbers upon document open, and then point formulas and macro code at that list in memory, for speed and efficiency gains.

Thanks in advance for any help you can offer.

Regards.

CT

#2

You don’t explain the problem, only your envisioned solution.

Normally, having the “lists” on a sheet, and referencing them from other places, should work reasonably well. If you have a specific scenario (a document) where you see a bottleneck, the best is to file a perf bug report with a sample document attached - that would hopefully enable a generic fix.

#3

In my opinion, for Calc (as well as for Excel) you need to try to minimize the number of calls to application methods.
For example, if you plan to use the values of the cells of a range many times, it is better to call the getDataArray method and then use the returned array.

#4

Hi Mike,

Thanks for the reply.

It’s not so much a “bug” like problem, as much as my inexperience at knowing the best programming solution to what appears to me to be a speed bottleneck when searching very long lists.

I just wondered if there is a way to “pre-load” a list of values that need to be referenced repeatedly for efficiency purposes.

Cheers.

CT

#5

Hi @sokol92,

Thanks very much for the reply.

For example, if you plan to use the values of the cells of a range many times, it is better to call the getDataArray method and then use the returned array.

That sounds like the sort of thing I’m looking for.

Does the DataArray persist in memory? And how would I update it if the list changes?

Cheers.

CT

#6

Heh, programming solutions are implemented programmatically; users are not expected to do things that the program must do for them.

E.g., you file a bug with a problem. We find out that we should have a cache for that. And we implement that “best programming solution” as an automatic thing, from which everyone benefits.

But are you talking about macros / scripting / programming an extension for LibreOffice?

#7

Hey Mike,

I’m using Chat GPT to help program some macros (which is currently above my paygrade) to replace some formulas I created that use multiple MINIFS and MATCH functions which are currently bottlenecking my speed when searching very large lists. It may be I can’t avoid the bottlenecks, but I’m experimenting to find out one way or the other.

We’'ve hit a snag where we’ve figured out how to load an array of numbers into memory, but can’t figure out how to maintain the array in memory for re-use across other sheets.

I’m fully prepared to write this experiment off due to lack of experience, but I figured if it was possible to do this, here is the place to ask.

CT

#8

Recalculation of formulas in Calc is implemented effectively. If possible, upload test example, on which we can try to rewrite the formulas to a more effective version.

#9

Hey @sokol92,

Thanks very much for the reply.

Do you mean post the whole workbook document, or just paste the formula in here for you to take a look at?

CT

#10

In the experiment, we should see slow work of formulas.

  1. You upload a file, specify which cell (cells) we should change. After the change, we see a delay in recalculating formulas.
    An alternative is a long recalculation of formulas when opening the file.
  2. We think about what needs to be done to reduce the delay.
#11

Why??
If you calculate or load something when the document is opened, and you want to make the found values persistent, using a macro, e.g. for later comparisons with “state at start”, in most cases the proper way should be to create a dedicated helper sheet for the purpose with pure data, and to create a named range with the proper scope. The helper sheet may be cleared then during the closing process like Save As, but references to it weill then be lost,. and the resulting state will be stored.
What are your reasons to not go this way?

#12

Hey @Lupp,

Thanks for the reply.

Because I already have a sheet with the main bulk data on it. It’s already tidy and using all named ranges, but when needing to search these lists using formulas on other sheets and macros in various places, the search time “from sheet” appears to be a huge bottleneck.

Loading the data once into a “cache’d in memory” version, allows me to point various macros at this array, and the speedup is noticeable. The problem I’ve found is that I don’t know how to maintain that cache’d version indefinitely. It seems that certain actions (I don’t know enough yet to know which specific actions) clear the cache, and that defeats the object of wanting to scan the data in once and use it over and over again, if that’s even possible, which was why I came here and asked my original question.

I’m sure I’m approaching this all wrong, but that’s why I love coming here, coz you guys have so much more knowledge and experience than me, and I always come away FAR better off in my learning journey.

Sorry for my newbness.

Cheers.

CT

#13

Hey @sokol92,

Thanks for the offer to take a look at my spreadsheet. I will bundle it up and post here shortly. I just have to run some errands for a short bit.

CT

#14

Please paste here the information on Menu/Help/About LibreOffice (There is an icon to copy)

#15

Funny expression.

This doesn’t sound sound.
If your “data" actually are data there isn’t anything to do. Referencing data from a SheetCellRange Is directly supported by the core of the software. If you suspect that the current implementation is wasting time, report a bug and attach a convincing example as already @mikekaganski told.
If you factually have a bunch of formula results the idea of caching only makes sense if it’s about avoiding unneeded recalculations or making the results "non-volatile`”.
You can get this by copying the data or output range and “Paste Special …” it to a range elsewhere disabling the formula option. To the original range for which you don’t want further automatic recalculations you can then store the literal formulas to preserve them for future calculations.
I had some fun with sketching macros for an example. It is extremely simplified, however, and in the given version only applicable if the range to get cached is filled with one array formula.
Have fun with implementing mor flexibility and error catching.

See attached demo.
recalcOnDemandExperimentB.ods (37.5 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.