How to make updateMany() faster

Question

i have a documentDb with, let's say **mycollection**, ewith docs such as 
```
{ 
  "_id" : "id string 1", 
  "field" : "Fieldvalue" 
  "obj1" : { "until" : ISODate("2022-12-19T00:00:00Z") }, 
  "obj2" : { "id" : 316524 }, 
  "obj3" : { "status" : "available" }, 
  "data" : { },
},
{ 
  "_id" : "id string 2", 
  "field" : "Fieldvalue" 
  "obj1" : { "until" : ISODate("2023-12-19T00:00:00Z"), id: 77, username: "some" }, 
  "obj2" : { "id" : 316524 }, 
  "obj3" : { "status" : "available" }, 
  "data" : { },
},
...
```
 and respectively, i have an index
```
db.mycollection.createIndex(
    { "field": 1, "obj1.id": 1, "obj2.id": 1, "obj3.status": 1 },
    { name: "my_special_index" }
)
```
but running
```
db.mycollection.updateMany(
    {
        $and: [
            { field: "Fieldvalue" },
            { "obj2.id": 24569 },
            { "obj3.status": "available" },
            {
                $or: [
                    { "obj1.until": { $lt: new Date()} },
                    { "obj1.id": 77 }
                ]
            }
        ]
    },
    {
        $set: {
            "obj1": { "id": 77, "username": "some", "until": ISODate("2023-12-19T19:51:32.479Z") }
        }
    }
)
```
it takes **12 seconds**, that is way too much, given the whole collection is ~280k, and the records that match the condition ar 62k

i have tried running explain on the **find()** ( unfortunately, no way to run explain on the updateMany() )

```
 db.mycollection.find(
    {
        $and: [
            { field: "Fieldvalue" },
            { "obj2.id": 24569 },
            { "obj3.status": "available" },
            {
                $or: [
                    { "obj1.until": { $lt: new Date()} },
                    { "obj1.id": 77 }
                ]
            }
        ]
    },
).limit(100000).explain("executionStats").executionStats;
```
and i get, what are think is good response 
```
{
	"executionSuccess" : true,
	"executionTimeMillis" : "173.373",
	"planningTimeMillis" : "1.064",
	"executionStages" : {
		"stage" : "SUBSCAN",
		"nReturned" : "60163",
		"executionTimeMillisEstimate" : "168.929",
		"inputStage" : {
			"stage" : "LIMIT_SKIP",
			"nReturned" : "60163",
			"executionTimeMillisEstimate" : "151.908",
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : "60163",
				"executionTimeMillisEstimate" : "147.511",
				"indexName" : "my_special_index",
				"direction" : "forward"
			}
		}
	}
}
```

i tried adding another index
```
db.mycollection.createIndex(
    { "field": 1, "obj1.id": 1, "obj1.until": -1, "obj2.id": 1, "obj3.status": 1},
    { name: "my_special_index_with_until" }
)
```

but the **find().explain()** still prefers the first index, and **updateMany()** doesn't seem to run any faster

so, my question is, what can i do to improve performance, i would expect these to run very fast

Thanks!

Kevin_Z · Answer

Hi

- What is the DocDB version used here?
- How many indexes for the collection? Update() operations will go faster with less indexes. For the collection you shared, a better index is filed (obj2.id, obj3.status, obj1.id)
- Is there a way you may add hint to force the index using?

From the DocBD doc: https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html#functional-differences.explain

```
Amazon DocumentDB emulates the MongoDB 4.0 API on a purpose-built database engine that utilizes a distributed, fault-tolerant, self-healing storage system. As a result, query plans and the output of explain() may differ between Amazon DocumentDB and MongoDB. Customers who want control over their query plan can use the $hint operator to enforce selection of a preferred index.
```

- From the MongoDB doc: https://www.mongodb.com/docs/manual/reference/method/db.collection.updateMany/ There seems to be a new feature in version 4.2.1 to add hint in the updateMany() function. See if this is available in the DocDB 4.0 version. Maybe not.
- is there is a way to bulkwrite for the same result using updateMany, and is that going to be faster?
- If you have support plan, I would suggest reach to us through a support case and our team can help investigate further.

jjpe · Answer

1. Try with bulkwrite 
2. DocumentDB is using write concern with majority so it may cost time to wait for the last reply

How to make updateMany() faster

Conteúdo relevante