Azure Cosmos DB – 查询中分区键提供方式的差异?

huangapple go评论74阅读模式
英文:

Azure Cosmos DB - difference in how the partition key is supplied to query?

问题

a)

var queryString = $"SELECT TOP 100 * FROM c WHERE c.region = '{region}'";
var query = this.container.GetItemQueryIterator<Item>(new QueryDefinition(queryString));

b)

var queryString = "SELECT TOP 100 * FROM c";
var query = this.container.GetItemQueryIterator<Item>(new QueryDefinition(queryString), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(region) });

RU(请求单元)似乎是相同的,所以Cosmos DB 可能在内部优化查询并重写a)或b)的方式!

英文:

I am querying a Cosmos DB collection from my .NET Core app. Now I am wondering, if there is any difference (i.e.: better to do it one way vs the other) in how I supply the partition key to a query?

Below, region is my partition key.

a)

var queryString = $&quot;SELECT TOP 100 * FROM c WHERE c.region =&#39;{region}&#39;&quot;;
var query = this.container.GetItemQueryIterator&lt;Item&gt;(new QueryDefinition(queryString));

b)

var queryString = &quot;SELECT TOP 100 * FROM c&quot;;
var query = this.container.GetItemQueryIterator&lt;Item&gt;(new QueryDefinition(queryString), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(region) });

At least the RU (request units) seem to be the same, so maybe Cosmos DB internally optimizes the query anyway and rewrites a) to b) or vice versa?!

答案1

得分: 7

当您在查询中指定分区键时,Cosmos将查询路由到指定的分区,从而实现更高效的执行。

您可以按照您在问题中展示的两种方式来指定分区键,一种是将其添加到WHERE子句中如本文所述,或者可以使用QueryRequestOptions来明确指定分区键。

在幕后,数据库引擎会以相同的方式处理这两种情况,并且查询将直接针对您指定的分区执行,因此RU成本应该相似。

唯一的真正区别在于,在某些情况下,您使用的API的客户端SDK可能要求您使用QueryRequestOptions指定分区键,或者使用相关属性启用跨分区查询。在这种情况下,出于性能原因,您肯定希望指定分区键。

英文:

When you specify a partition key as part of your query, Cosmos will route the query to the specified partition, which results in more efficient execution.

You can specify the partition key in the two ways you show in your question, either by adding it to the WHERE clause as described in this article, or you can explicitly specify the partition key using the QueryRequestOptions.

Behind the scenes both of these will be handled the same way by the database engine and the query will execute against directly against the partition you specified, so the RU cost should be similar for both.

The only real difference is that in some cases the client SDK for the API you are using may require you to either specify the partition key using QueryRequestOptions or enable cross partition query using the relevant property. In this case you definitely want to specify the partition key for performance reasons.

答案2

得分: 2

有,有一些差异。

根据文档和最佳实践,第二种写法是编写查询的首选方式,因为CosmosDB SDK本身知道分区键,而第一种方式倾向于创建不必要的跨分区。

英文:

Yes there is a difference.

As per the documentation and best practices, 2nd one is the most preferred way of writing the query since the CosmosDB SDK itself is aware of the partition key while the first one tends to create the cross partition which is not necessary.

huangapple
  • 本文由 发表于 2020年1月3日 21:48:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/59579724.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定