MongoTemplate and ISODate

Drivers & ODMs
,
#1

My application is a spring boot REST API which is interacting with MongoDB using the MongoTemplate.

We are observing a delay in response time for retrieval operations when the query is having a date range.

In our analysis, we noticed that the query when we ran using Mongo shell it is taking 20milliSeconds while the same query through java API it is more than 9 seconds.

Only difference is that Mongoshell accepts the date formart as ISODate in query while MongoTemplate in java is framing EPOCH date.

The date is stored in ISODate format in MongoDB Collection documents

The query generated from java side as follows

{ “rowCreationDate” : { “$gte” : { “$date” : 1566345601049 }, “$lte” : { “$date” : 1598054401049 } } }

Mongo shell query

{ “rowCreationDate” : { “$gte” : ISODate(“2018-08-07T14:13:10.979Z”), “$lte” : ISODate(“2020-09-07T14:13:10.980Z”) } }

MongoDB version: 4.0.18

How can we introduce ISODate in query generated from Java using Mongotemplate? Will it helps to improve the retieval time?

#2

Hello @Jain_Jose, welcome to the community.

Can you post the actual MongoTemplate query, including the date data? Is there any index defined on the rowCreationDate field?

#3

Hi @Prasad_Saya,

rowCreationDate field removed from Index (from a compound index) as we do not see any performance improvement.

Index on collection is :

({rowStatus:1,subscriberIdentifier:1,functionalErrorCode:1,productName:1})

The query originated by Mongo template is something similar below

db.getCollection('TestCollection').find({  
    "rowStatus" : { "$in" : ["NEW"] },
	"subscriberIdentifier" : { "$in" : ["1234", "6789", "0987", "67676"] },
	"productName" : { "$in" : ["PRODUCT1", "PRODUCT2"] },
	"rowCreationDate" : { "$gte" : { "$date" : 1514764801049 }, "$lte" : { "$date" : 1609459201049 } },
     Fields: { },
     Sort: { }
})

Note: Fields: { }, Sort: { } fields appended by default (not by programmer)

#4

Okay. What I meant was - how are you defining your MongoTemplate query. The following example Java code is used to query a MongoDB date field, which you see its value as, e.g., ISODate("2020-09-23T10:42:16.983Z") in the mongo shell. And, this date is stored in the MongoDB database as type Date.

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd H:m:s");
Date fromDate = dateFormat.parse("2020-09-23 00:00:00");
Date toDate = dateFormat.parse("2020-09-23 23:59:59");

Criteria c1 = Criteria.where("someDate").gte(fromDate);
Criteria c2 = Criteria.where("someDate").lte(toDate);
Criteria c = new Criteria().andOperator(c1, c2);

Query qry = Query.query(c);

MongoOperations mongoOps = new MongoTemplate(MongoClients.create(), "testDB");
List<Document> result = mongoOps.find(qry, Document.class, "testCollection");

I cannot tell how you can improve your query performance, with the provided info. If the query, data and the server are the same, then the query should perform similarly with the shell or the Java application.

Indexes play an important part in improving query performance. Try using explain in “executionStats” mode on the query, and study the generated plan. See Analyze Query Performance.

#5

Thank you Prasad for your inputs.
We did the query performance analysis already using mongo shell and it is optmized already.
The real problem we are facing or suspecting is, since Mongo template is forcing the date to EPOCH this might be slowing down the response time. In Mongo shell the same query uses ISODate and response time is very good as i mentioned in my original post

We tried using simipleDate format but still the final query (in debug inspect) we can see EPOCH date. Is there any option to force it it use ISODate?

#6

The JSON you are seeing a JSON representation of the query and the data. It is the MongoDB Extended JSON (v2) representation of the BSON type.

JSON can only directly represent a subset of the types supported by BSON. To preserve type information, MongoDB adds this extension to the JSON format. That is what you are seeing in the logs.

For example, from the shell I created a document:

db.dates.find()
{ "_id" : 1, "dt" : ISODate("2020-09-23T12:10:15.710Z") }

When I query this same document, and the result from a MongoTemplate#find method:

List<Document> result = mongoOps.find(qry, Document.class, "dates")
result.forEach(doc -> System.out.println(doc.toJson()));

This prints: {"_id": 1.0, "dt": {"$date": 1600863015710}}

This shows the extended JSON representation of the MongoDB date field.

1 Like
#7

Thank you for the good explanations!
So, the date format difference between shell and java is just a representation and no impact in the query performance? Any chance for EPOCH to ISODate conversion internally at MongoDB while querying ?

#8

When you query from shell you are probably using something like this:

Consider these mongo shell queries:

db.dates.find( { dt: { $gte: ISODate("2020-09-22") } } )
returns: { "_id" : 1, "dt" : ISODate("2020-09-23T12:10:15.710Z") }

db.dates.find( { dt: { $gte: new Date("2020-09-22") } } )
returns: { "_id" : 1, "dt" : ISODate("2020-09-23T12:10:15.710Z") }

The ISODate() (and new Date()) used above are date objects used in shell (see Date()). Similarly, in your Java code you use the Java representation of date with your queries. And, it is to the java.util.Date or java.time.LocalDateTime (NOTE: I had already posted Java code in my earlier comments an example query using java.util.Date).

About the Query Performance:

The query performance in Java application depends upon the code, data, its representation in Java, the amount of data, the environment (hardware and software) it is being run, and other factors like operations on the system when you are running the query.

You can run an explain on the query and post the query plan here. It can be analyzed. Do include the Java code used, sample document from the collection, its Java representation (typically a POJO class), info about amount of data, and the environment it is run.