search

goosepirate / lox365

⚗️ Lox365: XLOOKUP for LibreOffice
GNU General Public License v3.0
82 stars 5 forks source link

XLOOKUP("E", A:A, B:B) is very slow as compared to XLOOKUP("E", A2:A4, B2:B4) #1

Closed suokunlong closed 1 year ago

suokunlong commented 1 year ago

XLOOKUP("E", A:A, B:B) is very slow as compared to XLOOKUP("E", A2:A4, B2:B4).

I think the code is iterating through each row until the MAX possible row, rather than identify the last used row, for a match.

goosepirate commented 1 year ago

Thanks for raising this issue. I can confirm that XLOOKUP is very slow when an entire column is passed, which takes about 3 seconds on my computer for the one lookup. When a whole column is selected in the spreadsheet, LibreOffice Calc passes an array of 1048576 elements to Lox365's Python script. Searching through the entire array in Python takes about 19 milliseconds per lookup on my computer. So, I think 99% of the slowness is probably due to the way LO converts or passes its internal objects to Python. I don't know at the moment how to speed this up though. If you're familiar with the inner workings of LO, any pointers would be appreciated.

goosepirate commented 1 year ago

Good news, I figured out a way to resolve this. The fix is included in v4.0.