High RU (1400 times) utilization on native query invoked with CosmosRequestOptions and cosmosAsyncContainer.queryItems than @Query way of invocation

[ViswanathVeerappan]

I have a cosmos container which is hierarchically partitioned.

`@Data
@NoArgsConstructor
@container(hierarchicalPartitionKeyPaths = {"/partitionIdLevel1", "/partitionIdLevel2"})
public class UniqueTransactionInbound {

private String id;

private String originalMessageId;

private String messageTimestamp;

private String timeWindow;

private String partitionIdLevel1;

private String partitionIdLevel2;

private Integer ttl;

private Boolean testFlag;

private String hopUuid;

private Integer scenarioVersion;

private String insertTimestamp;

}`

Also, here is a snippet of the indexing policy we have,

{ "indexingMode": "consistent", "automatic": true, "includedPaths": [ { "path": "/partitionIdLevel1/?" }, { "path": "/partitionIdLevel2/?" }, { "path": "/testFlag/?" }, { "path": "/messageTimestamp/?" }, { "path": "/hopUuid/?" }, { "path": "/scenarioVersion/?" }, { "path": "/timeWindow/?" } ], "excludedPaths": [ { "path": "/*" }, { "path": "/\"_etag\"/?" } ], "fullTextIndexes": [] }

I was trying the following queries

SELECT DISTINCT VALUE c.scenarioVersion FROM c WHERE c.partitionIdLevel1 = @partitionIdLevel1 AND c.testFlag = @testFlag AND (c.messageTimestamp BETWEEN @rangeStart AND @rangeEnd)

SELECT c.timeWindow, COUNT(1) as count FROM c WHERE c.partitionIdLevel1 = @partitionIdLevel1 AND c.hopUuid = @hopUuid GROUP BY c.timeWindow

In the @Query way of executing the query, even though the first level of hierarchical partition was used in the where clause, the CosmosRequestOptions built from the CosmosTemplate did not populate it. We have an issue created in the past for this. (#45737)

I also implemented a custom implementation which would populate the first level of the hierarchical partition key on the CosmosRequestOptions.

PartitionKey partitionKey = new PartitionKeyBuilder().add(partitionIdLevel1).build(); CosmosQueryRequestOptions options = new CosmosQueryRequestOptions(); options.setPartitionKey(partitionKey); return cosmosAsyncContainer.queryItems(sqlQuerySpec, options, rclass) .collectList().block();

The custom implementation was consuming 1400 times the RU that was consumed by the @Query. Can someone please help understand the reason for the higher RU consumption and optimize the implementation?

[github-actions]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @kushagraThapar @pjohari-ms @TheovanKraay.

[ViswanathVeerappan]

`SELECT DISTINCT VALUE c.scenarioVersion FROM c WHERE c.partitionIdLevel1 = @partitionIdLevel1 AND c.testFlag = @testFlag AND (c.messageTimestamp BETWEEN @rangeStart AND @rangeEnd)

SELECT c.timeWindow, COUNT(1) as count FROM c WHERE c.partitionIdLevel1 = @partitionIdLevel1 AND c.hopUuid = @hopUuid GROUP BY c.timeWindow`

First query consumes about 98.84 RUs with @query whereas 16436.85 RUs with the custom implementation

Second query consumes about 6.24 RUs with @query whereas 10842.2 RUs with the custom implementation.

The number of records under consideration for the partitionIdLevel1 is 155,862. Overall the container has about 1,510,566 records.

[FabianMeiswinkel]

@ViswanathVeerappan - sorry - I am not able to fully understand when exactly the problem occurs - but my hunch is that when you run the query manually you are not adding WHERE-conditions for the partial hierarchical partition key to the query statement (but just filter via CosmosQueryRequestOptions.setPartitionKey with a partial HPK)? If so, just add the where statement for the partial partition key - this leads to more efficient query execution in the service.

[ViswanathVeerappan]

@FabianMeiswinkel Thanks for responding. The hierarchical partition key is added as a part of the WHERE clause and it is also set on the CosmosQueryRequestOptions via setPartitionKey.

SELECT c.timeWindow, COUNT(1) as count FROM c WHERE c.partitionIdLevel1 = @partitionIdLevel1 AND c.hopUuid = @hopUuid GROUP BY c.timeWindow

Here is a snippet for the above query,

`List parameters =
List.of(new SqlParameter("@partitionIdLevel1", partitionIdLevel1),
new SqlParameter("@hopUuid", hopUuid));

SqlQuerySpec querySpec = new SqlQuerySpec(HOURLY_AGGREGATION_QUERY, parameters);
PartitionKey partitionKey = new PartitionKeyBuilder().add(partitionIdLevel1).build();

return fetchHourlyAggregationCounts(querySpec, partitionKey,
    UniqueTransactionInbound.class)`

public List<HourlyAggregationCounts> fetchHourlyAggregationCounts(SqlQuerySpec sqlQuerySpec, PartitionKey partitionKey, Class<T> clazz) { String cosmosContainer = this.cosmosOperations.getContainerName(clazz); CosmosAsyncContainer cosmosAsyncContainer = cosmosAsyncClient.getDatabase(this.databaseProperties.getDatabase()) .getContainer(cosmosContainer); CosmosQueryRequestOptions options = new CosmosQueryRequestOptions(); options.setPartitionKey(partitionKey); return cosmosAsyncContainer.queryItems(sqlQuerySpec, options, HourlyAggregationCounts.class) .collectList().block(); }