Spring Data MongoDB Slow MongoTemplate.find() Performance

Question

I'm having performance issues when querying ~12,000 user documents, indexed by 1 column, (companyId), no other filter. The whole collection only has ~27000. It took me about 12 seconds to get the ~12000 rows of data...

I tried running explain for this query: db.instoreMember.find({companyId:"5b6be3e2096abd567974f924"}).explain();

result follows:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "production.instoreMember",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "companyId" : {
                "$eq" : "5b6be3e2096abd567974f924"
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "companyId" : 1,
                    "name" : 1,
                    "phoneNumber" : 1
                },
                "indexName" : "companyId_1_name_1_phoneNumber_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "companyId" : [ ],
                    "name" : [ ],
                    "phoneNumber" : [ ]
                },
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "companyId" : [
                        "[\"5b6be3e2096abd567974f924\", \"5b6be3e2096abd567974f924\"]"
                    ],
                    "name" : [
                        "[MinKey, MaxKey]"
                    ],
                    "phoneNumber" : [
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "companyId" : 1
                    },
                    "indexName" : "companyId_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "companyId" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "companyId" : [
                            "[\"5b6be3e2096abd567974f924\", \"5b6be3e2096abd567974f924\"]"
                        ]
                    }
                }
            }
        ]
    },
    "serverInfo" : {

    },
    "ok" : 1
}

It seems that it is actually using the indexed companyId field, and if i do the search directly via mongodb shell, it's very fast: only 1~2 seconds.

But via Spring MongoDB Data - MongoTemplate:

final Query query = new Query().addCriteria(Criteria.where("companyId").is(adminCompanyId));
final List<InstoreMember> listOfInstoreMembers = mongoTemplate.find(query, InstoreMember.class);

This becomes very slow ~10-12seconds. (How i measure is that I put a break point at the find statement, let it step through to next line, which took about ~10-12seconds)

I've added the DEBUG line for mongodb spring bootstrap and here is the logged output of the find statement :

2018-08-14 23:53:34.493 DEBUG 22733 --- [bio-8080-exec-2] o.s.data.mongodb.core.MongoTemplate      : 
find using query: { "companyId" : "58fa36dd31d103038e64b061"} fields: null for class: class fn.model.InstoreMember in collection: instoreMember

Version of spring-data-mongodb i use:

compile ("org.springframework.data:spring-data-mongodb:1.10.7.RELEASE")

Answer 1

I had this problem.

The slow part is mapping Document to Java object. Mongo template doesn't map at the codec level so it goes bson->Document->POJO. If you use just the mongo driver with the POJO codec that will go bson->pojo and remove the template mapping layer overhead.

Also, if you have old data and have moved packages, this will also break their mapping layer and make it super slow as it falls back to reflection.

Answer 2

How do i see the raw query that is actually being executed by spring mongodb data?

If the query is taking 12 seconds to load, you actually have time to run db.currentOp to see what is taking so long. The output will contain 'command' field that you can use to see what the DB has been tasked with.