[Mongo API] Queries hanging on a collection only 500k documents

Question

[Mongo API] Queries hanging on a collection only 500k documents

Juliano Nunes 1

I've created a collection where all documents have the same schema, like this:

{   
    "_id" : "8ded8078-4f0f-41e4-87aa-5e4d3227392d",   
    "AreaId" : "b321ddef-8cdd-4b80-9edf-07558e6a2cac",   
    "InvoiceNumber" : "0002115545654",   
    "CreatedAt" : "2021-07-07T12:38:00.000Z",   
    "CreatedBy" : "Juliano Oliveira",   
    "InvoicePeriodStart" : "2021-07-16T00:00:00.000Z",   
    "InvoicePeriodEnd" : "2024-07-16T00:00:00.000Z",   
    "Products" : [  
        {  
            "Section" : NumberInt(1),   
            "ProductId" : "434fe51f-ac59-4708-9177-a7742f54e282",   
            "ProductNumber" : "004846846684",   
            "Name" : "Some random product A",   
        }  
    ],   
    "Amount" : 2732.49,  
    "CustomerId": 8ded8078-4f0f-41e4-87aa-5e4d3227392d,  
    "CustomerName": "Some Random Company",  
    "CustomerEmailAddress": "******@randomcompany.com"  
}

I've created some single field indexes for the following fields:

AreaId
InvoiceNumber
CreatedAt
CreatedBy
InvoicePeriodStart
InvoicePeriodEnd
Products.Section
Products.ProductId
Products.Name

The collection has only 500k documents and the queries are generated by a query builder using MongoClient in a C# service, with pagination. After some changes, the query builder generates this query:

{   
    "Products": {   
        "$elemMatch": { "Section": 1, "ProductId": "298d6420-0f92-48f0-be1c-093f73f4f012" } }

}

Running an explain on this query and comparing with the query bellow:

{ "Products.Role": 1, "Products.ProductId": "298d6420-0f92-48f0-be1c-093f73f4f012" }

they show different runtimeExecutionTimes as you can see below:

Version with $elemMatch

					"runtimeExecutionTimes" : {  
						"queryEngineExecutionTimeMS" : 72.0501,  
						"systemFunctionExecutionTimeMS" : 22.13,  
						"userDefinedFunctionExecutionTimeMS" : 0  
					},

Version without

					"runtimeExecutionTimes" : {  
						"queryEngineExecutionTimeMS" : 53.11,  
						"systemFunctionExecutionTimeMS" : 32.2899,  
						"userDefinedFunctionExecutionTimeMS" : 0  
					},

Why is this different? One thing that I've noticed is that the version using $elemMatch requires an index for Products array and the version without it, requires an individual index for each field inside the Products array (like Products.ProductId, Products.Section).

How can I optimize it and run the CountDocuments faster?

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-24T00:56:47.313+00:00

Hi @Juliano Nunes ,

Thanks for using Microsoft Q&A !!
I am checking internally on this and get back to you.

Thanks
Saurabh
Juliano Nunes 1 Reputation point

2021-07-27T14:14:55.88+00:00
Hi @Saurabh Sharma ,

Do you have any update on this?

I'm having another problem that I think it's related to the MongoDB Driver. Even the CountDocuments method takes longer when running in my C# code than running from a tool like Studio 3T or noSqlBooster. If somebody experienced that too and have some lead on how to solve it.

Thank you.

UPDATE

The issue related to the CountDocuments method is indeed generated in the Mongo Driver as it generates what is seems to be a more complex query to count documents:

{ { "aggregate": "Products", "pipeline": [ { "$match": { "Products.ProductId": "298d6420-0f92-48f0-be1c-093f73f4f012", "Products.Section": 1 } }, { "$group": { "_id": 1, "n": { "$sum": 1 } } } ], "cursor": {} } }

1 answer

Your answer

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-24T00:56:47.313+00:00

Hi @Juliano Nunes ,

Thanks for using Microsoft Q&A !!
I am checking internally on this and get back to you.

Thanks
Saurabh
Juliano Nunes 1 Reputation point

2021-07-27T14:14:55.88+00:00

Hi @Saurabh Sharma ,

Do you have any update on this?

I'm having another problem that I think it's related to the MongoDB Driver. Even the CountDocuments method takes longer when running in my C# code than running from a tool like Studio 3T or noSqlBooster. If somebody experienced that too and have some lead on how to solve it.

Thank you.

UPDATE

The issue related to the CountDocuments method is indeed generated in the Mongo Driver as it generates what is seems to be a more complex query to count documents:

{ { "aggregate": "Products", "pipeline": [ { "$match": { "Products.ProductId": "298d6420-0f92-48f0-be1c-093f73f4f012", "Products.Section": 1 } }, { "$group": { "_id": 1, "n": { "$sum": 1 } } } ], "cursor": {} } }

Answer 1

Saurabh Sharma 23,846 Microsoft Employee Moderator

Hi @Juliano Nunes ,

Sorry for the delay. As per internal discussion with the products team they need to investigate this issue in your environment and thus requested to raise a support ticket for this. Could you please create a support ticket and provide me the ticket number over here so that I can share that with the products team for better assistance. In case you have any limitation creating a support ticket please let know and I will help providing a one time free support ticket for you.

Thanks
Saurabh

Share via

[Mongo API] Queries hanging on a collection only 500k documents

1 answer

Your answer