search

microsoft / advanced-formula-environment

Create, edit, and reuse formulas in Excel
https://aka.ms/get-afe
MIT License
109 stars 11 forks source link

AFE editor and Excel becomes very slow with a medium amount of LAMBDAs in tables #69

Open gl-walker opened 8 months ago

gl-walker commented 8 months ago

I've found that there's a certain size or complexity of Excel file with lambas created in the AFE where it becomes almost unusable. I have one file that takes 45+ minutes to even open the AFE, and the current one I'm working on takes ~2 minutes to open the AFE, or insert or delete a sheet (a totally blank sheet even). Inserting or deleting columns takes a lot longer than normal as well, 30 seconds to a minute.

This is making it not so useful for me since any applications I'm using it for will end up with files of this complexity. Previously, I had built similar Excel files, but with quite large in-cell formulas instead of using LAMBDAs. These did not have the same slowdown issues that I'm experiencing now.

For example, the current file I'm working on has 4 tables which use LAMBDAs I've created. Each table is ~64 rows and 60-70 columns. Each column has a consistent formula. None of the LAMBDAs are recursive, and I've tried to avoid directly referencing table data in them, everything is passed in as an argument (similar to any coding where avoiding global variables is always recommended).

jack-williams commented 8 months ago

Thank you for the report. A couple of follow-up questions:

gl-walker commented 8 months ago

This is on desktop.

Opening the taskpane is slow, but just for the first time I open with the file open. Once it's open, I can close it and reopen it without issue.

I don't believe I have used any volatile functions. I have not used any of the following: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO, CELL, SUMIF

The slowness of adding or deleting a sheet doesn't seem to be affected by having the taskpane open or closed.

My suspicion (based only on my knowledge of Excel, not any particular evidence) is that it has something to do with the dependency tree, but that's just speculation on my part.

On Thu, Oct 26, 2023, 2:58 a.m. Jack Williams @.***> wrote:

Thank you for the report. A couple of follow-up questions:

  • Is this on web or desktop?
  • It is opening the taskpane that is slow (rather than adding a sheet with the taskpane closed).
  • Do the lambda's contain volatile functions (like RAND() or NOW())

— Reply to this email directly, view it on GitHub https://github.com/microsoft/advanced-formula-environment/issues/69#issuecomment-1780697308, or unsubscribe https://github.com/notifications/unsubscribe-auth/ANCIFULSB2NHKUG6VGSVUQLYBIQZPAVCNFSM6AAAAAA6QFKZ2WVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBQGY4TOMZQHA . You are receiving this because you authored the thread.Message ID: @.***>