My sheet is running slow over the last few months. Never noticed it before. Any ideas how to fix? I just tried to create a P&L report and it timed out and said it exceeded maximum execution time. Seems like same issue I am having with splitter tool. Thought I would try here first before going to help desk. Thanks, Blake
Hi @Blake,
I know you wrote in to support about the performance of your sheet, but still wanted to check in here.
Did those tips I shared help?
Also, were you getting the timeout on the Tiller add-on P&L report generator or the Tiller Labs version of P&L report generator?
Thanks,
Heather
Hi @heather ,
24998 100% Balance History rows before deleting rows
-2302 9% Balance History rows after deleting rows
22696 91% Rows deleted
Above is where I stand right now. As you can imagine, my performance issues are all but gone. The P&L report was from Tiller Labs. I previously had splitter issues. Yesterday I did a 10 way split. It took about a minute, did not time out, and basically worked the way it is supposed to.
At the bottom here is my prior post on this matter and an email you sent to me in response.
I looked at the Utilities add-on that you mentioned in your email. However, after seeing all the many and numerous warnings, especially the one saying that Tiller will not offer assistance if anyone uses the add-on and runs into issues, I decided not to use it and manually cleaned up the Balance History tab myself. This was not a quick project.
In your email you say “The large amount of data in Balance History is probably slowing things down a bit…”. You maybe should have said “is surely slowing things down a lot”. At least that is the conclusion that I came to.
What I deleted fell into two buckets. Bucket 1: Multiple entries per day. I some cases, there were as many as 10-12 entries per day. I kept the last one and deleted the rest. Bucket 2: Same dollar amount continuing on for days, weeks, or even months. For example, charge your credit card for $10 on the first day of the month and no other charges for the rest of the month. Thus, you have 30 consecutive days showing $10. I kept the first day of the month and deleted all subsequent days in the month. This bucket also included many cases where the $10 was $0.
Based on my Balance History data, it appears Tiller cleaned up the duplicates starting around mid August. I find that very admirable and I truly appreciate it. Per one of my prior suggestions, this is something Tiller should be proactively telling its users. This is something that I would like to know that Tiller is doing behind the scenes. Why not toot your horn a bit? And why stop here? Why not try to clean up the rest of the Balance History tab? Maybe its not that easy; I do not know. But I would like to know that you are at least thinking about it and what are the plans for the future. Tiller might have an opportunity here to forge a stronger relationship with its clients by sharing this type of information. Communication goes a long way but it takes an investment and that investment is time. Only Tiller can make the call on this.
I believe what I did with my Balance History tab was the equivalent of a disc defrag. Everything starts with this tab and everything pulls from it. This tab is crucial. The longer users are around using Tiller, the more cluttered their tabs will become. Eventually, more users will experience system bog like I did. I would like to now how Tiller is addressing this issue. I like Tiller, no doubts there. I know it is calculation heavy. Everyone has great ideas for new templates. But what happens if the system gets to the point that it can only handle 10 tabs in a sheet and only one year’s worth of history. I do not know about other users, but I desire more. Why not 30, 40, or 50 tabs and 5 years of transaction and balance history? Tiller is a smart company so I know they are already talking about these issues. So, why not share the company vision on this and other important matters with your users?
Keep up the good work and please do advise.
Thanks,
Blake
11-14-19 Email
Hi Blake,
You have 50 accounts (not sure if they’re all connected) and a year of Balance History data where 3/4 of it is probably duplicate entries for each day. We fixed this issue recently to stop proliferating unnecessary Balance History entries when a balance didn’t change in a day. The large amount of data in Balance History is probably slowing things down a bit on top of the formula intensive sheets.
If you want a hack you could use the Tiller Utilities add-on to try and clear out some of the duplicated balance history entries. If you run it in a Tiller Money Feeds sheet it won’t work by default because it was built for Feed Bot sheets and requires the Index column in Balance History. If you add that column into Balance History and properly assign a unique index per account to all entries for that account you should be able to run the Tiller Utilities tool to Trim your Balance History and see if that helps. If the sheet is already a Feed Bot sheet (it looks like you still have some connected) then it will work fine, but would recommend running it in a copy of the sheet first to make sure it works, doesn’t corrupt your live sheet, and that it actually speeds things up.
https://community.tillerhq.com/t/tiller-labs-utilities-add-on-comprehensive-guide/80
Beyond that, if there are custom formulas running against balance history, at it’s size it’s likely just really slow on those operations combined with your other custom sheets.
Some of the Feed Bot versions of template (anything pre-Tiller Labs add-on) sheets weren’t built for performance so they’re slow in general.
Heather
I’m glad you were able to improve your sheet performance.
Here are a few quick thoughts on what you shared:
- We have not tried the new Tiller Labs P&L report against a test database quite as large as yours. I’m sorry to hear the add-on timed out. I’m not sure how much we can do as Google Sheets scripts aren’t particularly performant on large data sets, but I’ll peek at the code and see if there are any obvious optimizations.
- 10-way split?! You are a wild man.
- Even a 10-way split should not take a minute to run. We’ve got a splitter update on our radar… along with many other important projects. Personally, I’m really excited to take this on and have lots of ideas. Please be patient as we get to it…
- The Tiller Labs library is really exciting with so many tools readily-available for trialing. A challenge is that each new sheet live-calculates which can become crippling with either a) large data sets (in Transactions and/or Balance History) or b) lots of concurrently-running sheets. So, we’d love for users to try many solutions… but users will also need to purge non-essential solutions to keep their spreadsheets running responsively.
- I wrote the Tiller Utilities Balance History Trimmer a while back. I’d like to do a refresh to get it tuned up for the improvements we’ve made in the past months to our sheets & data architecture. The process you went through manually is effectively what the script does. It makes more sense to run that process in a trustworthy script.
Thanks for sharing your process.
Randy
@randy, I appreciate your reply.
- The P&L report generates fine now. Clearing out 91% of your Balance History will fix anything and everything. I have 2,400 rows in Balance History and 6,700 rows in Transactions. Regarding data set size, what is the normal range? At what size do performance issues appear?
- Maybe the split was closer to 30 seconds. If you are dealing with someone who has benefits, I am not sure how your paycheck split is not 8-10. My point is, I do not think a 10 way split is an unrealistic expectation for the splitter tool.
- Is there any way to manage and/or turn off live calculations? Lets say users do not want to purge? What are the workaround best practices? When is it time to create another sheet? Any articles in this area?
- For those who are not techies, like me, can you provide a brief layman’s explanation of scripts?
- Yes, an update to the utility trimmer would be sweet.
Your discussion helps but I am not sure my questions are answered. I have very specific questions above. I do not expect answers right now. These issues are big. They require discussions among Tiller personnel…I think. As more users make bigger investments in the Tiller platform, I think they are thinking ahead into the future, at least I am. I know there are technology and performance barriers. But how is Tiller addressing these barriers? Above you say users need to purge non-essential solutions. What if everything in the sheet is essential? I provide an analogy. We have encountered the proverbial brick wall. I don’t want the wall to stop us. Human ingenuity always always gets us to the other side. We dig a tunnel under the wall. We build a ladder and climb over the wall. We walk along the wall and after 10 miles we find it ends and then realize the wall was only so wide but it appeared to go on forever from the point at which we first encountered it. I do not want to be limited to only using a few of the very many awesome templates that I see this community creating. I guess maybe what I am looking for is Tiller’s vision. I want us to constantly be pushing the envelope. I think you now have a better understanding of where I am coming from. Thanks for listening. Blake
1 Like
Yes. Sorry. I’m afraid I lost this thread…
- I haven’t heard too many complaints about the P&L tool these days. It doesn’t touch the Balance History table but performance is affected by the size of the Transactions sheet.
- New splitter in Tiller Labs should be much faster.
- I don’t think live calcs can be turned off. I would love that feature.
- Scripts are basically like Visual Basic in Excel. They are just javascript code that call some Google APIs (like add row, change value, format cell, etc) to do things in your sheets. They can be bound scripts and stored with your local sheet or deployed as add-ons by a publisher— same code.
- This is updated… but there are still more improvements required. I’m working on some bug fixes this week.
Regarding performance, we are at the mercy of Google’s platform which frankly isn’t super performant as a web solution. Their performance is steadily improving but frankly some sheets & datasets & portfolios of templates are just too much. If they are all required and the platform is too slow to be functional, then, frankly, a more industrial solution/platform is the answer. We are definitely pushing up against some limits with some users. Most users should be able to moderate performance by managing the # of accounts, depth of data history, and number of solutions.
Thanks again for the chat today. Really good to finally meet you!
Hope this helps.
Randy