question

julianonunes avatar image
0 Votes"
julianonunes asked SaurabhSharma-msft answered

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

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": "some@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?


azure-cosmos-db
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @julianonunes,

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

Thanks
Saurabh

0 Votes 0 ·

Hi @SaurabhSharma-msft,

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": {}
     }
 }
0 Votes 0 ·

1 Answer

SaurabhSharma-msft avatar image
1 Vote"
SaurabhSharma-msft answered

Hi @julianonunes,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.