Super STFC users home page does not load

[TCMeldrum]

A user of ISIS has 361 proposals the next highest is about 250. Thier homepage does not load and the other super users homepage was quite slow. We should paginate the the proposals users query. https://github.com/UserOfficeProject/user-office-core/blob/develop/apps/frontend/src/graphql/user/getUserProposals.graphql

[zacharyjhankin]

We found the same problem. Though I tracked it down to one specific SQL call. (apps/backend/src/datasources/postgres/FapDataSource.ts getRelatedUsersOnFap first query). This one for some reason took us 10 seconds to execute in our staging environment. I have this commit that fixes the speed issue. Though I am a bit concerned to raise a PR to ensure that behaviour is kept the exact same. I have a theory there may be a bug in the old one. I also want to add that our database had 0-5 rows in all relevant tables so the fact that it was so slow to execute is still a mystery to me. I know that the query could explode with more rows.

The actual API call that is slow "getUserProposals" is called twice from the front end from what I could see. Which are done in parallel so that could be fixed to one query.

Also this entire SQL query can be skipped in the getUserProposals api call because it lets you get PI from the proposerID but no PI is returned about the proposer. Just their ID. So we could just return the proposerID and skip the auth check

[TCMeldrum]

Im not quite sure if this is the same issue, I think getRelatedUsersOnFap to me looks like it only run on the getAll end point when getting all users which doesn't happen on the homepage for a user. The above issue is only for the fact a user has 350 proposals on there homepage and it doesn't load.

Just to check getUserProposals being called twice, I only see it being called once in our production environment. I think graphql calls every query twice when in local environment as React.StrictMode is active is that why it is being called twice?

Also this entire SQL query can be skipped in the getUserProposals api call because it lets you get PI from the proposerID but no PI is returned about the proposer. Just their ID. So we could just return the proposerID and skip the auth check

Sorry can you explain what you mean here? I can't see getRelatedUsersOnFap this auth check happening on auth check of getUserProposals

[zacharyjhankin]

My understanding here is that the getUserProposals.graphql will call this function:

@FieldResolver(() => BasicUserDetails, { nullable: true })
  async proposer(
    @Root() proposal: Proposal,
    @Ctx() context: ResolverContext
  ): Promise<BasicUserDetails | null> {
    return await context.queries.user.getBasic(
      context.user,
      proposal.proposerId
    );
  }

Because the

   proposer {
        id
      }

is inside it.

[William-Edwards-STFC]

I thought it used the proposals resolver on the user type because it uses the following in graphql

me { proposals {...} }

[zacharyjhankin]

It does use the proposals resolver, but it has nested queries inside that query. desc

So status, proposer and call are all nested queries which will call their own resolvers

[TCMeldrum]

Ah my bad I see it now my bad! That db query seems very odd, we can optimise it as part of this issue then

[zacharyjhankin]

So do you think that we are having the same issue? I pointed my local dev at the environment with problems and used a timing statement on the slow SQL to narrow it down to that exact place

[zacharyjhankin]

And the thing that I am concerened about is rewriting this function because it seems to me like there is a bug in it.

".leftJoin('fap_reviewers as r', function () {"

The purpose of this line is to filter all of the faps as s down to only ones that are associated with our own users ID. the one that is passed into getRelatedUsersOnFap. This is then expaned into all fap_reviewers by .join('fap_reviewers as fr', { 'fr.fap_id': 's.fap_id' });.

But they use a leftJoin which keeps all of the old ones that didn't match, so it ends up returning everyone!

My re-write is great. But it changes the outcome I think. So its a bit worrying

[TCMeldrum]

So do you think that we are having the same issue? I pointed my local dev at the environment with problems and used a timing statement on the slow SQL to narrow it down to that exact place

It could be that query slowing down the query but I think for us it is because the page tries to load and send 380 proposals on that page. We haven’t seen issues with people with smaller numbers of proposals.

Yeah that looks like a bug in the sql running the sql against our db returns a bunch of users not on there FAP, I would be happy adding your commit.