[BUDI-7588] Slow load times & 500 errors from enrich api

[andyburgessmd]

Checklist
I have searched budibase discussions and github issues to check if my issue already exists.
NOTE: It does show but I believe this needs flagging as an issue.

Hosting

• Cloud
  • Tenant ID: mistraldata

Describe the bug
I have an application where it takes 10 seconds to load each page and its unusable. Its not doing much but developer tools shows me the requests that are taking up the time.

• A request to https://mytenant.budibase.app/api/self takes 2.95s to get a response from the server, and the contents is about me and the budibase account (billing, quotas, applications) most of which is not needed in the application
• Next is a request to https://mytenant.budibase.app/api/ta_users/myuserid/enrich and takes 4.44s to get a response from the server and contains a very similar set of information as the first
  From the total load time of 10.06s these two requests are taking 7.39s (73%) of the load time.

To Reproduce

1. Create and publish an application
2. View published application in browser and open browser developer tools and view network tab
3. Look at request timeline and view requests that are taking the most time

Expected behavior
Much faster application load time

Discussed in #11832

^{Originally posted by andyburgessmd September 20, 2023}
Can anything be done about the slow loading times?
I have an application where it takes 10 seconds to load each page and its unusable. Its not doing much but developer tools shows me the requests that are taking up the time.

• A request to https://mytenant.budibase.app/api/self takes 2.95s to get a response from the server, and the contents is about me and the budibase account (billing, quotas, applications) most of which is not needed in the application
• Next is a request to https://mytenant.budibase.app/api/ta_users/myuserid/enrich and takes 4.44s to get a response from the server and contains a very similar set of information as the first

From the total load time of 10.06s these two requests are taking 7.39s (73%) of the load time.

Am I doing something wrong? Does anyone else get this?

Hosting: CLOUD

_BUDI-7588

[andyburgessmd]

Not sure if this is related to this but other users in the company who are users of the applications are seeing a number of errors. What leads me to believe it is connected is that console is showing a 500 error from the https://mytenant.budibase.app/api/ta_users/myuserid/enrich request I have mentioned above.

And this is shown in the application.

[aptkingston]

Hey @andyburgessmd. Firstly apologies, that performance is terrible and unacceptable. Could you share how many user relationships or formula columns you have? Mostly concerning the users table, since that seems to be where most of the issues are.

The only immediate advice I can offer is self-hosting, which will improve performance massively. I appreciate that this may not be possible, but self-hosting will completely alleviate any performance issues.

[andyburgessmd]

Hi @aptkingston
There are two relationships on the users table, but one of those relationships does have a lot of links. One user is related to hundreds of records in another table.
I'll look at self-hosting but we really wanted to avoid that so we don't have to manage any infrastructure.

[andyburgessmd]

@aptkingston in another app with a lot less rows and no formula columns this is also happening.

For the second part of my report (#11855 (comment)) which my or may not be related, I have been able to replicate it by deleting a row from a relationship table in the UI, the row is deleted in that table but the user table relationship column still contains the ID of the now deleted row and the UI is now showing the 'cannot read properties of null' error that I attached.

[andyburgessmd]

@aptkingston I have also tried setting up a self hosted version but neither of the key applications that I would need to migrate will export from the cloud version.

[andyburgessmd]

@aptkingston if this could be set as higher priority that would be appreciated. It is blocking a critical piece of work for us now.

[aptkingston]

@andyburgessmd we're looking in to this. In the meantime, one thing you could try which would hopefully speed things up is removing any auto-columns (specifically the "created by" and "updated by" columns) on all tables that you don't need them. If you currently have these columns in multiple tables then it will slow down calls that touch the users table (like both /self and the user enrich call).

Give that a shot and let me know if it helps thing.

This applies to both self-host and cloud by the way, so try it in your cloud tenant.

[andyburgessmd]

@aptkingston thanks. I have stripped out all I can, not sure if it has made much difference but I think I may have found something else that did.
I made a change to the table block so that instead of using 'Current User.Test' as the data source I have used just Test and added a filter using the {{ Current User.email }} binding to match a field in the Test table. This is a lot quicker as its not requesting the enrich api endpoint. Its also got rid of the 'Cannot read properties of null' error.

[mike12345567]

Hi @andyburgessmd - I've been investigating this a bit, I was wondering if you could tell me how big the response is from self or enrich calls are?

I believe the cause of this may be related to a lot of relationships linked to your users in the application - I've been able to replicate quite slow loading scenarios with a few hundred relationships to my current user. This is an issue with the user table and one of the reasons that we introduce the new user column type, it is a lot more efficient than the user table and quite a bit more scalable for scenarios that you have a lot of rows linked to different users.

I would be interested to know if your scenario is similar, if you have a few hundred relationships to the users experiencing performance problems, as well as the size of the self/enrich calls for these users, just to get an idea of the scale of these requests.

[andyburgessmd]

@mike12345567 yes there are a lot of relationships to one table. So one user may have 900+ relationships in table A.
I'll try to re-engineer with the new user type tomorrow.
Enrich doesn't happen now that I have changed from Current User.Table to Table with a filter now but will try and duplicate tomorrow.
Self is only 5kb but still takes ~6.5sec

[andyburgessmd]

@mike12345567 any advise on a way to migrate records to use the new user type? I have 3000+ records that need to be re-associated?

[mike12345567]

Hi @andyburgessmd - I replied on another discussion of yours - we are working on a feature to help with the migration of records - but this may take a bit longer to implement.

As for your self call - is there anyway you could provide me what the response looks like? I would be interested to see exactly what is going on there, it is quite a small response, so I assume there must be a lot of different relationship columns on the user table.

[mike12345567]

Hi @andyburgessmd - I'm going to close this issue as I believe we have done everything we can about this - we've released an update which should improve the performance of some enrich calls but as for the self call we cannot improve the performance of it that much if the user has a lot of relationships to them.

Going forward our recommendation for the best performance when relating a lot of information to users is to try and use the user column where possible as this performs a lot better at larger scale. We will also be providing a migration path very soon for user relationships to user columns, which should make it easier to move to this new type.