Bug 115490

Summary:	VLOOKUP unsorted is (too) slow in larger row sets, filling all cells in the column with the formula
Product:	LibreOffice	Reporter:	Cor Nouws <cno>
Component:	Calc	Assignee:	Dennis Francis <dennisfrancis.in>
Status:	RESOLVED FIXED
Severity:	normal	CC:	aron.budea, cno, telesto
Priority:	medium	Keywords:	perf
Version:	6.1.0.0.alpha0+
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=79892
Whiteboard:	target:6.1.0
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	109329
Attachments:	test file for VLOOKUP performance unsorted callgrind output

Description Cor Nouws 2018-02-06 15:24:52 UTC

I'll attach a test file.

Take a lookup in 10.000 columns
Unsorted takes ~40 secs on my laptop. Sorted is done in < 1 sec.
(for reference, in 4.0.6.2, unsorted takes more then 4 minutes.)
One might decide that it is good to sort the lookup range.
Indeed.
However… if a value that is seached for, is not available, then there will still be a result returned (see Help on VLOOKUP, 4th argument, (un)sorted)
To prevent this, one could first check if a matching value is available, by use of VLOOKP unsorted  ..
So no joy in that approach.

Comment 1 Cor Nouws 2018-02-06 15:25:57 UTC

Created attachment 139639 [details]
test file for VLOOKUP performance unsorted

Comment 2 Cor Nouws 2018-02-06 15:27:13 UTC

found in Version: 6.1.0.0.alpha0+
Build ID: c7f74bbab4c666a8b3b865dbd58b3666f1f63052
CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-02-03_00:22:13
Locale: nl-NL (nl_NL.UTF-8); Calc: group

Comment 3 Cor Nouws 2018-02-06 15:32:36 UTC

Created attachment 139640 [details]
callgrind output

callgrind dump attached - stopped the process at roughly 1/5 of the full lookup

Comment 4 Cor Nouws 2018-02-06 15:36:14 UTC

  !
  !
  !
Apologies, I should have mentioned in the description that the time is needed to full the full 10,000 rows in column E with the formula.
  !
  !

Comment 5 Commit Notification 2018-04-06 13:58:58 UTC

Michael Meeks committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=76bc1a81d089d9f66639c118c4063d87e4422684

tdf#115490 - avoid transliteration by using SharedString.

It will be available in 6.1.0.

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

Affected users are encouraged to test the fix and report feedback.

Comment 6 Michael Meeks 2018-04-10 20:31:57 UTC

With a series of patches this comes down from 40seconds to 4.5seconds or thereabouts on Dennis' machine - lets mark this fixed for now =)

Thanks !

Comment 7 Cor Nouws 2018-10-12 11:31:52 UTC

works even faster now thanks to Luboš recent commit:
https://cgit.freedesktop.org/libreoffice/core/commit/?id=79449d73900d7a9bf061244d76f5f8eecc441198

(though difference between ~4 and ~3 seconds for the test file in this issue, is not that noticeable)

Comment 8 astrel 2019-08-10 04:13:09 UTC

Please see the bug below, it seems to me that the problem started after your revision, in 6.0 this problem was not.
https://bugs.documentfoundation.org/show_bug.cgi?id=124513

Comment 9 Aron Budea 2019-08-10 13:09:43 UTC

(In reply to astrel from comment #8)
> Please see the bug below, it seems to me that the problem started after your
> revision, in 6.0 this problem was not.
> https://bugs.documentfoundation.org/show_bug.cgi?id=124513
Bug 124513 has been traced back to a different commit, if you suspect it's because of a different change, please open a new bug report (but only in that case, use bug 124513 otherwise). Let's keep this closed unless the original issue reported here persists.