Spring + MongoDB custom queries running slow

Question

I have a MongoDB collection (called "Products") of ~7000 documents, hosted on a Mongo Atlas shared cluster, looking somewhat like this:

{
   identifier: 123,
   archived: true,

   /* Some other properties.. */
}

Attempting to run simple queries on the above collection in a manner such as:

mongoTemplate.find(
      new Query().addCriteria(Criteria.where("archived").is(false)), 
      Product.class, 
      "Products");

leads to an extremely high execution time, 8~9 seconds on both the local instance as well as deployed (Heroku) version. However, when running the same query directly from the MongoRepository<>, e.g. repository.findByArchived(false), the query runs almost instantaneously (as it would do in the mongo CLI).

Am I missing out some additional configuration that is preventing mongoTemplate from running queries in a smoother manner like the pre-configured MongoRepository (e.g. some bson/POJO encoding)? Any help on this would be greatly appreciated!

Can you confirm that in your application there is no additional mongo related filter/configuration? Or in other words you are able to reproduce it in seprate application with minium code. — Rajat Goel, Jun 01 '21 at 14:33
Try using an existing or adding an index with: `new Query().addCriteria(Criteria.where("archived").is(false)).withHint("your_index")` — LMC, Jun 03 '21 at 20:14
Can you run 3-4 times each query? Seems like you run 1st time with `.addCriteria` and after then when you run `findByArchived` MongoDB uses the cache and returns fast. Provide also `.explain` for both query — Valijon, Jun 05 '21 at 18:21

score 0 · Answer 1 · answered Jun 04 '21 at 09:16

0

Its hard to tell what exactly causing the slowness of custom queries but atlas provides several tools to help analyze slow queries being executed on your clusters.

The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance. Do give it a read - Performance Advisor

answered Jun 04 '21 at 09:16

Abhishek Nayek

19
1
2

I doubt that there is an indexing issue when the data in question contains next to nothing that needs indexing for this query. – C. Sederqvist Jun 04 '21 at 22:21

score 0 · Answer 2 · edited Jun 04 '21 at 15:29

Although there's no way to answer exactly what you need to change, if the connections are ok, there's a chance your code is not optimized, I would advise you to check the latest changes made to the code (if the database has ~7000 documents I will assume that it was working properly until recently).

Also, something like this could happen: Why is processing a sorted array faster than processing an unsorted array?

score 0 · Answer 3 · answered Jun 04 '21 at 22:11

Using a Criteria Query with MongoTemplate

I might have misinterpreted your problem area completely, but I hope that I'm not totally lost. To me it looks as if you want a list of projects in a series where the archived property is false?

Unsure of what some other properties mean, but with this data:

    {
       identifier: 123,
       archived: true,
    
       /* Some other properties.. */
    }

To me it looks like you would get much better performance, readability and results by using Projection with the MongoTemplate to include only the properties that you actually want and exclude the rest.

So instead of writing:


mongoTemplate.find(
      new Query().addCriteria(Criteria.where("archived").is(false)), 
      Product.class, 
      "Products");

You'd probably be better off doing something like this:


public List findActive() {
    Query query = new Query();
    // Here you exclude those unrelated 'other properties'
    query.fields().exclude("prop1").exclude("prop2");
    query.addCriteria(Criteria.where("archived").is(false));
        
    return mongoTemplate.find(query, Product.class);
}

Anyway, as I said, please tell me if I've misunderstood the problem. If so I'll correct the errors.

Spring + MongoDB custom queries running slow

3 Answers3

Using a Criteria Query with MongoTemplate