Calc's countifs() is almost 3 times slower than its Excel counterpart, even though Excel does not benefit from OpenCL. It would be greatly appreciated if the algorithm can be tuned to extract even better performance than Excel since Excel lacks GPU acceleration.
Do you have a sample document you can attach which demonstrates this?
Hmm, I suppose you might say to use https://bugs.documentfoundation.org/attachment.cgi?id=174642 from Bug 144159.
Yes, that is correct. In that document, countifs() on 10,000 rows takes 84 seconds in Excel but 238 seconds in Calc.
[Automated Action] NeedInfo-To-Unconfirmed
Thanks for reporting this issue. It looks like a duplicate of bug 144159 *** This bug has been marked as a duplicate of bug 144159 ***
Hi Xisco, Just wanted to point out that this is a request for enhancement and not really a duplicate of 144159. I was hoping that someone could explore possibilities of further improving the performance of countifs() because Excel seems to do it faster without using GPU acceleration so maybe Calc can do it even faster.
(In reply to adnanbaloch from comment #6) > Hi Xisco, > > Just wanted to point out that this is a request for enhancement and not > really a duplicate of 144159. I was hoping that someone could explore > possibilities of further improving the performance of countifs() because > Excel seems to do it faster without using GPU acceleration so maybe Calc can > do it even faster. In that case, could you please add a document to reproduce the performance issue ?
(In reply to Michael Warner from comment #2) > Hmm, I suppose you might say to use > https://bugs.documentfoundation.org/attachment.cgi?id=174642 from Bug 144159. Nevermind, I've just seen this document
Created attachment 175389 [details] sample document
Reproduced in Version: 7.3.0.0.alpha0+ / LibreOffice Community Build ID: ac34bafb6cad056f843ff3ff0dee293bf1e18c56 CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded Steps: 1. Open attached document 2. Copy H2 3. Paste it in H3:H187282 -> I killed LibreOffice after 5 minutes
@Julien, would it be possible to have a perf graph for this issue ?
Created attachment 175430 [details] perf flamegraph Here's a Flamegraph retrieved on pc Debian x86-64 with master sources updated today + gen rendering.
Eike/Noel: the Flamegraph shows that a lot of time is in ScTable::ValidQuery See https://opengrok.libreoffice.org/xref/core/sc/source/core/data/table3.cxx?r=b60b6bfa#2979 I noticed in the loop "for (const auto& rItem : rItems)" at line 3039 that perhaps changing the order of test may help. For example, I thought about putting: 3069 else if (rParam.mbRangeLookup) 3070 { 3071 std::pair<bool,bool> aThisRes = 3072 aEval.compareByRangeLookup(aCell, nCol, nRow, rEntry, rItem); 3073 aRes.first |= aThisRes.first; 3074 aRes.second |= aThisRes.second; 3075 } 3076 3077 if (aRes.first && aRes.second) 3078 break; just after: 3041 if (rItem.meType == ScQueryEntry::ByTextColor) 3042 { 3043 std::pair<bool, bool> aThisRes 3044 = aEval.compareByTextColor(nCol, nRow, nTab, rItem); 3045 aRes.first |= aThisRes.first; 3046 aRes.second |= aThisRes.second; 3047 } 3048 else if (rItem.meType == ScQueryEntry::ByBackgroundColor) 3049 { 3050 std::pair<bool,bool> aThisRes = 3051 aEval.compareByBackgroundColor(nCol, nRow, nTab, rItem); 3052 aRes.first |= aThisRes.first; 3053 aRes.second |= aThisRes.second; 3054 } so these would be at the end: 3055 else if (aEval.isQueryByValue(rItem, nCol, nRow, aCell)) 3056 { 3057 std::pair<bool,bool> aThisRes = 3058 aEval.compareByValue(aCell, nCol, nRow, rEntry, rItem, pContext); 3059 aRes.first |= aThisRes.first; 3060 aRes.second |= aThisRes.second; 3061 } 3062 else if (aEval.isQueryByString(rEntry, rItem, nCol, nRow, aCell)) 3063 { 3064 std::pair<bool,bool> aThisRes = 3065 aEval.compareByString(aCell, nRow, rEntry, rItem, pContext); 3066 aRes.first |= aThisRes.first; 3067 aRes.second |= aThisRes.second; 3068 } So we would call "isQueryByValue" and/or "isQueryByString" only if necessary. Now, I don't know if the order of evaluation matters and could change the final result.
Forget about of course: 3077 if (aRes.first && aRes.second) 3078 break; it's not in the if/else if part.
Order of evaluation matters, as compareByValue() and compareByString() may deliver an exact match and only if they didn't then compareByRangeLookup() is to be tried; see also its comment at https://opengrok.libreoffice.org/xref/core/sc/source/core/data/table3.cxx?r=b60b6bfa#2941 Apart from that, COUNTIFS() doesn't do range lookups, only LOOKUP, MATCH and [HV]LOOKUP do.
> compareByValue() and compareByString Bah, of course I meant isQueryByValue() and isQueryByString() as it's also documented.
The daily build shows some improvement, with total calculation time for 10,000 rows reduced from 238 seconds to 203 seconds. I have noticed that in some calculations, when the string "Adapt row height" appears, a green colored progress bar is shown. But with countifs, the green progress bar does not appear. Can this be fixed? Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 459f9de8a87373c826eadab142850cc3fa578fca CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: en-AE (en_AE); UI: en-US Calc: CL
Calculation time has regressed to twice as slow since Build ID 459f9de8a87373c826eadab142850cc3fa578fca. Version: 7.3.0.0.beta1+ (x64) / LibreOffice Community Build ID: 8c137ff0e201c2d0ecd1bb567496dbed8e5eced7 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-AE (en_AE); UI: en-US Calc: CL
Created attachment 176681 [details] Flamegraph I retrieved a new Flamegraph on pc Debian x86-64 with master sources updated today (gen rendering)
Luboš: noticing your recent patches on this area, thought you might be interested in this one.
It would be nice to have some performance based unit tests to avoid such regressions in future.
Created attachment 176778 [details] Flamegraph Here's a new Flamegraph on pc Debian x86-64 with master sources updated today (7e5af164b7d293dd410710bed411e1ca64bbecf7) + gen rendering + non debug build.
Situation as of today: 372 seconds in LO 7.3.0.1 355 seconds in LO 7.4.0.0.alpha0+ 185 seconds in LO 7.3.0.0alpha0+ 28 seconds in Office 2021 LTSC v2111 14701.20226 Version: 7.3.0.1 (x64) / LibreOffice Community Build ID: 840fe2f57ae5ad80d62bfa6e25550cb10ddabd1d CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-AE (en_AE); UI: en-US Calc: CL Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 9e8c1da64fa8a520730ce0aea0f7199cd75c892f CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-AE (en_AE); UI: en-US Calc: CL
*** Bug 146546 has been marked as a duplicate of this bug. ***
I'm not sure bug 146546 is a duplicate of this; see bug 146546 comment 5
The problem I reported in bug 146546 seems to be triggered by selecting a cell that contains the triggering formula, but not when recalculating that same cell in response to changes in referenced cells. It makes LibreOffice unusable for several hours, sometimes across killing and relaunching the app
It's even weirder than that: if reopening the file doesn't hang immediately, if I click on the cell it hangs, but if I use the keyboard to navigate to the cell it does not hang; I can use the keyboard to extend the column (with copy, move, paste...), but I can't use the mouse to extend the column (with click & drag the corner)
*** Bug 144205 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/248e49d3f409d414331945ba91b3083406d59f78 no PerformQuery() with ScSortedRangeCache if not needed (tdf#144777) It will be available in 7.4.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.
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a49afabb0cf1eac8f1214bfada0d7c9bc662878e reduce size of *IFS conditions array (tdf#144777) It will be available in 7.4.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.
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/c05c826bd18eedaa7720ff0817b5c796435dbc02 search faster an array where most elements do not match (tdf#144777) It will be available in 7.4.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 144257 has been marked as a duplicate of this bug. ***
Following the steps in comment 10, it took about 15 seconds to paste 187,280 cells with COUNTIFS(), in the latest LO 7.4 alpha on Ubuntu 20.04. I don't have a GPU. Marking as VERIFIED FIXED. Version: 7.2.6.2 / LibreOffice Community Build ID: b0ec3a565991f7569a5a7f5d24fed7f52653d754 CPU threads: 8; OS: Linux 5.13; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
Following the steps in comment 10, it took about 15 seconds to paste 187,280 cells with COUNTIFS(), in the latest LO 7.4 alpha on Ubuntu 20.04. I don't have a GPU. Marking as VERIFIED FIXED. Version: 7.4.0.0.alpha1+ / LibreOffice Community Build ID: 4bd8eb13e1e2693961fdb9c19c403fde9d163de1 CPU threads: 8; OS: Linux 5.13; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
26 seconds in Office 2021 LTSC v2111 14701.20226 This time is for 10,000 rows. The daily build I downloaded is now doing it in roughly 2 seconds! I'm speechless. AMAZING WORK, GUYS! I hope in future this performance will not regress. Thank you once again! Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community Build ID: fe687d1b8f5305edfb167152a4fb19ffa20c5404 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-AE (en_AE); UI: en-US Calc: CL