Complicated Spreadsheet Slow to Save

[Jaydor53]

I have a spreadsheet I created and developed myself using Excel. I have converted it to an ODS document and after adapting some of the formulae it is now, if anything, better than the original. There is of course a huge BUT on it's way I'm afraid, which is that it is extremely slow to load and even slower to save (generally it takes over a minute to think about it and between 5 and 10 seconds more while it actually writes to disk). Its size is only 500Kb but it does contain 32 sheets and there are vast numbers of formulae on each sheet which cross-reference to other sheets in the workbook and as other, less complicated, sheets open and save quickly I must assume this is the reason.

I would love to continue using Open Office but unless there is some way to address this problem I will, with reluctance, have to revert to Excel.

I did search to see if this problem had already been posted but did not find anything on the subject....my apologies if I have inadvertently duplicated an earlier question.

[Villeroy]

No, there is no configuration option "run faster" if that is what you are looking for.

[Jaydor53]

Ok, thanks Villeroy and no I wasn't looking for a "Go Faster" button. Being a complete newbie to Open Office (as in I installed it for the first time 5 days ago) I just thought that maybe other people had experienced the same problem and could advise me if there was anything I could adjust. Clearly either they haven't or they put up with it and it's an inherent problem with Open Office, one which will have to be addressed before I can use it for business. It's a shame really.

[RoryOF]

The slowness in OpenOffice saving files arises from it analysing the file into various parts, then saving these in a complicated archive structure. When the slowness impinges on us, we just have to put up with it. If you are interested, you can look inside any OpenOffice file using an archive manager such as 7-zip or Winzip. You may need to rename the extension from .odt/.ods to .zip; work on a copy of a file in case you damage it.

[Villeroy]

I use it for business all the time connected to a database server which handles dozends of interrelated tables with tens of thousands of records growing day by day.
You do not give us the faintest idea about your spreadsheet.

If it is about the usual madness
if(VLOOKUP(...) then (VLOOKUP(...) else VLOOKUP(...)) then I tend to recommend Excel (for ever).

[Jaydor53]

@Villeroy. I'm sure you know what you're talking about but I don't have a clue what "the usual madness" is. Like I said I'm completely new to Open Office and this forum and I'd like to take this opportunity to thank you for the warm welcome and your evident tolerance towards and desire to help those who don't know as much as you.

[Jaydor53]

Thank you Rory for taking the time to explain what is going on (unlike some). It's appreciated very much.

[RoryOF]

You should pay attention to Villeroy; he is an expert on databases and the use (and misuse, which is all too common) of spreadsheets. Without sight of your spreadsheet (ad hence knowledge of its internal structure) we can only guess if that structure may be causing extra slowness in saving; If the structure is a series of nested VLOOKUP statements, which is a common method of (mis)use, that would explain abnormal slowness.

[Jaydor53]

Thanks Rory and I'm sure you're right but there are ways to say things and I don't need a techie patronising me because I don't know as much as him/her. I don't know Basic from Cobol and I couldn't write a line of code if my life depended on it so I have no idea what his vLookup blather was all about (I assume it was code?), I just need a spreadsheet to do what I need it to do. I have no idea what he needed to know about my spreadsheet so telling me that would have been a good start. Anyway, Excel is perfectly good for my needs and saves the same spreadsheet in less than 5 seconds so obviously it's better really. Just as well as this whole experience has put me off Open Office completely as clearly each time I post a question here I'm going to get some patronising response. I just wanted to stick it to Bill but now I'm more than happy to stick with him.

I do however thank you Rory and appreciate your responses.

[JohnSUN-Pensioner]

Dear Jaydor53!
My attention was drawn to this your phrase:

Anyway, Excel is perfectly good for my needs and saves the same spreadsheet in less than 5 seconds so obviously it's better really.

Are you not seen as very different-sized files xls and ods?
The reason explained RoryOF: ODF-files (.ods in your case) is ZIP-archive.
Packaging data during saving and loading takes time.
But as a result you have a small file.
If you do not like it, there is a way to save your work without packing data. Not long ago, this technique was discussed at this forum.

[Villeroy]

Are you not seen as very different-sized files xls and ods?
The reason explained RoryOF: ODF-files (.ods in your case) is ZIP-archive.
Packaging data during saving and loading takes time.

He will notice when he saves his first xlsx. But then he will not complain.

[JohnSUN-Pensioner]

Oh, maybe I misunderstood the words:

I have converted it to an ODS document and after...

[Villeroy]

As far as I know, many Excel users still prefer the binary xls format for large files because xlsx uses the same kind of zipped XML as Calc does. Just like ods, xlsx takes some time to unzip and to build up the document model. The binary content of an xls is more or less identical to the document model that is kept in the RAM of the computer.
Apart from all this, Excel caches intermediate lookup results, so the lookups for the same search value are not repeated.
In Calc it is possible to replace the lookups with MATCH and then get the INDEX-array for a whole row if the match returned a number. This way each "foreign key" is only looked up once. Of course, this problem does not exist when you store relational data in a database. Of course all the spreadsheet lookups always return one row, even if there may be more (by mistake). Of course the spreadsheet lookups may return false positives for several other reasons (regex and other silly options)