Do Cross-Sheet References Impact Sheet Load Time?

As my Smartsheet app has gotten more complex I often try to find ways to simplify sheets and speed things up. The new cross-sheet reference capability has me wondering if it would be faster than the existing links when loading a sheet? I don't know exactly what goes on when you load a sheet but the save icon stays dark grey for several seconds typically and you can't edit things until it dims. Does that have anything to do with rechecking links or evaluating formulas? If the latter, does it have to go back to any sheets the cross-sheet references point to to update the calculations? If so, would it take longer to be able to edit a sheet after loading it if I convert a couple thousand incoming links to formulas that use the cross-sheet references? Also, after having some problems with broken links a couple of weeks ago I'm wondering if the cross-sheet references would be more reliable?

Find more posts tagged with

Sheets

Formulas

Comments

Richard Rymill SBP

Jim

I think you are right to presume that Cross Sheet formulas will be an overhead that my make very large sheets slower, so archiving and staying as compact as possible is definitely worthwhile. My colleagues James R and Craig Williams are doing some testing and will report back on findings as they are known.

Hope that helps.

RichardR

Jim Hook

Richard,

I had a few minutes so I set up two new Smartsheets that both brought in 1000 cells of data from another Smartsheet. One sheet did it all with incoming links and the other using 1000 formulas that loaded the same data using cross-sheet references. The linked sheet was faster, taking an average of 2.8 seconds for the save icon to dim while the cross reference formulas caused the second sheet to take 4.43 seconds for the save icon to dim. The difference is 1.63 seconds for evaluating the 1000 formulas which is not too bad unless you have the maximum of 5000 incoming links used on a sheet and the 8.15 second extra would become noticeable.

All the links and formulas grabbed cell data from the same source sheet so having to pull in data from multiple sheets might make things worse. Also, I assume that internet speed might make a difference if the destination sheet is doing a lot of handshaking with the source sheets on the servers.

I suspect that simply evaluating the 1000 formulas is using significant time regardless of the cross-sheet references since I have several sheets with a lot of formulas, but relatively few incoming links and little conditional formatting, and they take several seconds to load also.

Jim

Brad Jones

Jim,

I would VERY much like to know how you are measuring your sheet load times? I have tried to find a solution that will allow me to quantitatively measure the differences made by things like links, references, etc.

Can you please share how you have done the measurements so that I, and others, can find the best way to improve our sheet speeds?

I have 10 active teams right now, and 9 of them are overseas. Given that SS does not have any servers outside of the USA, if a sheet takes 5 seconds to load over here the performance over there is dramatically worse. I need all the tweaks I can to keep my users from jumping ship.

Thanks in advance,

Brad

Jim Hook

Hi Brad,

Over the years I've figured out that there are two phases involved in the time it takes to load a Smartsheet beginning from when you click on a sheet to load it. I define the loading as complete when the save icon goes dim and you can safely begin editing. The time to when the sheet appears is determined by the sheer size of the sheet and your internet speed. After that it takes additional time for Smartsheet to recalculate all the formulas and process all the conditional formatting on a sheet. When I'm loading a very large sheet with thousands of complex formulas and a lot of conditional formatting rules I can see that my internet speed is in the 800KB/sec range from home and several MB/sec from work using a very high speed fiber connection.

I know that the Smartsheet servers automatically update all links and cross-sheet references, even on sheets that are not currently open, and updates all their resulting cell values when new data comes into the system from other users or the API. So, I setup two test Smartsheets that get 1000 cells of external data from another sheet, one using entirely links with no formulas and the second using INDEX() function formulas to bring in the same 1000 cells with data. Then, using nothing more elaborate than the stopwatch on my smartphone I measured the time it took from when I clicked on a sheet name to when the save icon dimmed after loading and averaged the time for three loadings for each sheet. Both sheets had no conditional formatting to slow things down.

I used to have Smartsheets with over 5000 incoming links and many thousands of complex IF() formulas and that pushed the limits of what could be loaded and saved after changes were made. It could take over a minute to save after a significant change and I would occasionally get timeouts. I broke those sheets up into two smaller sheets that communicated via links to simplify individual sheets and that has worked fine ever since. On my test sheets I don't know if the incremental time the INDEX() version takes to load is due to the time to recompute all 1000 equations or if cross-sheet references are reloaded when a sheet is opened while links are already there plus the recomputation time.

I do know that load time varys not only with your internet speed but significantly with your location. With very high speed internet at work in California the load time is slower than with a slightly slower internet connection in Maryland that is closer to the Smartsheet servers. I'm sure that is greatly impacting your overseas teams' load times. Back when I was having severe load/save time problems I created several large "background sheets" that would do special processing without my ever having to open them. That saved all the download and save time and would complete in three or four seconds, much less time than it would take to even load one of the background sheets. I still use several of them. By "background sheet" I mean a Smartsheet that receives a lot of input data via links, performs some complex algorithms on the data (like alphabetizing rows of data before the new SMALL() and LARGE() functions came out) and then sends the data to some other sheet for display via output links without ever having to be opened in my browser.

In summary, to minimize load time keep the number and complexity of formulas on a sheet to a minimum, break very large sheets up into two smaller sheets (especially sheets that typically run just on the Smartsheet servers) if possible and minimize the number of conditional formatting rules. The browser being used also impacts load time. Back when I was pushing the Smartsheet size limits I had to edit my largest sheets using Safari because Chrome would timeout.

That's everything I know about load time!

Jim

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…