[Libreoffice-bugs] [Bug 139444] New: VLOOKUP function very slow on Large Tables in Calc

[bugzilla-daemon at bugs.documentfoundation.org]

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

            Bug ID: 139444
           Summary: VLOOKUP function very slow on Large Tables in Calc
           Product: LibreOffice
           Version: 6.4.7.2 release
          Hardware: All
                OS: Linux (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: rabayahbaland at gmail.com

Description:
Hello all,

Hope you are all doing well

I would like to file a bug, or rather some performance issue with Libreoffice
Calc which happens on both 6.4.7 and 7.01.1. I have a rather large table which
I am using a VLOOKUP formula to find data, which the VLOOKUP formula also has a
CONCATENATE formula as well. With the formula, when pasting across 1024 columns
and 90 rows, Libreoffice simply does not calculate the data and hangs up. 

I've tested this on three machines, one Windows and two using KDE Neon. The
windows Machine used LibreOffice 7.01.01, while the KDE Neon machines used
6.4.7. I tested the same sheet (in ODS format) on Excel where Excel was able to
calculate the formulas near instantaneously, and I also tested on gnumeric,
where gnumeric took 5 to 8 seconds to calculate the full sheet.

I also tried this in safe mode to ensure it was not a user profile issue. Other
further notes which I should mention:

 I've attempted to use openCL to try and speed up the calculations.
 I also ran libreoffice on terminal to see if it was throwing any error
messages, none appeared.
 File size is approximately 15MB

I am a big fan of LibreOffice and was somewhat surprised by the performance
issue stated above, as usually Calc is much quicker than Excel! It would be
great if we can get to the bottom of this to see what may be the issue,
unfortunately I do not have any strong form of Software diagnostic skills, and
realistically can only contribute by filing bugs and financially supporting the
project.

Steps to Reproduce:
1.open a large file which has a table of data that requires to use a
CONCATENATE formula to combine two columns of data (Date and Stock ticker).

2. Use a VLOOKUP function in another sheet with stock tickers across in
columns, and dates in rows where the VLOOKUP formula will have a CONCATENATE
formula as well (example =VLOOKUP(CONCATENATE(date,stock ticker),table
range,false) to search up the CONCATENATED variable in the table in another
sheet

3. Copy the formula from one column towards the last possible column.

Actual Results:
Results are that LibreOffice freeezes and does not calculate the results of the
VLOOKUP.

Expected Results:
Expected Libreoffice to take at most, 1 minute to calculate given the large
amount of data and slight complexity of the formula.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Software should be quick at calculating the VLOOKUP formula, as other software
was able to do it fine. It may be by chance due to specific hardware I'm using,
but I prefer to file this as a bug/performance issue

-- 
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/20210106/36bc4978/attachment.htm>