Optimize DB queries where many-to-many relationships are used

[anderspeders]

I am wondering where the performance issue is located. Or maybe I was just unlucky...

[anderspeders]

Adding Nigeria country page to this bug report:

[KseniyaKuprina]

I need access to the prod to do proper debugging. Please share it.

[iprunache]

@KseniyaKuprina how can we help with access to prod? What kind of access do you need?

[mattfullerton]

There are a lot of lines like this in the controllers:
https://github.com/NRGI/ResourceProjects.org/blob/master/server/controllers/companies.js#L63

This is starting to become a problem: If our first query returns a lot of (in this case, but many controllers are affected) companies, then a lot of new queries result. This has two issues:

• It is slow (lots of I/O)
• It may be resulting in overloading the cluster and destabilizing the whole application due to broken DB connection (unconfirmed but suspected)

The fix would be to stuff all the entity IDs into an array and only run one query on the links to return all links with any of those (e.g.) company IDs. Then with the result of that query the entity can be populated with the information from the relative links relating to that entity.

@KseniyaKuprina At the moment this issue is assigned to you but let me know if you need assistance