CountDocuments takes 5+ seconds running on C# app, but mongoshell takes 1 second or less

Working with Data Drivers
,
1

I need to execute the query below to return 10 objects (it’s a paginated query) and get the total count of objects for this query, then I found that when executing both queries on C# app and on mongo shell, the first one takes longer to finish, even though there are no object transformation or such thing, as it is only a count operation.

//query is { "PersonId" : "298d6420-0f92-48f0-be1c-093f73f4f012", "Role" : 1 }
var totalRowsQuery = await db.Documents
                            .CountDocumentsAsync(query);

Why?

2

I’ve downloaded the driver source code and saw that the generated script for CountDocuments method seems to be more complex than the count used on mongo shell.

Using mongo shell
db.getCollection(“documents”).explain(‘executionStats’).count({ “PersonId” : “298d6420-0f92-48f0-be1c-093f73f4f012”, “Role” : 1 })

{
	"command" : "db.runCommand({explain: { 'count' : 'documents', 'query' : { 'PersonId' : '298d6420-0f92-48f0-be1c-093f73f4f012', 'Role' : 1.0 } }})",
	"stages" : [
		{
			"stage" : "$aggregateQuery",
			"timeInclusiveMS" : 19.3676,
			"timeExclusiveMS" : 0.0565,
			"in" : 1,
			"out" : 1,
			"dependency" : {
				"getNextPageCount" : 1,
				"count" : 1,
				"time" : 19.3111,
				"bytes" : NumberLong(39)
			},
			"details" : {
				"database" : "db",
				"collection" : "documents",
				"query" : {
					"$and" : [
						{
							"PersonId" : {
								"$eq" : "298d6420-0f92-48f0-be1c-093f73f4f012"
							}
						},
						{
							"Role" : {
								"$eq" : 1
							}
						}
					]
				},
				"indexUsage" : {
					"pathsIndexed" : {
						"individualIndexes" : [
							"PersonId",
							"Role"
						],
						"compoundIndexes" : [ ]
					},
					"pathsNotIndexed" : {
						"individualIndexes" : [ ],
						"compoundIndexes" : [ ]
					}
				},
				"aggregate" : {
					"$count" : "c"
				},
				"shardInformation" : [
					{
						"activityId" : "778181fd-04bc-4ed3-903a-d491c01b1722",
						"shardKeyRangeId" : "[,FF) move next",
						"durationMS" : 5.1081,
						"preemptions" : 0,
						"outputDocumentCount" : 1,
						"retrievedDocumentCount" : 0
					}
				],
				"queryMetrics" : {
					"retrievedDocumentCount" : 0,
					"retrievedDocumentSizeBytes" : 0,
					"outputDocumentCount" : 1,
					"outputDocumentSizeBytes" : 46,
					"indexHitRatio" : Infinity,
					"totalQueryExecutionTimeMS" : 1.4,
					"queryPreparationTimes" : {
						"queryCompilationTimeMS" : 0.19,
						"logicalPlanBuildTimeMS" : 0.13,
						"physicalPlanBuildTimeMS" : 0.3,
						"queryOptimizationTimeMS" : 0.04
					},
					"indexLookupTimeMS" : 0.48,
					"documentLoadTimeMS" : 0,
					"vmExecutionTimeMS" : 0.51,
					"runtimeExecutionTimes" : {
						"queryEngineExecutionTimeMS" : 0.03,
						"systemFunctionExecutionTimeMS" : 0,
						"userDefinedFunctionExecutionTimeMS" : 0
					},
					"documentWriteTimeMS" : 0
				}
			}
		}
	],
	"estimatedDelayFromRateLimitingInMilliseconds" : 0,
	"retriedDueToRateLimiting" : false,
	"totalRequestCharge" : 7.02,
	"continuation" : {
		"hasMore" : false
	},
	"ok" : 1
}

When using MongoDriver CountDocuments generated script:

db.getCollection("policies").explain('executionStats').aggregate(
    [
        { 
            "$match" : { 
                "CommissionedAgents.PersonId" : "298d6420-0f92-48f0-be1c-093f73f4f012", 
                "CommissionedAgents.Role" : 1.0
            }
        }, 
        { 
            "$group" : { 
                "_id" : 1.0, 
                "n" : { 
                    "$sum" : 1.0
                }
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
)



{
	"command" : "db.runCommand({explain: { 'aggregate' : 'documents', 'pipeline' : [{ '$match' : { 'PersonId' : '298d6420-0f92-48f0-be1c-093f73f4f012', 'Role' : 1.0 } }, { '$group' : { '_id' : 1.0, 'n' : { '$sum' : 1.0 } } }], 'allowDiskUse' : false, 'cursor' : { } }})",
	"stages" : [
		{
			"stage" : "$aggregateQuery",
			"timeInclusiveMS" : 9058.3815,
			"timeExclusiveMS" : 0.0834,
			"in" : 1,
			"out" : 1,
			"dependency" : {
				"getNextPageCount" : 1,
				"count" : 2,
				"time" : 9058.2981,
				"bytes" : NumberLong(101)
			},
			"details" : {
				"database" : "db",
				"collection" : "documents",
				"query" : {
					"$and" : [
						{
							"PersonId" : {
								"$eq" : "298d6420-0f92-48f0-be1c-093f73f4f012"
							}
						},
						{
							"Role" : {
								"$eq" : 1
							}
						}
					]
				},
				"indexUsage" : {
					"pathsIndexed" : {
						"individualIndexes" : [
							"PersonId",
							"Role"
						],
						"compoundIndexes" : [ ]
					},
					"pathsNotIndexed" : {
						"individualIndexes" : [ ],
						"compoundIndexes" : [ ]
					}
				},
				"aggregate" : {
					"_id" : 1,
					"n" : {
						"$sum" : 1
					}
				},
				"shardInformation" : [
					{
						"activityId" : "2dd9a989-1d07-471f-b123-63bc123460ad",
						"shardKeyRangeId" : "[,FF) move next",
						"durationMS" : 200.1699,
						"preemptions" : 0,
						"outputDocumentCount" : 1,
						"retrievedDocumentCount" : 14454
					},
					{
						"activityId" : "901cdd1b-ffa6-4131-a1f6-299f06eeb6c5",
						"shardKeyRangeId" : "[,FF) move next",
						"durationMS" : 195.3146,
						"preemptions" : 0,
						"outputDocumentCount" : 1,
						"retrievedDocumentCount" : 8534
					},
					{
						"activityId" : "7e946d49-1979-4410-8b59-11f2ddb18aa8",
						"shardKeyRangeId" : "[,FF) move next",
						"durationMS" : 8654.8059,
						"preemptions" : 0,
						"outputDocumentCount" : 1,
						"retrievedDocumentCount" : 782
					}
				],
				"queryMetrics" : {
					"retrievedDocumentCount" : 23770,
					"retrievedDocumentSizeBytes" : 57425217,
					"outputDocumentCount" : 3,
					"outputDocumentSizeBytes" : 360,
					"indexHitRatio" : 0,
					"totalQueryExecutionTimeMS" : 417.5,
					"queryPreparationTimes" : {
						"queryCompilationTimeMS" : 1.14,
						"logicalPlanBuildTimeMS" : 0.47,
						"physicalPlanBuildTimeMS" : 0.64,
						"queryOptimizationTimeMS" : 0.08
					},
					"indexLookupTimeMS" : 0.29,
					"documentLoadTimeMS" : 310.99,
					"vmExecutionTimeMS" : 413.79,
					"runtimeExecutionTimes" : {
						"queryEngineExecutionTimeMS" : 102.49,
						"systemFunctionExecutionTimeMS" : 28.47,
						"userDefinedFunctionExecutionTimeMS" : 0
					},
					"documentWriteTimeMS" : 0.02
				}
			}
		}
	],
	"estimatedDelayFromRateLimitingInMilliseconds" : 8623.5604,
	"retriedDueToRateLimiting" : true,
	"totalRequestCharge" : 1869.3899999999999,
	"continuation" : {
		"hasMore" : false
	},
	"ok" : 1
}

Why it generates the script with such difference?

3

One additional info. The obsolete CountAsync method from the Driver, generates the simpler (and faster) query.

{{ "count" : "policies", "query" : { "PersonId" : "298d6420-0f92-48f0-be1c-093f73f4f012", "Role" : 1 } }}

I understand that this method is being deprecated, but how can I optimize the recommended method to make it run faster and cheaper?

4

Hello @Juliano_Oliveira, welcome to the MongoDB Community forum!

The way to optimize the recommended countDocuments query is to create a Compound Index on the query filter fields you are using: PersonId + Role. Note the order of the fields in the index definition also matters in query optimization.

As you already know the countDocuments is equivalent to the following aggregation. You can run the explain with “executionStats” mode on the aggregation query (the countDocuments doesn’t support the explain).

db.collection.aggregate([ 
  { $match: { // filter fields and values... } }, 
  { $count: "count" } 
])

When the index is used, the query plan does show a COUNT_SCAN stage. Otherwise it would be a COLLSCAN.

Please include the MongoDB cluster and C# driver versions in your reply post.

5

Hello @Prasad_Saya,

For some reason, while debugging the Mongo Driver source code, I saw that the script that was generated does not use $count, but $group.

 db.getCollection("policies").explain('executionStats').aggregate(
    [
        { 
            "$match" : { 
                "PersonId" : "298d6420-0f92-48f0-be1c-093f73f4f012", 
                "Role" : 1.0
            }
        }, 
        { 
            "$group" : { 
                "_id" : 1.0, 
                "n" : { 
                    "$sum" : 1.0
                }
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
)
6

That should be okay. The functionality is the same - to count all documents.