Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Performance issues with LOOKUP() formula

George Thomas Irimben
George Thomas Irimben ✭✭
edited 12/09/19 in Archived 2017 Posts

Hi,

I have a Smartsheet with around 3600 rows and ~20 columns. After introducing a simple LOOKUP() into one column of these 3600 entries, the Smartsheet page has become dead slow. Browser is giving error "An application is slowing down ...". Tried opening this sheet in both Firefox and Chrome browsers but same result.

Formula is like this:

=LOOKUP([Serial No]1, [RefTableColA]:[RefTableColB], 2, false)

How to resolve this issue? Any suggestions?

 

Thanks

-George

· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hi George—

    It appears you're treating the span of multiple columns (which contain 3600 cells each) as your lookup table. This is not a simple LOOKUP function when it has to iterate over 7200 cells to find the value each time the sheet is opened. 

    Smartsheet is ultimately a browser based application, and as such is limited to browser speeds. Even when using the latest version of Chrome (which works best with Smartsheet), you'll likely have slowdown with this type of function.

    To solve this, reduce the lookup table you're referencing such that it doesn't span the entirety of multiple columns.

    · Share on FacebookShare on Twitter
  • George Thomas Irimben
    George Thomas Irimben ✭✭

    Thanks!

    I understand huge number of cells for lookup is causing the issue. But, I feel this slowdown should happen only at the loading time or if any edits completed in relevant cells.

    Thoughts?

    -George

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 05/23/17

    Since you're referencing the range of multiple columns, this slowdown can occur anytime you add a single row, as when you do that, you're expanding the range of the column—which causes the formula to update its understanding of how many cells to look at. Slowdown can also occur, as you put, on initial load and when cells in those columns are edited.

    There are many changes that might trigger the formula engine to think, "I need to recalculate something or store new information."

    · Share on FacebookShare on Twitter
  • George Thomas Irimben
    George Thomas Irimben ✭✭

    I see the slowdown issue even with arrow key presses.. This makes the sheet unusable even for viewing the content.

    Unless there is a *value change* in the sheet, I don't think lookup engine need to recalculate.  Pls comment.

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 05/24/17

    George—

    As you stated, your sheet got slow when you put the LOOKUP function in it, that tells me that the LOOKUP function, or the argument selection (your lookup table) is the cause of the issue.

    You need to remove the LOOKUP function from your sheet altogether in order to troubleshoot the slowness.

    If you remove the function and your sheet stops being slow, it's referencing too much data and the range needs to be reduced.

    If you remove the function, save your sheet, and it's still slow, then contact our Support team for further troubleshooting.

    · Share on FacebookShare on Twitter
  • George Thomas Irimben
    George Thomas Irimben ✭✭

    I understand size of lookup table(~tried with 2000 rows) is causing this slowdown issue.

    My question is why the sheet has to be slow(after loading), when I am just moving the cursor around using arrow keys.

    May be, it is a limitation in Smartsheet.. Will contact Support team..

    Thanks for help.

     

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    Please do—there could be an unknown factor that I'm not able to see or troubleshoot for that the Support team can check out.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories