Why is INDEX slow?

Rob Hagan

Hi,

We have been using SmartSheet for many years and have developed our entire project management around it. Every production sheet is derived from the same template. Thus, every production sheet has the same columns and many of those columns have formulas. These sheets now use summary fields, some of which derive their values from the sheet fields. Each formula refers to either columns in the same row (using @row) or entire other columns (or both).

All of this has been working well to date.

Recently, we attempted to expand our implementation with extra columns to obtain information from the row that is nominated as the predecessor row. It is here that we have run into performance issues when opening, saving and copy/pasting rows in our test sheets.

The two new columns are:

[Predecessor Row] with a formula that extracts the row number from the first predecessor in [Predecessors] (e.g. 215 if [Predecessors] is "215FS-3d,245SS") and returns 0 if [Predecessors] is null.

[Predecessor %Complete] with the formula =IF([Predecessor Row]@row = 0, "", INDEX([%Complete]:[%Complete], [Predecessor Row]@row)).

We then use [Predecessor %Complete] in the calculation that informs a staff member (on his or her task report) that a task is ready to be worked on (because its predecessor is complete) or is pending (because its predecessor is not yet complete).

After a deal of investigation, we have narrowed our speed issue down to INDEX.

We have already attempted reducing the INDEX's range down to being from the first row down to the current row (on the assumption that all predecessor references point to an earlier row). This didn't change the speed.

So, is there a way to take a (dynamically obtained) row number and use it to obtain the value from a given cell on that nominated row. If INDEX is the only mechanism, then why is it so slow and how can it be sped up?

Thanks,

Rob.

Ben Goldblatt

Hi Rob,

Narrowing down the INDEX range can sometimes help as it can take a significant amount of time to process information in an entire column, but you may also want to try testing in a different browser to see if this makes a difference. I've had some success using Firefox when working on sheets that have a wide-spread use of advanced functionality, such as formulas, conditional formatting, and cell linking.

I can see that you've submitted a Support ticket for the performance issues you're running into and they will be your best resource in troubleshooting this.

Ben

Rob Hagan

Hi Ben,

Most appreciated.

As background, all of our production sheets are produced from just one template so they all contain the same columns and formulas. All fields references are either to the same row, the first row, the summary area or an entire column. There are no references of the form @row+3 or @row-2 that I believe aren't supported anyway.

Do you know of any evidence on the performance testing of INDEX?

We have already experimented with [column]$1:[column]@rows as the range but this imposes an operation constraint that a Predecessor must be a backward reference. This is not an acceptable position.

We already only invoke the INDEX in situations where it is required (sounds obvious I suppose), so only on rows that have a [Row Type] ofTasks or Milestones and not on Deliverables, Requests or Comments.

I take it from your comment "it can take a significant amount of time to process information in an entire column" that the underlying data structure isn't optimised for rapid indexed access to an entire column. We would like to use INDEX more as a significant piece of improvement for us would use it about four more times (above the current single use of INDEX).

Very slow turnaround on tickets at the moment (from my perspective).

Rob.

Rob Hagan

Hi again Ben,

Here is the response from Support which is really a non-answer (basically "turn it off and on again" when you consider our history of use and quite sophisticated development using the product). I've already done a lot of optimisation on the formulas that we use to squeeze what I can from them. Do you have a way of getting a more satisfactory response on the question of performance?

Hope so, thx Rob

Genevieve P.

Hi @Rob Hagan,

I’ve removed the email that was cut and pasted here, since in most circumstances Support emails are private conversations that are specific to your individual scenario, based on plan & issue type, whereas the Community is a public space to share solutions and discuss best practices with other users.

That said, in reviewing the email you received, I understand that it did not address your specific issue. Based on this, we had escalated your case and since then I see that you have been working with a Support agent and have scheduled time for a call to go over your questions in a more detailed manner. 

If a Support email either doesn’t properly address your request or you still require help, please do respond to the email to continue the conversation. This will re-open the ticket and let our team know that your issue was not resolved or that the initial response was incorrect.

In the meantime, if you have any other questions that are not related to troubleshooting issues, let us know and we'd be happy to help!

Thanks,

Genevieve

Rob Hagan

I have now had engagement from a 2nd Level Support Technician which was very productive in terms of the opportunity to communicate the essence of the issue(s). Thus, I believe that it is now well understood. Hopeful of some traction soon... Thanks SmartSheet.