CODEWITHSUNDEEP
databasemongodbjoinnosqldatabase-performance
gen
gen

Reputation: 10040

Mongodb: should I use a `foreach` or a `$lookup` to perform an update on `collection1` using data from `collection2` for better performance?

I have two collections, collection1 and collection2 and I need to update all records in collection1 by decorating them with some corresponding data from collection2. To "join" these collections I have an entry userid in both collections that I can use to identify matching records.

For better performance, should I use foreach or a $lookup?

Upvotes: 0

Views: 341

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

So when you use .find() & then write back to database, You're reading a set of data from database & iterating over all of those documents using foreach & then writing back to database again, If you've a huge dataset to work on then this definitely a time consuming thing & unnecessary impact on both application & database servers, which can also be time consuming as data has to flow through servers & is not preferred to so. Just in case if you had to got this route check .bulkWrite() - at least that helps you to write documents in one DB call, but there is a limitation, it can process only 100K docs in a go if you send more, then it will internally do chunks & process data - but I've not really tested it with millions of docs.

As you've version 4.2.3, Starting MongoDB v4.2 aggregation has a new feature $merge that can help merge results of aggregation query to another collection, To be in safe point first try to write data to a new collection & if everything looks good write it on collection1 - at any point have a backup on collection1. This could save you sometime & also all the processing do happen in server side (Database side). Apart from this both approaches has read & write mechanism so test your queries are covered with indexes by using explain. Also consider aggregation-limits.

Upvotes: 1

Related Questions