Calculation/Performance Limits Inquiry
Hi all,
I'll try to keep this as brief as possible. I'd like to know if anyone has a similar sized application in production and if they see any performance issues as it pertains to record loading and report loading.
I have an Accounts table (table A) that will have 1,500 records total at any given time. This table has 3 children tables B, C, and D with 72,000; 168,000; and 168,000 total records at any given time, respectively. These children records will all have an Account# to tie it to their parent.
On table A, I need to keep up to 4 years worth of data (Current Year, Prior Year, etc.). As a result, I will need about 1,500 to 1,750 currency summary fields per year (6,000 to 7,500 in total). This number then gets doubled because Quickbase does not have a built-in way to display numbers in Accounting format, so I need an additional 6,000 to 7,500 rich text formula fields.
At no point in table A should I ever need a report with more than 12 columns of calculated summary fields for all 1,500 Account records. This is the max performance need. On the forms, I plan to "only" have 6,000 to 7,500 of the 12,000 to 15,000 total fields displayed. This will be broken across 4 different tabs (hoping that improves performance) for each year of data. Then each tab will be broken into 6 sections, which can be pre-minimized if it will help load times & performance (Actuals, Budget, Forecast, Actuals VS Budget, Actuals, VS Forecast, Budget VS Forecast).
This table A is my biggest concern.
If you care for additional context:
In addition to this, table A will be a child to table E (Summary Report Rows). There will be 12/13 total Summary Reports (table F; parent to table E), made up of about 168 table E records. To restate, the 168 records will be parents via 3 relationships to the Accounts (table A). There is 1 relationship per key/vital column in the Accounts table. These will be running calculations based on the summary field calculations in table A.
I'll leave it at this for the time being to see if anyone is able to take the time to respond.
At the end of the day, I've never built something with this many calculations and I'm not entirely sure on the order of operations for Quickbase when it comes to forms.
Regards,
Curtis
Another approach would be to have a looping callable pipeline, which would call itself. The pipeline would set a focus year or perhaps even a focus month and then you would have a much more limited set of summary fields which would calculate totals for that focus year or focus month. Then these would be written to a static history table.
That would allow you to run your reports off the history table, and it would save you having to make so many different summary fields.
