Solved: slowness problem with tens of thousands of transactions

Hi everyone, a few days ago I asked the question Is “Filling updates” speed decreasing with the number of transactions?. There seems to be a simple, easy solution to this problem.

  • Make a copy of your “Transactions” sheet, called e.g. “Transactions.old”.
  • Remove older transactions from “Transactions” and newer transactions from “Transactions.old” in such a way that the union of the rows in the two sheets is all transactions without duplicates. For me, I kept all transaction through 2023-12-31 (73K rows) in “Transactions.old” and all 2024 transactions in “Transactions”.
  • Add a new sheet called e.g. “Transactions.all” and in cell A1 insert this formula:
    ={ Transactions!A1.Q ; Transactions.old!A1.Q }
    This will show all rows of “Transactions” followed by all rows of “Transactions.old”.
  • If you have any analysis that relies on the “Transactions” tab contents, change all instances of “Transactions” with “Transactions.old”. Usually there are only a handful of changes to make (I use “Category Tracker”, “Categories by Month”, and “Spending Trends”, each of each is easy to adjust).

Now you have a slim and fast “Transactions” tab, a place to see all of your transactions going back years, and analyses that work correctly. Hope this helps.


Interesting. So, you haven’t reduced the data being processed by reports and Fill speed is noticeably improved?

It makes me wonder if Fill would be faster inserting the data at the top of the sheet, instead the current append to bottom approach :thinking:

At least I got from “Every fill timed out for two week” to being able to fill things properly, albeit not quickly. I’m still talking minutes.

Actually you can experiment with that. There’s a setting called “Auto Sort Override” that has new insertions occur at the bottom.

Forgot to mention, reports are quite speedy so no problem there.

1 Like

Nice to have an improvement :slight_smile:

New transactions always get added to the bottom, that option is just for sorting afterwards, which will either leave them at the bottom, or sort them to the top.

Auto Sort Override
When new transactions are added to your Transactions sheet by the Tiller Money Feeds add-on they are added to the bottom and then sorted to the top automatically. This override setting, when it’s turned on, will leave these transactions at the bottom. This is a great feature for folks who want to use Tiller with another service like Zapier and/or Airtable where the tool expects the new rows to be added to the bottom and stay at the bottom.

If you have any analysis that relies on the “Transactions” tab contents, change all instances of “Transactions” with “Transactions.old”.

Shouldn’t this be “change all instances of ‘Transactions’ with ‘Transactions.all’”? Or is there something I misunderstand?

Yes, “Transactions.all”, sorry for the confusion.

1 Like

I’m not sure whether inserting at the top or bottom is faster, but one thing I noticed is adding a computed column to the Transactions tab can make fills dramatically slower. I’ve added a column that computes for each row the average dollars spent per day per category up until that day (quadratic scaling) and it makes fills time out even if there aren’t very many rows.

1 Like

Ah, okay, interesting. I wondered if there might be some calculation needed to determine the bottom transaction row before appending and how fast that might be with tens of thousands of transactions :thinking:

I wonder if it’s really the overall spreadsheet calculation load that matters and not that it occurs specifically on the Transaction sheet? Like do that calculation on a “TransactionAll” sheet that will change with a fill and remove/disable it on the Transaction sheet.

From what I can tell it’s a combined effect. Calculations on other sheets that depend on “Transactions” also impact update speed, but for some reason calculations done on the “Transactions” sheet itself have a greater impact. But… I speculate.

Andrei, did you solve this via policy based class design or D slices?

1 Like