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