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 220.127.116.11, unsorted takes more then 4 minutes.)
One might decide that it is good to sort the lookup range.
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.
Created attachment 139639 [details]
test file for VLOOKUP performance unsorted
found in Version: 18.104.22.168.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
Created attachment 139640 [details]
callgrind dump attached - stopped the process at roughly 1/5 of the full lookup
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.
Michael Meeks committed a patch related to this issue.
It has been pushed to "master":
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:
Affected users are encouraged to test the fix and report feedback.
With a series of patches this comes down from 40seconds to 4.5seconds or thereabouts on Dennis' machine - lets mark this fixed for now =)
works even faster now thanks to Luboš recent commit:
(though difference between ~4 and ~3 seconds for the test file in this issue, is not that noticeable)