Performance issue because of associated relationships

Help me!
1

Hi team,

Following the solution brought on the first performance issue I reported, I still have some performance issues generated by the fact that the smart fields of all associated relations are computed.

After analyzing the requests that are done when viewing my users collection, I could confirm that all the smart fields of all the collections associated with the users collection are computed.
And each one is computed 20 times (because I am displaying 20 lines per page).

Because many of them are requesting themselves information from the DB, I am getting a lot of requests: 20 times per smart field that need access to the DB.

Only for querying the smart fields of the associated collections (I mean without the main query retrieving the users data), I have counted a total of 12 * 20 = 240 SQL requests
while it would need to query only 2 * 20 = 40 requests (because only two smart fields references are needed to query the DB).
Furthermore, even some smart fields of some relations that I am not displaying in the main users list are computed, while I am not using them.

Here the details of the associations with my users collection, that I am using in the users list view:

// associations displayed in the users list view
db.users.hasOne(db.kyc);
db.users.hasOne(db.subscriptions);
db.users.hasOne(db.cards);
db.users.belongsTo(db.users, { as: ‘sponsor’, foreignKey: ‘sponsor_id’, targetKey: ‘id’ });
db.users.belongsTo(db.cod_countries_labels, { as: ‘Birth_country’, foreignKey: ‘birth_country’, targetKey: ‘country_code’ });

Columns used as Reference:

  • in kyc: smart field
  • in subscription: smart field
  • in cards: smart field
  • in cod_countries_labels: native SQL field
  • in users (for the sponsor): smart field

When displaying the users list, here is what I noticed:

  • all the 18 smart fields of the kyc collection are computed
  • the smart field if the subscriptions collection is unique and computed as needed
  • all the 5 smart fields on the cards collection are computed
  • the smart field of my addresses collection is computed (it is a hasMany between users and addresses, so I don’t know why)
  • all the 9 smart fields of my users collection are computed, while they are not all needed in the same view

This last point is also important: when displaying a list, all native fields of the list are retrieved from the DB, and all the smart fields of this list are always computed, even if only some columns are displayed.

So all these data and computations are generating lots of useless traffic, loads, memory occupation, and are really impacting Forest admin global performances.

We have some users that are using Forest admin all the day as their main working tool.
The displayed columns are optimized for their day to day needs, and they are becoming impatient.

So I am facing a real challenge to optimize Forest and I really do not know how I could do this.
I hope you will be able to dig into that issue for a whole performance improvement.

Thanks in advance for your help.

Here is my last setup:

  "meta": {
    "database_type": "postgres",
    "liana": "forest-express-sequelize",
    "liana_version": "6.3.11",
    "engine": "nodejs",
    "engine_version": "12.13.1",
    "framework": "express",
    "framework_version": "^4.17.1",
    "orm_version": "4.44.0"
  }
3 Likes
2

Hi @Louis-Marie,

You request makes a lot of sense.

I think it is technically possible to compute only the necessary Smart Fields.
I’ll try to prioritise the overall Smart Fields performance optimisation (for hidden columns and reference fields of associations).

Thanks for this great feedback, it has a lot of value for the team :pray:

3

Hi @arnaud

Thank you for your message.
We are attaching a lot of importance to Forest admin because it is our main back office tool.

We could not work without it, and I will be happy to assist if you need any other information.

Thanks again!

1 Like
4

Hi @arnaud

I just wanted to let you know that we are having more and more performance problems as our customer database grows.

Would you be able to give us some news on the availability of this improvement. It’s starting to become critical for us.

Thanks in advance for your help.
Best.

5

Hey @Louis-Marie,

Could you please share a video reproducing your issue with the network tab open and explicitely show the columns requested that should not be requested ?

6

Hi @vince

I am sorry, but I’m afraid you did not catch the problem.

The requests sent to our back-end are not the issue.
As illustrated below, only two requests are sent, one for the data, and one for the counting rows.
image

As explained in the ticket, the issue is coming from the fact that each user data line displayed is requesting all the smart fields of all the related associations, while they are not needed anywhere in the main list.

It is then generating a lot of useless SQL requests for all smart fields that are computed using another SQL request.

Please let me know if you need more information.

Thanks.

7

Okey so I tried to reproduce your issue. I have an issue when displaying a record detail but not in the listing. Are you sure that you have an issue in the listing ?
If you do could you please share the query parameters sent to your server ?

