CODEWITHSUNDEEP
mongodbperformanceexpressmongoose
Rainmaker
Rainmaker

Reputation: 321

MongoDB - searches are slow with connected collections

I have multiple collections in my MongoDB database.

Regular searches on one collection is fine. But search on a collection that has relationships to other collections is visibly slow. I am the only one on the system, both DB and the express application are on the same laptop. The search takes about 3 seconds.

Here is the collection in question:

var mongoose = require("mongoose");

var visitSchema = new mongoose.Schema({
    Study: {type: mongoose.Schema.Types.ObjectId, ref: "study"},
    Site:  {type: mongoose.Schema.Types.ObjectId, ref: "site"},
    Subject: {type: mongoose.Schema.Types.ObjectId, ref: "subject"},
    Phase: {type: mongoose.Schema.Types.ObjectId, ref: "phase"},
    Visit_date: Date
},{ strict: false });


module.exports = mongoose.model("visit", visitSchema);

The route that does the search 

router.get("/visit/subject/:id", middleware.isLoggedin, function(req,res){
    visit.find({ Subject: req.params.id }).populate("Study").populate("Site").populate("Phase").populate("Subject").sort('Visit_date').exec(function(err,returnedvisits){
        var returnedsubjects = [];
        if(err){
            console.log(err);
        } else {
            console.log(returnedvisits);
            res.render("visit", {returnedvisits: returnedvisits, returnedsubjects: returnedsubjects});
        }
    });
});

returns only 2 records but gets the related data from other collections:

[ { _id: 5d9e1bdec73b3c34c0b97bb9,
    Subject:
     { _id: 5d8e42f200c09107744bd8c0,
       Subject_Name: 'Virat Kohli',
       Subject_DOB: 1979-10-24T00:00:00.000Z,
       Subject_Address: '14 Coakwell drive',
       Subject_City: 'Toronto',
       Subject_Prov: 'ON',
       Subject_zip: 'L6B 0L7',
       Subject_Country: 'Canada',
       __v: 0 },
    Study:
     { _id: 5d9e1bc3c73b3c34c0b97bb8,
       Study_short_name: 'CCR',
       Study_name: 'Cancel Cell Research',
       __v: 0 },
    Site:
     { _id: 5d9635a6e5ede964f4394447,
       Site_short_name: 'MCR',
       Site_name: 'Microsoft',
       __v: 0 },
    Phase:
     { _id: 5d93bd727d0a484b04317b68,
       Phase_number: 1,
       'Phase Description': 'Phase 1',
       __v: 0 },
    Visit_date: 2018-10-10T04:00:00.000Z,
    __v: 0 },
  { _id: 5d9e1bf6c73b3c34c0b97bba,
    Subject:
     { _id: 5d8e42f200c09107744bd8c0,
       Subject_Name: 'Virat Kohli',
       Subject_DOB: 1979-10-24T00:00:00.000Z,
       Subject_Address: '14 Coakwell drive',
       Subject_City: 'Toronto',
       Subject_Prov: 'ON',
       Subject_zip: 'L6B 0L7',
       Subject_Country: 'Canada',
       __v: 0 },
    Study:
     { _id: 5d9e1bc3c73b3c34c0b97bb8,
       Study_short_name: 'CCR',
       Study_name: 'Cancel Cell Research',
       __v: 0 },
    Site:
     { _id: 5d9635a6e5ede964f4394447,
       Site_short_name: 'MCR',
       Site_name: 'Microsoft',
       __v: 0 },
    Phase:
     { _id: 5d94abb5092b7333b472b0f6,
       Phase_number: 2,
       'Phase Description': 'Phase 2',
       __v: 0 },
    Visit_date: 2019-11-11T05:00:00.000Z,
    __v: 0 } ]

Is it supposed to be this slow? I know MongoDB is not for reporting. But a little complex query like this?

what are the alternatives? - Should I save these data in one collection so the search would be faster? - Would Indexes help?

Thank you!

Upvotes: 0

Views: 203

Answers (2)

Umashankar Das
Umashankar Das

Reputation: 601

Since you only have 2 records, the optimal approach would be to embed the Study, Site,Subject and Phase documents inside the same main document. You're not making that many copies.

This is also recommended in MongoDB One-By-Many documentation.

Alternatively, if your number of records start increasing, just create an index in each of the collections(Subject,Phase,Study,Site) on that id, and see, how much your performance improves. This should provide good benefits.

Upvotes: 3

Superpassteque
Superpassteque

Reputation: 101

Try to change Datetime format to string or tiny datetime. It can help with perf a lot. https://www.mongodb.com/blog/post/fast-datetimes-in-mongodb

Upvotes: 1

Related Questions