LibreOffice Calc Auto Filter Slow after Update

English
#1

Let me explain how I work with the auto filter. The spreadsheet I’m working with has 100000 thousand rows and 98 columns. To filter the data in the spreadsheet, I apply an auto filter to all 98 columns. I then go to various columns I want to further filter and click on the auto filter icon next to the column header. In the filter window, I proceed to select what data I want displayed and not displayed. I’ve used auto filters in this way since 2022 and I haven’t had any problems until I recently updated libreoffice to the latest version. After updating, it now takes 1 to 5 seconds for the auto filter window to open after clicking the icon next to a column’s header. Is there a remedy to fix this slow down after the update?

The images below displays what I call an “auto filter icon” and an “auto filter window.” I do not know their official names so forgive my incorrect vernacular.

Auto Filter Icon png
Auto Filter Icon png1920×1040 271 KB

Auto Filter Window png
Auto Filter Window png1920×1040 248 KB

Here’s my system’s specs.
Version: 24.2.5.2 (X86_64) / LibreOffice Community
Build ID: bffef4ea93e59bebbeaf7f431bb02b1a39ee8a59
CPU threads: 16; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

#2

Some things to try for troubleshooting (one at a time):

  1. Run LibreOffice Calc in safe mode and see if it still happens.
  2. Disable any extensions, restart LibreOffice. If that fixes the issue, reenable extensions one at a time to see which one is causing the problem.
  3. Backup then create a new user profile, and see if that fixes the issue.
  4. Enable “Tools/Options/View/Force Skia software rendering”. Restart LibreOffice and test it again.
#3

I tried your suggestions and here are my results.

#1 Results: It still runs slow.
#2 Results: I tried disabling the extensions in normal mode or safe mode but the program wouldn’t allow me to disable them.
#3 Results: I backed up my old profile and created a new one and the slow down still occurs.
#4 Results: It seems “force skia software rending” is already enabled.

#4

Did you try disabling this? It’s not enabled by default so must have been enabled manually at some point.

There are some extensions bundled with LibreOffice and will have small padlock icons on the far right - you won’t be able to disable these and that’s fine. They shouldn’t affect this issue.

#5

I disabled “force skia software rendering” and nothing changed. It was still slow and other aspects of libreoffice slowed down as well.

#6

Does this delay only occur once after update, or is it every time you select a filter dropdown?

#7

It occurs every time I click the filter dropdown.

#8

It has been happening to me since the last upgrade, to 24.2.5.2.

150 columns, 65 rows.

I believe it has something to do with the feature of filtering by colour. Is it possible to disable it?
It takes like 4 seconds to open the filter window… which I do a lot.

#9

qa108614.ods (58.0 KB)
(3 columns, 1000 rows of auto-filtered data)

#10

10 rows, 200 columns. 4 seconds to open. auto-filter.

TEXAMPLE.ods (16.5 KB)

#11

The problem is in your file - the autofilter area extends to the last row of the sheet (1048576 instead of 10).
Try the corrected version.
TEXAMPLE.ods (15.1 KB)

#12

Thank you, but that file has no auto-filter. And I tried to select only 10 rows, auto-filter, and it takes up to 4 seconds to open the filter dialog.

#13

Same data, no such problem.
qa108614_2.ods (14.9 KB)

#14

Could you please check my file, not yours?
There are problems with your specific file.


The TEXAMPLE_2 sheet in your file (from message #10) contains the autofilter which area is A1:IF1048576.
Accordingly, when changing the autofilter, the program scans more than 1,000,000 cells.


Here is a fragment of the content.xml file from the .ods archive:

<table:database-range table:name="__Anonymous_Sheet_DB__0" table:target-range-address="TEXAMPLE_2.A1:TEXAMPLE_2.IF1048576" table:display-filter-buttons="true">
1 Like
#15

By the way, Excel (2016) crashes when opening the file from message #10. :slight_smile:

1 Like
#16

Problem solved.

After realizing that the auto-filter was scanning all rows, I tried several times to disable and re-enable it, limiting it to rows with data, but without success. Here’s what I did:

Cleared all contents from the already supposedly empty cells using “Clear Contents” by selecting all the empty rows.

image

It’s all working as it should.

Another problem that was nagging me was the slow and sluggish selection of cells.
That is now solved as well.

But, the main problem remains, the same file works fine with the previous versions.

Thank you for your help.

1 Like
#17

An “unnamed” database range is always created for a sheet filter. This range remains when you remove the autofilter (Ctrl+Shift+L).


When you redefined the filter range, this range was expanded to the maximum again.
This is a bug, but I don’t know how to formulate it clearly yet.


A workaround might be as follows.
Open the file from #10.

  1. Fill the cell range A12:B13 (for example) with any text, select this range and set the autofilter for this range.
  2. Delete the sheet rows 12:13.
  3. Select our previous range A1:IF10 and set the autofilter.

Please let me know if this trick helped?

#18

So, here’s the thing, deleting the sheet rows, 12:1048576 won’t solve the problem.
Selecting 12:1048576, and removing all the contents, it will work.

I’ve trying all sort of stuff for a few months, thinking that the problem was in the data, formulas, conditional formatting, etc. Well, it seems not to be.

#19

That’s weird, indeed.

There is still that nasty hidden database range which is created when you do a database operation without defining any range. Meanwhile it is hidden from the API as well.

On top of it all, there are 84 different cell formattings on that sheet, 54 of them i the used area A1:IF10.

#20

Not anymore, thanks to @erAck! :slight_smile:
See GetSheetFilterDBRange.

  msgbox GetSheetFilterDBRange(ThisComponent.Sheets(0)).ReferredCells.AbsoluteName

shows $TEXAMPLE_2.$A$1:$IF$1048576 for file from #10.

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