Slow update operation

SQL++
1

I try to update many documents (~4638) in query from 46000 docs. Execution time is 12 s !!! Very slow? How can I optimize query ?

UPDATE `cms-deploy` SET status = 'progress'
WHERE _scope='rolling' AND status = 'active'

I use index

CREATE INDEX `Index_status` ON `cms-deploy`(`status`) PARTITION BY hash(`_scope`)

Explain plan is:

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IntersectScan”,
“scans”: [
{
“#operator”: “IndexScan3”,
“index”: “Index_status”,
“index_id”: “12b8306ddcf40233”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “cms-deploy”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““active””,
“inclusion”: 3,
“low”: ““active””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan3”,
“index”: “adv_scope_type”,
“index_id”: “929c00ebc66d7d72”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “cms-deploy”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““rolling””,
“inclusion”: 3,
“low”: ““rolling””
}
]
}
],
“using”: “gsi”
}
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“keyspace”: “cms-deploy”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “(((cms-deploy._scope) = “rolling”) and ((cms-deploy.status) = “active”))”
},
{
“#operator”: “Clone”
},
{
“#operator”: “Set”,
“set_terms”: [
{
“path”: “(cms-deploy.status)”,
“value”: ““progress””
}
]
},
{
“#operator”: “SendUpdate”,
“alias”: “cms-deploy”,
“keyspace”: “cms-deploy”,
“namespace”: “default”
}
]
}
},
{
“#operator”: “Discard”
}
]
}

2

Try this

CREATE INDEX Index_statusONcms-deploy(status, _scope) PARTITION BY hash(_scope)

3 
CREATE INDEX Index_status ON cms-deploy (status, _scope) PARTITION BY hash(_scope)

Unfortunately unchanged, the query execution time is the same.
The Select query is fast (24ms). I think that updating takes a long time.
Are there ways to speed up document updating ?

4

It using IntersecScan try with USE INDEX. UPDATE uses Fetch the document. Check the profile timings where it taking that long.

UPDATE `cms-deploy`  USE INDEX (Index_status)
SET status = 'progress'
WHERE _scope='rolling' AND status = 'active'
5

I checked timings
“phaseTimes”: {
“authorize”: “1.752294ms”,
“fetch”: “1.436106212s”,
“filter”: “563.699067ms”,
“indexScan”: “22.378282ms”,
“instantiate”: “96.508µs”,
“parse”: “1.074622ms”,
“plan”: “269.012µs”,
“run”: “11.399121144s”,
“update”: “11.369011165s”
},

6

Do you have any recommendations?

7

You are using EE, and only mutating 4.5K documents update taking time. Note update is serial. Don’t have any recommendation.