Bug 92456 - Vertical lookup freeze with large row
Summary: Vertical lookup freeze with large row
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.2.0
Keywords: perf
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2015-06-30 12:48 UTC by Luigi
Modified: 2021-02-06 12:18 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
vertical lookup performance (1.15 MB, application/vnd.oasis.opendocument.spreadsheet)
2015-06-30 12:48 UTC, Luigi
Details
FlameGraph (197.50 KB, image/svg+xml)
2021-01-06 20:31 UTC, Roman Kuznetsov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Luigi 2015-06-30 12:48:05 UTC
Created attachment 116951 [details]
vertical lookup performance

Hi 
I have the file in attach
I do a vertical look up for all line ~23000
but calc non respond and doesn't give me a result

I've Ubuntu 15.04  and libre office 4.4.4.3

thanks in advance
regards
Comment 1 tommy27 2015-06-30 19:28:08 UTC
please explain step by step what do you exactly mean "do a vertical look up"
Comment 2 Luigi 2015-06-30 20:47:33 UTC
ok
copy cell i2
past from i3 to i23285

cell i2 contain vlookup

when you past calc don't returns results and go in standby

thanks in advance
Comment 3 tommy27 2015-07-01 04:08:27 UTC
freeze is reproducible under Win8.1x64 using LibO 4.4.3.2 and 4.2.0.4 
I see the same issue with AOO 4.1.0 so the bug is most likely inherited from OOo

status NEW. I add Calc expert to CC list.
Comment 4 Luigi 2015-07-01 06:36:40 UTC
Thanks
Comment 5 QA Administrators 2016-09-20 10:14:36 UTC Comment hidden (obsolete)
Comment 6 Xavier Van Wijmeersch 2017-07-22 16:20:37 UTC
Version: 6.0.0.0.alpha0+
Build ID: 8becd40f030b94fe3fb6ad82b048ee97daaea2b1
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

Did test with attachment
loading the file take's a while, copy/past about 5 a 10min
and have results; only 4 #N/A
Comment 7 QA Administrators 2018-07-23 02:32:07 UTC Comment hidden (obsolete)
Comment 8 Attila Szűcs 2020-12-03 13:48:18 UTC
It is probably a performance issue.
This copy (23000+ rows) finished in 13 minutes on a i7-4790.

Version: 7.2.0.0.alpha0+ (x64)
Build ID: 498c6b9f4ee53677381d10d41df0abc9782689c4
CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL
Comment 9 Roman Kuznetsov 2021-01-06 19:31:03 UTC
I think a bug 139444 is about the same problem
Comment 10 Roman Kuznetsov 2021-01-06 20:31:10 UTC
Created attachment 168726 [details]
FlameGraph
Comment 11 Roman Kuznetsov 2021-01-06 20:35:13 UTC
Noel, could you please look at it as a performance problem guru. There is a FlameGraph in attach here. Thank you.
Comment 12 Commit Notification 2021-01-25 16:55:50 UTC
Noel Grandin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/aa98ed61a7b1e50bcc4f64ceaea3bb0cda360bb4

tdf#92456 improve VLOOKUP perf

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2021-01-26 11:33:54 UTC
Noel Grandin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/7383b518af160d89cba19429980141c4d400387f

tdf#92456 dynamic_cast to static_cast

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2021-01-27 06:39:28 UTC
Noel Grandin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b2ebceeaf49aa45979486997e6358697b7045361

tdf#92456 store hash with string

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Noel Grandin 2021-01-27 08:47:10 UTC
I have made some small improvements.

But this is essentially a pathological test-case - every single one of those VLOOKUPs is going to return a different result, which means our query-result-cache is never hit, which means that such a copy-paste is just going to take a really really long time, and there is not much we can do about it.
Comment 16 Roman Kuznetsov 2021-01-27 14:58:29 UTC
(In reply to Noel Grandin from comment #15)
> I have made some small improvements.
> 
> But this is essentially a pathological test-case - every single one of those
> VLOOKUPs is going to return a different result, which means our
> query-result-cache is never hit, which means that such a copy-paste is just
> going to take a really really long time, and there is not much we can do
> about it.

Noel, thank you for your work here. So, will you plan to do anything else here? Or it require to full rework for that place?
Comment 17 Roman Kuznetsov 2021-02-03 13:13:06 UTC
~ 2m 30 sec now on Core i3-3120m (2 core, 4 thread, 2.5GHz) in

Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 6ab1feb2604d2620435a2aecbafff1d8c21255e9
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded