0

There are thousands of documents in MongoDB collection and when I run this query it takes lots of time.

This is my db query:

db.collection.aggregate( [
{   $match: commonSearch },
{
    $addFields: {
        "ingestionDate": {
            $toDate: "$commonData.bo_ingestionDate"
        },
        "lastModifiedDate": { $toDate: "$commonData.bo_lastModifiedDate" }
    }
},
{
    $project: {
        "commonData": 1, "data.page_1": 1, documentType: 1,
        isExported: 1, dcn: 1,
        endorserNumber: "$data.page_1.data.H_BARCODE.value",
        "receivedDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": "$ingestionDate"
            }
        },
        "exportedDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": "$lastModifiedDate"
            }
        }
    }
},
{   $match: searchQuery },
{   $sort: { [sortingKey]: sortingOrder } },
{   $skip: (req.body.pageNumber - 1) * req.body.rowPerPage },
{   $limit: req.body.rowPerPage }
] )
.allowDiskUse(true)

The query takes more time to execute; how to decrease execution time.

prasad_
  • 12,755
  • 2
  • 24
  • 36
ramratan
  • 11
  • 2
  • (1) Run a query plan using `explain()` with "executionStats" mode. Post the results. (2) Why is this `{ $match: searchQuery }` at the 4th stage of the pipeline? – prasad_ Nov 01 '19 at 12:50
  • Why the `$dateToString` usage here? Are you in fact just simply looking to query on "specific dates"? The same applies to the whole `$toDate` part before that. This would appear to suggest that in fact your current "dates" are stored as strings and that your initial query does not select this range. This is probably the greatest inefficiency here. Again I would suggest actually showing the full query variables ( i.e `commonSearch` and `searchQuery` ) and an actual small sample of data you expect to match. Converting the dates in the collection though *should* be your first step. – Neil Lunn Nov 02 '19 at 01:00
  • @Prasad_ at the 4th state of the pipeline after projection 'searchQuery' contains search parameter. – ramratan Nov 04 '19 at 05:22
  • @NeilLunn $dateTosString using for creating local variable in mongodb and converted into 'Y-M-D' format because there is 'exportedDate' comes form UI for searching and sorting purpose. The key contains inside 'searchQuery' Object – ramratan Nov 04 '19 at 05:26
  • You may want to look at these topics closely and see what is useful: [Analyze query performance](https://docs.mongodb.com/manual/tutorial/analyze-query-plan/index.html), [Query optimization](https://docs.mongodb.com/manual/core/query-optimization/) and [Aggregation pipeline optimization](https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/index.html). – prasad_ Nov 04 '19 at 05:51
  • Why are you using `$match` stage twice? You may want to keep them together at the 1st stage. In general, a match stage should come as early as possible in a pipeline; this is to use any indexes defined on the match fields. Also, more stages means more processing.Please go thru the topics I had posted in my earlier comment. The reason why I had asked for an _explain_ output is someone can look at what is happening with your query plan and point out the shortcomings in the performance. – prasad_ Nov 04 '19 at 06:05
  • You were not paying attention to the comment. I am well aware of what `$dateToString` does. What I am **pointing out** is this conversion process is completely detrimental to your query performance if not totally unnecessary. What you really needed to do here was edit your question and show the necessary detail of sample documents and the actual query content of the two mentioned variables which are not present in your question. You did not do that.. Please learn from existing answers. Forcing conversion during an aggregation pipeline is the problem with your performance. – Neil Lunn Nov 04 '19 at 09:00

0 Answers0