#1 - Query the table with a pointer, and include the pointer attributes
const query = new Parse.Query("TableX");
query.include("PointerNameY");
const result = await query.first();
#2 Query two tables separately, one for the data, and the other for the pointer attributes
const query = new Parse.Query("TableX");
const result = await query.first();
const pointerQuery = new Parse.Query("PointerNameYTableZ");
const result = await query.first();
#3 - Use an aggregate query with $lookup to combine the table attributes with the attributes of the pointer in one file
As far as I know, approaches 1 and 2 are equal in terms of efficiency, because each “include” makes another query call in the background. How about approach #3 though? is $lookup also making a separate query call? Which of these would be the most efficient in terms of minimizing connection count to the DB and using fewer DB resources?
Such a generic comparison is not interesting, it depends on the access pattern.
If one request iterates over many items, the MongoDB aggregation will have the best performance. The aggregation will happen on database level, so MongoDB can apply all kinds of performance optimizations. Typical requests are reports that sum up business stats (active users last month, created items in quarter, …"
Maybe noteworthy that the aggregation pipeline runs on the MongoDB server and thus requires additional overhead. Depending on the request characteristics you may see an increase in RAM and CPU use.
A more reliable way to validate assumptions about performance is to do a load test that resembles your expected request patterns.
Fast answer : the first one.
The $in is done on the server.
It’s not super different (same DB resources), but avoids a bit of network communication to the client.
And avoid $lookup whenever you can. It does the join one document at a time.
It can kill you db.
