Skip to main content
Solved

Calculation/Performance Limits Inquiry

  • March 11, 2025
  • 0 replies
  • 549 views
Forum|alt.badge.img+6

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

Best answer by MarkShnierYou

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.

Forum|alt.badge.img+15
  • DonLarson
  • Registered
  • March 11, 2025

Curtis,

I have a client where there is a master table and then all the 63 account tables are child records of that.    There are 273 Summary fields between the Master and child tables.

I am not sure how many records are in all the child tables, but the app size is 269MB.  

It runs very slowly.   Opening the application from scratch can take 30 seconds.  I would call it QB Abuse.

It is not a perfect comparison to your business case.

 

Forum|alt.badge.img+6
  • CurtisM
  • Author
  • Registered
  • March 12, 2025

Hey Mark,

A couple things that lead into this:
1. I need a snapshot of any given time for the current year, plus the prior full 3 years (36 months + current). SOX requirements.
2. How the Budget and Forecast data is generated balloons this a lot as well.
3. Lastly, these calculations on the Accounts table get pulled into the Summary Report field for reports such as: SEC Balance Sheet, YTD Budget Vs Actuals, FY Forecast Vs Budget, etc..

For Actuals I need:
- MTD xx (1 per month x 12 months x 4 years at most = 48 fields)
- YTD xx (48 fields again)
- QTD xx (48 fields again)

For Budget I need:
- MTD xx as of xx (12 per month x 12 months x 4 years = 576 fields)
- YTD xx as of xx (576 fields again)
- QTD xx as of xx (576 fields again)
- The "xx as of xx" is because we get a full year budget for every month of the year, so it will come with 12x as many values as Actuals.

For Forecast I need:
- Same as Budget, so 576 fields x 3

For AvB, AvF, and BvF I need:
- MTD comparisons
- YTD comparisons
- FY comparisons
- I haven't gotten an exact number, but it's any where from 1.5 to 2 times the fields required for the first 3 sections.

Then all of these fields need Rich Text visual adjustments. But I also need the capability to export the currency values since RT doesn't export very kindly.

In addition to all of this, comments will be associated with each Account, as well as each Summary Report Row.

Most of these things I believe is what disqualify my ability to use summary reports. Because it isn't just about displaying the data, but about interacting and snapshotting the data.

I'd love if there is another way around this, but I'm not feeling optimistic.

MarkShnierYou
Forum|alt.badge.img+24

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.

Mark Shnier. Your Quickbase Coach
    Terms & ConditionsAccessibility statement