CODEWITHSUNDEEP
mongodb
Mustafa Yasin Çelik
Mustafa Yasin Çelik

Reputation: 13

Mongodb LookUp Poor Performance

var product = db.GetCollection<Product>("Product");
var lookup1 = new BsonDocument(
    "$lookup", 
    new BsonDocument { 
        { "from", "Variant" }, 
        { "localField", "Maincode" }, 
        { "foreignField", "Maincode" }, 
        { "as", "variants" } 
    }
);
var pipeline = new[] { lookup1};
var result = product.Aggregate<Product>(pipeline).ToList();

The data of collection a is very large so it takes me 30 seconds to put the data in the list. What should I do to make a faster lookup?

Upvotes: 1

Views: 936

Answers (1)

Joe
Joe

Reputation: 28376

What that query is doing is retrieving every document from the Product collection, and then for each document found, perform a find query in the Variant collection. If there is no index on the Maincode field in the Variant collection, it will be reading the entire collection for each document.

This means that if there are, say, 1000 total products, with 3000 total variants (3 per product, on average), this query will be reading all 1000 documents from Product, and if that index isn't there, it would read all 3000 documents from Variant 1000 times, i.e. it will be examining 3 million documents.

Some ways to possibly speed this up:

  • create an index on {Maincode:1} in the Variant collection This will reduce the number of documents that must be read in order to complete the lookup
  • change the schema If the variants are stored in the same document with the product, there is no need for a lookup
  • filter the products prior to lookup Again, reducing the documents read during the lookup
  • use a cursor to retrieve the documents in batches If you perform any necessary sorting first, and the lookup last, you can return the documents to the application in batches, which would allow the application to display or begin processing the first batch before the second batch is available. This doen't make the query itself faster, but it can reduced the perceived wait in the application.

Upvotes: 1

Related Questions