Bug Report
Our CMS model contains ~30 collections with low volumes of data (from 5 to maybe 300 rows per collection).
We use relationships a lot, and the response times are extreme. A query that would take maybe 1s in a RDBMS can take several minutes to run.
Expected Behavior
Database queries should run in a reasonable time.
Current Behavior
Using REST and GraphQL APIs triggers the performance issues.
Possible Solution
I've had a look at the source code and my understanding is that Payload manipulates relationship rows 1 by 1.
Let's say we have a one to many relationship between A and B, ie each record from A is storing 0 or more references to records of B.
Using Mongoose populate method, the following would happen:
- Mongoose finds all records from A
- Mongoose retrieves all the referenced ids of B from the preceding step
- Mongoose finds all records from B where _id is in ids from preceding step
Mongoose made 2 queries to MongoDB.
Using Payload the following happens:
- Payload asks Mongoose to find all records from A
- Payload retrieves all the referenced ids of B from the preceding step
- For each reference of B, Payload asks Mongoose to retrieve the record in B by id
Payload generated 1 + B queries to MongoDB.
Now if you have 10 relationships in a collection and you need to stitch 6 collections together the amount of MongoDB queries generated becomes enormous.
Steps to Reproduce
We can provide an access to our environment as well as queries if you need to observe the issue.
Detailed Description
Payload: 0.13.6
Bug Report
Our CMS model contains ~30 collections with low volumes of data (from 5 to maybe 300 rows per collection).
We use relationships a lot, and the response times are extreme. A query that would take maybe 1s in a RDBMS can take several minutes to run.
Expected Behavior
Database queries should run in a reasonable time.
Current Behavior
Using REST and GraphQL APIs triggers the performance issues.
Possible Solution
I've had a look at the source code and my understanding is that Payload manipulates relationship rows 1 by 1.
Let's say we have a one to many relationship between A and B, ie each record from A is storing 0 or more references to records of B.
Using Mongoose populate method, the following would happen:
Mongoose made 2 queries to MongoDB.
Using Payload the following happens:
Payload generated 1 + B queries to MongoDB.
Now if you have 10 relationships in a collection and you need to stitch 6 collections together the amount of MongoDB queries generated becomes enormous.
Steps to Reproduce
We can provide an access to our environment as well as queries if you need to observe the issue.
Detailed Description
Payload: 0.13.6