Summary: | Trim function making index/match or lookup functions calculate all spreadsheet rows instead of data interval | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | Gisseh <gisseh> |
Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | NEW --- | ||
Severity: | enhancement | CC: | buzea.bogdan, erack, miguelangelrv |
Priority: | medium | ||
Version: | Inherited From OOo | ||
Hardware: | All | ||
OS: | All | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Bug Depends on: | |||
Bug Blocks: | 108827, 136524 | ||
Attachments: | example file |
Description
Gisseh
2023-05-29 21:09:41 UTC
Created attachment 187596 [details]
example file
Looks that TRIM() doesn't shortcut for reference to the whole column A:A. I think many text functions e.g., SUBSTITUTE has the same issue. Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: 20873f073ae4a9478f0a84355f779a2176bd2ec8 CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded Please change the title accordantly. (In reply to m.a.riosv from comment #2) > Looks that TRIM() doesn't shortcut for reference to the whole column A:A. > I think many text functions e.g., SUBSTITUTE has the same issue. Having TRIM() operating at least 123*128 = 15744 times is not the same as operating 123 times only. Here is what I mean: 1. In attachment 187596 [details] from comment 1, worksheet "Pivot_stock_1", cell C2: =TRIM(A2) and copy down until C124. 2. In worksheet ledger, cell B2: =INDEX($Pivot_stock_1.B:B;MATCH(A2;$Pivot_stock_1.C:C;0)) 3. In worksheet ledger, cell D2: =INDEX(Pivot_stock_1.$B$2:$B$124;MATCH(A2;Pivot_stock_1.$C$2:$C$124;0)) 4. Copy B2:E2 down until B129:E129. With that, TRIM() will be operating 123 times (only), while the INDEX() and MATCH() functions will operate the same amount of times as before. In my system, this change generates a much faster response, which might suggest that the problem might not be "Open interval vs “fixed” interval for index/match or lookup function" (as the current title/subject suggests), but rather that the TRIM() function (operating on strings) is taking most of the resources. If that is correct (which needs to be corroborated by developers), then the subject of this report (at the time I am writing this) is not adequate/relevant. The problem here is that functions like MATCH() and [HV]LOOKUP() (have to) force its range parameters into array mode, which for functions like TRIM() that do not handle cell ranges (i.e. expect a single scalar value as argument) means having to iterate over its given cell range to produce an array that will be passed to MATCH(). For a cell range like in TRIM(B:B) that is iterating over a million rows to generate a million elements array.. I agree, and have changed the title from "Open interval vs “fixed” interval for index/match or lookup function" to "Trim function making index/match or lookup functions calculate all spreadsheet rows instead of data interval" |