Achieving Optimal Index-Match Performance

Jon Friend · 09/28/21

I'm wondering if anyone has any insight as to whether or not Index-match works any faster when the fields being used in the look-up contain numbers only, instead of text or text/number mix?

In my own anecdotal experience, sheets that use index-match on numbers-only columns do seem to open/update/save faster. But I have no way of knowing is this is true, or a coincidence, or merely my own subjective observation.

BTW, yes, I know that SmartSheet does not offer distinguished/different column types for text vs. numbers; it's all "text/number." I'm talking about structuring your data in such a way that columns involved in index-match happen to contain numbers only, and observing a performance improvement with this set-up compared to an index-match that uses columns containing both text and numbers.

Paul Newcome · 09/29/21

I personally haven't noticed a difference. There are so many variables that come into play when talking about sheet speed, you would have to set up some very identical test sheets with the only difference being text vs numbers, make sure there are no additional tabs or programs running on your computer, test multiple times to account for possible variations in internet speed and background programs, and then redo all of this multiple times throughout the day on multiple days to try to account for the possible impact of Smartsheet server speeds depending on how many other people are actively using it.

I guess in theory the numbers could potentially be a little faster because for each digit there are only 10 possibilities (9 for the first digit because a leading zero creates a text string) but for text there are literally thousands of possibilities for each digit.

Paul Newcome · 09/29/21

I personally haven't noticed a difference. There are so many variables that come into play when talking about sheet speed, you would have to set up some very identical test sheets with the only difference being text vs numbers, make sure there are no additional tabs or programs running on your computer, test multiple times to account for possible variations in internet speed and background programs, and then redo all of this multiple times throughout the day on multiple days to try to account for the possible impact of Smartsheet server speeds depending on how many other people are actively using it.

I guess in theory the numbers could potentially be a little faster because for each digit there are only 10 possibilities (9 for the first digit because a leading zero creates a text string) but for text there are literally thousands of possibilities for each digit.

Jon Friend · 09/29/21

Thanks Paul. Makes sense. I'm gonna plan to try to use the numbers going foward, see how that works. I'll reply if I find anything more.

Paul Newcome · 09/30/21

Sounds good. I am also interested to see if anyone else has any insight.

@Genevieve P. @Andrée Starå @Mike Wilday @L_123 Any thoughts on this?

L_123 · 09/30/21

Smartsheet is browser based, which means that all of the computational power comes from the browser and what your computer/server/smartsheets servers allocate to it

That being the case, I don't think you will be able to accurately test this and have a definitive answer from the consumer side with how close the speeds are between the two, and as paul mentioned, the number of variables that can contribute to the speed of the smartsheet. IMO this would have to come from a software engineer from smartsheet who knows the backend well and can directly test it directly with little to no latency.

Personally, I don't think there would be enough of a difference to justify a change in behavior, though I may be wrong. What I've found to be of higher importance to increasing speed is lowering the number of references and overall length of formulas.

Paul Newcome · 09/30/21

Thanks for the input @L_123. I have found the same about references (inbound and outbound) and length/complexity of formulas impacting sheet performance. I have also noticed that conditional formatting can play a big role in sheet speed as well.

Jon Friend · 09/30/21

hearing ya, guys: minimize aggregate inbound/outbound references, trim those formulas where we can, and chill on (or otherwise optimize the design of) the conditional formatting. All things sometimes more easily said than accomplished. I've got some killer sheets in my current solution; trimmed it as much as I can. Among the 5 sheets that constitute the solution, the most commonly used sheet poses an avg. 10-20 second save time. Obviously no one is thrilled about that, but I don't see what else I can do to optimize its design/performance. I might engage my SS rep at this point, ask them to connect me with an engineer in case they've got any other suggestions. Luckily, the users of this sheet collection are pretty cool about this: they are moving to the SS solution from a veritable rats nest of unlinked/incongruent Excel sheets.

Paul Newcome · 10/01/21

I understand that completely. Sometimes it is just unavoidable. But going from a "veritable rats nest of unlinked/incongruent Excel sheets" to Smartsheet, I am sure that even with a 30 second save time they are still spending less time updating everything. Haha

Achieving Optimal Index-Match Performance

Best Answer

Answers

Help Article Resources

Categories