LIKE operator consumes high RUs

Question

Hi

Our document in cosmos structure is as below. As you can see we have used wildcard % part of the values due to the reason that the source data can have any value in it in real time. we use LIKE operator in the query to lookup the matching document in cosmos. Doing so, the RU/s rate takes about more than 1800 per query which seems very expensive to our requirement.

Is there any better way could we improve this ?

We have partition set on the flowname and indexing policy applied on all fields under flowIdentify object.

{
    "flowname": "abc_test_flow",
    "decisioncode": -1,
    "flowIdentify": {
        "source": "%https://abc123.test.com/%",
        "sender": "ABC",
        "receiver": "XYZ",
        "msgtype": "%",
        "appref": "ABC123%",
        "testIndicator": "***",
        "dataFormat": "XML",
        "queue": "TEST.Q",
        "characterSet": "UTF-8"
    }
}

Answer 1

Hi @ShaktiSingh-MSFT

we process 3 to 4 million query / day and using LIKE operator per query gives around 1800 RU/s. This causes latency issue as well as incurs high cost

The Document model is given in my example.

Our max RU/s : 12000

Throughput : Auto scale

The document is partitioned on /flowname

Indexing policy:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/flowname/?"
        },
        {
            "path": "/decisioncode/?"
        },
        {
            "path": "/flowIdentify/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/*"
        },
        {
            "path": "/\"_etag\"/?"
        }
    ]
}