8

Hi @vince ,

Yes I am sure to have the issue in the listing.

I am in a local environement, and the only one working on my locale db, and I can confirm that there is a lot of useless requests for all the smart fields of the linked collections.

I have multiple pages, and I have 20 lines per page.
I can see 20 times the same requests for all of them.

Here is the query sent to the server:

http://127.0.0.1:3002/forest/users?fields%5BBirth_country%5D=name_en&fields%5Bcard%5D=CHID_and_status&fields%5Bkyc%5D=Onboarding&fields%5Bsponsor%5D=fullname&fields%5Bsubscription%5D=Subscription&fields%5Busers%5D=created_at%2Cemail%2Cfirstname%2Clastname%2Ccard%2CCardOrdered_Date%2Ckyc%2Ccompliance_score%2Cfraud_suspicion_level%2Cnb_accounts_same_device%2Cstatus%2Clanguage%2Clast_balance%2Csubscription%2Cdefault_currency%2Cphone_number%2Ccurrent_address%2Csponsor%2CnbSponsored%2CCurrentCountry%2Ccity%2CBirth_country%2CCardActivated_Date%2Cbirthdate&filters=%7B%22aggregator%22%3A%22and%22%2C%22conditions%22%3A%5B%7B%22field%22%3A%22email%22%2C%22operator%22%3A%22not_contains%22%2C%22value%22%3A%22jbtest%22%7D%2C%7B%22field%22%3A%22deleted_at%22%2C%22operator%22%3A%22blank%22%2C%22value%22%3Anull%7D%5D%7D&page%5Bnumber%5D=1&page%5Bsize%5D=20&searchExtended=0&sort=-created_at&timezone=Europe%2FParis

Here is the same as a screen copy

image
image887×528 19.8 KB

Thanks.

9

Could you click on view decoded please it will be more readable :sweat_smile:

10

Here is the decoded URL:

http://127.0.0.1:3002/forest/users?fields[Birth_country]=name_en&fields[card]=CHID_and_status&fields[kyc]=Onboarding&fields[sponsor]=fullname&fields[subscription]=Subscription&fields[users]=created_at,email,firstname,lastname,card,CardOrdered_Date,kyc,compliance_score,fraud_suspicion_level,nb_accounts_same_device,status,language,last_balance,subscription,default_currency,phone_number,current_address,sponsor,nbSponsored,CurrentCountry,city,Birth_country,CardActivated_Date,birthdate&filters={"aggregator":"and","conditions":[{"field":"email","operator":"not_contains","value":"jbtest"},{"field":"deleted_at","operator":"blank","value":null}]}&page[number]=1&page[size]=20&searchExtended=0&sort=-created_at&timezone=Europe/Paris

Thanks

11

Sorry if I was not clear enough I was speaking about the Query String Parameters not the url :sweat_smile:

12

But the query string parameters are in the URL indeed… did I miss something?

Nevermind, here is

image
image879×573 19 KB

Thanks

13

Hi @Louis-Marie,

I’m sorry that you’re facing this issue, it’s such a pain!

However, I’ve tried to reproduce your problem but with no success:

  • If I have a smart field in my collection and it is hidden in my collection list, the smart field is not computed
  • If my collection A has a hasOne relationship with a collection B, and this collection B has smart fields, none of the collection B smart fields are computed when I’m displaying the collection A unless one of them is the reference (and in that case, only the reference smart field is computed).

Am I correct on what I’ve been checking to try to reproduce your issue?
Still, we absolutely need to reproduce it on our side.

Did you override the route of the users collection? If yes, could you please show us the get('/users'?
Or did you override the route of the related collections (kyc, subscriptions…)?

Thanks.

14

Hi @anon34731316

Thank you for your efforts.

I will need to check all of this in details then.
Give me some time to dig into it, and I will come back to you.

Thanks again.

1 Like
15

Hi @anon34731316,

Damned!

You were right, this was due to the overridden route router.get('/users'...
I am so sorry for that mistake.
But it is due to a specificity that I coud not guess on the use of recordsGetter.serialize function.

I opened a new issue (please see performance issue #1010) to avoid mixing subjects.
Please see it, as it is generating the issue that I described here.

Now, with the standard routes, I agree with you: the useless smart fields of associated collections are computed when accessing the records detail.

Thanks for fixing it as soon as you can.

Best.