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
Created attachment 168721 [details] File which LibreOffice is struggling to calculate the VLOOKUP formula Hi All, I've attached the file where Libreoffice is struggling to calculate the VLOOKUP formula. To test, in the 2nd sheet, please paste the first column of formulas across the sheet below the tickers which are on the first row. Hope this helps
confirm in Version: 7.2.0.0.alpha0+ (x64) Build ID: 8e691505d4675b878b30bd00cd2e4fb4f794f0ef CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win Locale: ru-RU (ru_RU); UI: ru-RU Calc: threaded and in 5.2.7.2
OMG, MS Excel does the same copy-paste just momently >_<
For me about a minute with O365 on the earth. There are older bugs about vlookup performance https://bugs.documentfoundation.org/buglist.cgi?quicksearch=vlookup&list_id=1247981
(In reply to Roman Kuznetsov from comment #3) > OMG, MS Excel does the same copy-paste just momently >_< and Gnumeric too!
Either the instructions in comment #1 are unclear, or I cannot reproduce with current master.
(In reply to Luboš Luňák from comment #6) > Either the instructions in comment #1 are unclear, or I cannot reproduce > with current master. 1. Open attached file in Calc 2. Select Lookup sheet 3. Select D2:B90 cell range and copy it 4. Select C2:AMJ2 cell range and press Ctrl+V => LO hangs
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ec0edb0969c23b25576f4d1b3b2ee5d3f21990ad optimize VLOOKUP by returning SharedString if possible (tdf#139444) It will be available in 7.3.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.
*** Bug 92456 has been marked as a duplicate of this bug. ***
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5e9c2677e8fcd19b289d947b94ceba52b138728b improve performance of cell equality comparisons (tdf#139444) It will be available in 7.3.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.
Tested 06:00 CAT 9Nov Version: 7.3.0.0.alpha1+ / LibreOffice Community Build ID: c7500945fc5d5bd2130a2d38be0bd4b15445cd90 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-GB (en_GB.UTF-8); UI: en-US Calc: threaded ██ 05:38:49 $ copy, LO "freezes" ██ 05:51:59 $ LO unfreezes >13 minutes During this time was able to use Version: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.1 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded Environment: Dell E5590 32G memory CPU: Topology: Quad Core model: Intel Core i5-8350U bits: 64 type: MT MCP arch: Kaby Lake rev: A L2 cache: 6144 KiB System: Kernel: 5.4.0-89-generic x86_64 bits: 64 compiler: gcc v: 9.3.0 Desktop: Cinnamon 5.0.6 wm: muffin dm: LightDM Distro: Linux Mint 20.2 Uma base: Ubuntu 20.04 focal Downloaded LO Tested: CET 10Nov Will test again after downloading the latest update (tomorrow?)
I killed the LO's process after 10 minutes Version: 7.3.0.0.alpha1+ / LibreOffice Community Build ID: ccf3d9140a24d767003c08cd0247e6ee6fd12fe0 CPU threads: 4; OS: Mac OS X 10.16; UI render: default; VCL: osx Locale: ru-RU (ru_RU.UTF-8); UI: en-US Calc: threaded
Created attachment 176168 [details] Report from macOS after LO killing
Took 6 minutes after LO update (50% improvement) 07:14:53 $ 07:20:43 $ 07:24:34 $ 07:30:51 $ Is it possible to change the formula to use a another function that might be quicker? e.g. something like https://www.linkedin.com/pulse/ten-alternative-excel-methods-vlookup-anders-liu-lindberg Version: 7.3.0.0.alpha1+ / LibreOffice Community Build ID: d4c56284813c1e38a2fb46e9a52c5350d1ba3cef CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-GB (en_GB.UTF-8); UI: en-US Calc: threaded
Set back to NEW. @Elmar you should not change the Status as this was already CONFIRMED.
Note that the document has Tools->Options->LibreOffice Calc->"Seach criteria = and <> must apply to whole cells" disabled, which means the lookup has to use slower substring matching.
Still extremely slow with LO 7.3.0.2 on Ubuntu 18.04.6:it took 5:55 minutes to paste the formulas of column B into the blank range C:AMJ Version: 7.3.0.2 / LibreOffice Community Build ID: f1c9017ac60ecca268da7b1cf147b10e244b9b21 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
https://git.libreoffice.org/core/+/122e676ce35b34c289cc4c91bb72e25398dc9e12%5E%21 and surrounding commits (and comment #16).
*** Bug 144931 has been marked as a duplicate of this bug. ***
*** Bug 133704 has been marked as a duplicate of this bug. ***