MongoDB: How to speed up my data reorganisation query/operation?-CodePudding

I'm trying to analyse some data and I thought my queries would be faster ultimately by storing a relationship between my collections instead. So I wrote something to do the data normalisation, which is as follows:

var count = 0;
db.Interest.find({'PersonID':{$exists: false}, 'Data.DateOfBirth': {$ne: null}})
.toArray()
    .forEach(function (x) {
    if (null != x.Data.DateOfBirth) {
        var peep = { 'Name': x.Data.Name, 'BirthMonth' :x.Data.DateOfBirth.Month, 'BirthYear' :x.Data.DateOfBirth.Year};
        var person = db.People.findOne(peep);
        if (null == person) {
            peep._id = db.People.insertOne(peep).insertedId;
            //print(peep._id);
        }

        db.Interest.updateOne({ '_id': x._id }, {$set: { 'PersonID':peep._id }})

          count;
        if ((count % 1000) == 0) {
            print(count   ' updated');
        }
    }
})

This script is just passed to mongo.exe.

Basically, I attempt to find an existing person, if they don't exist create them. In either case, link the originating record with the individual person.

However this is very slow! There's about 10 million documents and at the current rate it will take about 5 days to complete.

Can I speed this up simply? I know I can multithread it to cut it down, but have I missed something?

CodePudding user response：

In order to insert new persons into People collection, use this one:

db.Interest.aggregate([
   {
      $project: {
         Name: "$Data.Name",
         BirthMonth: "$Data.DateOfBirth.Month",
         BirthYear: "$Data.DateOfBirth.Year",
         _id: 0
      }
   },
   {
      $merge: {
         into: "People",
         // requires an unique index on {Name: 1, BirthMonth: 1, BirthYear: 1}
         on: ["Name", "BirthMonth", "BirthYear"] 
      }
   }
])

For updating PersonID in Interest collection use this pipeline:

db.Interest.aggregate([
   {
      $lookup: {
         from: "People",
         let: {
            name: "$Data.Name",
            month: "$Data.DateOfBirth.Month",
            year: "$Data.DateOfBirth.Year"
         },
         pipeline: [
            {
               $match: {
                  $expr: {
                     $and: [
                        { $eq: ["$Name", "$$name"] },
                        { $eq: ["$BirthMonth", "$$month"] },
                        { $eq: ["$BirthYear", "$$year"] }
                     ]
                  }
               }
            },
            { $project: { _id: 1 } }
         ],
         as: "interests"
      }
   },
   {
      $set: {
         PersonID: { $first: "$interests._id" },
         interests: "$$REMOVE"
      }
   },
   { $merge: { into: "Interest" } }
])

Mongo Playground