[Libreoffice-bugs] [Bug 140365] New: VLOOKUP slow on accessing search matrix in other files

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=140365

            Bug ID: 140365
           Summary: VLOOKUP slow on accessing search matrix in other files
           Product: LibreOffice
           Version: 7.0.1.2 release
          Hardware: All
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: enhancement
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: joerg.meichtry at bringhen.ch

Description:
VLOOKUP slow on accessing matrix table in other files

We use Libreoffice here in our company on about 300 machines. First of all: A
big thanks to the community and the developers.

Some users have reported to us internally that the CALC is very slow in the
function VLOOKUP. We have found out internally that VLOOKUP reacts fast within
the spreadsheets, but not when VLOOKUP is executed over files.

Example:
Case 1:
1500 rows, VLOOKUP in 4 columns over 900 rows.
VLOOKUP is executed within File 1 and only accesses another spreadsheet.
Duration ~1 second

Case 2:
File 1: 1500 lines, VLOOKUP in 4 columns over 900 lines
VLOOKUP is executed over 2 files. Search matrix is located in File 2.
File 2: corresponds exactly to the 2nd spreadsheet from File 1.
Duration ~30 seconds

We assume that with VLOOKUP a file hook is created for each row.
Destination File - open-call -> VLOOKUP -> Destination File close-call
In the above Case 2, this would thus generate 3600 hooks. This could lead to
this delay.

Is it possible for you to optimize the VLOOKUP function for cross-file
searches? Maybe only 1 file hook and close the hook first after finishing the
whole calculation.

Best thanks to you all of you.

Steps to Reproduce:
1. See description

Actual Results:
Case 1: Duration ~1 second
Case 2: Duration ~30 seconds

Expected Results:
Case 2: Duration enhancement nearer to case 1


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Can be reproduced in other Versions too.
Release 6.4.7.2
Release 6.4.3.2

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210212/8aa6ad80/attachment.htm>