Debug Signs & Markings Performance Issues

[johnclary]

See Investigate Knack Performance Issues - #832 and Look into performance impact of our custom JS in Signs and Markings app - #920.

Migrated to atd-data-tech #1888

[johnclary]

My suspicion is that the conditional status rules based on rolled-up COUNT and SUM formula fields are to blame.

Going to start debugging these three objects:

• work_orders_signs_asset_spec_actuals
• work_orders_signs_locations
• work_orders_signs

Plan

1. Remove all page rules; test page loading and form submits

2. Remove all conditional status rules, starting with those on asset_spec_actuals, then locations, then work orders. testing at each point

3. Repeat step 2, but removing COUNT and SUM formula fields

4. Repeat step 2, but removing text formula fields

5. Repeat step 2, but removing connection fields

Findings

1. Noticed immediately the that test app adds locations about 2x as fast as the prod app. This is true in both the 18 NOV instance and 4 DEC instance.

I wonder if the heavier usage of prod is slowing down the app. We have external services hitting the prod API (ESB inbound, esb outbound, SR due date assigner) as well as many users. Going to keep testing.

2. Removed page rules from work order details and location details. No noticeable improvement in load times or the speed with which add asset and add location forms submit. They are significantly faster than prod as noted in Step 1.

3. Removed all conditional rules from work orders. Definitely some improvement on the Add location form after removing hold rules, gis rules, and location name rules.

4. Removed all conditional rules from work order locations object. No noticeable improvement in load times or for submits.

5. Removed all formula fields from asset spect actuals. No noticeable improvement.

6. Removed count of assets from locations. Add Asset form seems to submit faster.

7. Removed all other formula fields from locations. No noticeable improvement.

8. Removed all count, sum fields from work orders. Very noticeable improvement on add locations and add assets.

[johnclary]

To summarize:

1. Noticed immediately the that test app adds locations and assets at least 2x as fast as the prod app. This is true in both the 18 NOV instance and 4 DEC instance. I wonder if the heavier usage of prod is slowing down the app. We have external services hitting the prod API (ESB inbound, esb outbound, SR due date assigner) as well as many users. Going to keep testing.

2. Removing equations which summarize related records (e.g. count and sum) resulted in noticeable improvement

3. Removing page rules (hide/show views based user role or record data) did not noticeably improve anything.

Next step: I'm going to a create a brand new copy of prod and re-test.

[johnclary]

Shared with JD and going to discuss further this week. His response:

I would love to look and see if there are any workarounds we could use with JS that wouldn’t tie to a form submission so that some of these rules could be removed and hopefully help with speed.

What is the plan you operate on currently? I have had a couple Enterprise Knack clients I worked with on small customizations but no one I worked with significantly outside of the franchise system I developed for, and realistically they ended up building a custom platform because of performance and speed issues. I think mapping out the architecture of your current Knack ecosystem (what’s feeding into Knack, what is Knack feeding out too) could be a good idea to understand the usage going on. I’d love to learn more about this because maybe there’s a solution to getting these integrations to work in a way that doesn’t blow up PROD.

I think you are absolutely correct that production is slow because of concurrent users (either actual users or other applications via the Knack API) hitting the Knack database with inserts, updates, deletes. I have noticed similar issues and I think you are absolutely right that requesting additional instance CPUs and any other hardware adjustments is a BandAid more than an actual long-term solution. I haven’t spent enough time with the Knack apps in the ATD Knack ecosystem but I think documenting all of the input/output going on could help start a discussion around strategy for integrations and architecture. If we are suspecting a micro-services model, then this will be key, because sometimes you have to pick and choose the amounts you’re going to let users read/write in different areas of the business based on business value. Read/write transactions aren’t expensive at a small level, but they do become an issue at a larger level.

I guess the one thing to keep in mind is that this would all have to be considered with a custom solution too. At my previous contract where they had a lot of cloud infrastructure and custom web applications sitting on top of that, they had a lot of traffic to their search functionality and we had to be mindful of expenses in indexing and even read/write transactions to newly designed tables that resulted from new features. It’s more frustrating in Knack because we don’t always know what field creation is triggering a new index on their end (though we can probably guess) but it’s essentially the same issues you’re going to have to consider no matter the solution.