Issue 126951

Summary: Subtotal function fails to respond to filter settings
Product: Calc Reporter: randomwalker <ascholberg>
Component: programmingAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Normal    
Priority: P5 (lowest) CC: orcmid
Version: 4.1.2   
Target Milestone: ---   
Hardware: PC   
OS: Windows 10   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description randomwalker 2016-05-03 18:25:59 UTC
On a large 56MB spreadsheet with a data set of 247506 rows, the subtotal function fails to respond to standard filter settings. The function result remains unchanged after activating the filter, for all the subtotal functions.

The problem disappears after reducing the size of the data set. It works fine on a data set of some 50'000 rows.

I have had many crashes loading and modifying the large spreadsheet when making apparently minor changes.
Comment 1 randomwalker 2016-05-03 18:51:19 UTC
Note: on the large spreadsheet, crashes occur with a "bad allocation" message.
Comment 2 orcmid 2016-05-03 19:53:21 UTC
(In reply to randomwalker from comment #1)
> Note: on the large spreadsheet, crashes occur with a "bad allocation"
> message.

With regard to "bad allocation" messages.

This suggests that you are running into memory-access limitations in OpenOffice Calc.

Although Calc was able to build a sheet with such a large number of rows, the complexity of the sheet is apparently overwhelming with regard to the memory allocations required to use the sheet.  A seemingly-small change may exhaust the working memory available to Calc.

One limiting factor is that OpenOffice on Windows is an x86 application, so the limitation on available memory is fixed at a value less than what a Windows x64 system might be able to provide.  

Try closing other applications and only having OpenOffice Calc open with the one document to see if that makes any difference.

You can also try increasing the Windows swap space (Virtual Memory), although that might not be a factor.  Only do that if you are comfortable working with the Control Panel > System and Security > System (link) > Advanced system settings (link on sidebar) > Advanced tab > Performance (Settings... button) > Advanced tab > Virtual memory.  The amount used on all drives is probably generous already (e.g., nearly 5000 MB or more).
Comment 3 orcmid 2016-05-03 20:02:58 UTC
(In reply to randomwalker from comment #0)
> On a large 56MB spreadsheet with a data set of 247506 rows, the subtotal
> function fails to respond to standard filter settings. The function result
> remains unchanged after activating the filter, for all the subtotal
> functions.
> 
> The problem disappears after reducing the size of the data set. It works
> fine on a data set of some 50'000 rows.

This might be related to the problem of memory exhaustion, where some functions fail silently in order to not crash and the function does not operate properly.

In that case, it might be time to consider using a personal database of some kind.  Are you familiar with the OpenOffice Base application?
Comment 4 randomwalker 2016-05-06 07:40:16 UTC
Thank you for your help. 

Memory exhaustion is a probable reason. I have had many crashes with this spreadsheet, with an ominous "bad allocation" message.

The data I'm working on is provided by a legacy Delphi program, so I could include the filters in that program, or indeed, try the OO Base. Either option requires significant more work.

As it is, I have opted for the quick and dirty way out, which is breaking the file in two. Not exactly clean, but it's doing the job.
Comment 5 randomwalker 2016-05-06 07:50:27 UTC
(In reply to orcmid from comment #3)
> (In reply to randomwalker from comment #0)
> > On a large 56MB spreadsheet with a data set of 247506 rows, the subtotal
> > function fails to respond to standard filter settings. The function result
> > remains unchanged after activating the filter, for all the subtotal
> > functions.
> > 
> > The problem disappears after reducing the size of the data set. It works
> > fine on a data set of some 50'000 rows.
> 
> This might be related to the problem of memory exhaustion, where some
> functions fail silently in order to not crash and the function does not
> operate properly.
> 
> In that case, it might be time to consider using a personal database of some
> kind.  Are you familiar with the OpenOffice Base application?

Thank you.(In reply to orcmid from comment #3)
> (In reply to randomwalker from comment #0)
> > On a large 56MB spreadsheet with a data set of 247506 rows, the subtotal
> > function fails to respond to standard filter settings. The function result
> > remains unchanged after activating the filter, for all the subtotal
> > functions.
> > 
> > The problem disappears after reducing the size of the data set. It works
> > fine on a data set of some 50'000 rows.
> 
> This might be related to the problem of memory exhaustion, where some
> functions fail silently in order to not crash and the function does not
> operate properly.
> 
> In that case, it might be time to consider using a personal database of some
> kind.  Are you familiar with the OpenOffice Base application?

(In reply to randomwalker from comment #4)
> Thank you for your help. 
> 
> Memory exhaustion is a probable reason. I have had many crashes with this
> spreadsheet, with an ominous "bad allocation" message.
> 
> The data I'm working on is provided by a legacy Delphi program, so I could
> include the filters in that program, or indeed, try the OO Base. Either
> option requires significant more work.
> 
> As it is, I have opted for the quick and dirty way out, which is breaking
> the file in two. Not exactly clean, but it's doing the job.
Comment 6 randomwalker 2016-05-06 07:50:44 UTC
(In reply to orcmid from comment #3)
> (In reply to randomwalker from comment #0)
> > On a large 56MB spreadsheet with a data set of 247506 rows, the subtotal
> > function fails to respond to standard filter settings. The function result
> > remains unchanged after activating the filter, for all the subtotal
> > functions.
> > 
> > The problem disappears after reducing the size of the data set. It works
> > fine on a data set of some 50'000 rows.
> 
> This might be related to the problem of memory exhaustion, where some
> functions fail silently in order to not crash and the function does not
> operate properly.
> 
> In that case, it might be time to consider using a personal database of some
> kind.  Are you familiar with the OpenOffice Base application?
Comment 7 randomwalker 2016-05-06 08:03:56 UTC
(In reply to orcmid from comment #2)
> (In reply to randomwalker from comment #1)
> > Note: on the large spreadsheet, crashes occur with a "bad allocation"
> > message.
> 
> With regard to "bad allocation" messages.
> 
> This suggests that you are running into memory-access limitations in
> OpenOffice Calc.
> 
> Although Calc was able to build a sheet with such a large number of rows,
> the complexity of the sheet is apparently overwhelming with regard to the
> memory allocations required to use the sheet.  A seemingly-small change may
> exhaust the working memory available to Calc.
> 
> One limiting factor is that OpenOffice on Windows is an x86 application, so
> the limitation on available memory is fixed at a value less than what a
> Windows x64 system might be able to provide.  
> 
> Try closing other applications and only having OpenOffice Calc open with the
> one document to see if that makes any difference.
> 
> You can also try increasing the Windows swap space (Virtual Memory),
> although that might not be a factor.  Only do that if you are comfortable
> working with the Control Panel > System and Security > System (link) >
> Advanced system settings (link on sidebar) > Advanced tab > Performance
> (Settings... button) > Advanced tab > Virtual memory.  The amount used on
> all drives is probably generous already (e.g., nearly 5000 MB or more).

Thanks.

Increasing virtual memory could perhaps do the job, but the response time is already pretty bad, so I am reluctant to give it a try.

My work-around has been to break the spreadsheet in two. Not clean, but it works, although it requires some extra processing. What "surprises" me is that the OO spreadsheet allows some 1'048'576 rows, while I was using only a little over 250'000.
Comment 8 orcmid 2016-05-08 18:02:06 UTC
(In reply to randomwalker from comment #7)
> [ ... ] What "surprises" me is
> that the OO spreadsheet allows some 1'048'576 rows, while I was using only a
> little over 250'000.

Yes, expanding the capacity of an index field and having the potential for that many rows is not the same as assurance that a sheet of any complexity can be built out that large.  Unfortunately, they are not the same thing and I wonder if anyone ever created a sheet with that many rows.

I suspect one problem is that the OpenDocument Format (used for .ods) does not support sparse arrays very well.  That is, if you put something at A1048575, the file actually has an entry for each preceding row, even if all cells are empty.  I wonder how one can test that easily.