我们如何提高查询速度?
我们在1-2 minutes
范围内大约有 100个消费者,它们执行以下查询。这些运行中的每个运行都代表一个消耗函数的运行。
TableQuery<T> treanslationsQuery = new TableQuery<T>()
.Where(
TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey",QueryComparisons.Equal,sourceDestinationPartitionKey),TableOperators.Or,TableQuery.GenerateFilterCondition("PartitionKey",anySourceDestinationPartitionKey)
)
);
此查询将产生大约 5000个结果。
完整代码:
public static async Task<IEnumerable<T>> ExecuteQueryAsync<T>(this CloudTable table,TableQuery<T> query) where T : ITableEntity,new()
{
var items = new List<T>();
TableContinuationToken token = null;
do
{
TableQuerySegment<T> seg = await table.ExecuteQuerySegmentedAsync(query,token);
token = seg.ContinuationToken;
items.AddRange(seg);
} while (token != null);
return items;
}
public static IEnumerable<Translation> Get<T>(string sourceParty,string destinationParty,string wildcardSourceParty,string tableName) where T : ITableEntity,new()
{
var acc = CloudStorageaccount.Parse(Environment.GetEnvironmentVariable("conn"));
var tableclient = acc.CreatecloudTableclient();
var table = tableclient.GetTableReference(Environment.GetEnvironmentVariable("TableCache"));
var sourceDestinationPartitionKey = $"{sourceParty.ToLowerTrim()}-{destinationParty.ToLowerTrim()}";
var anySourceDestinationPartitionKey = $"{wildcardSourceParty}-{destinationParty.ToLowerTrim()}";
TableQuery<T> treanslationsQuery = new TableQuery<T>()
.Where(
TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey",anySourceDestinationPartitionKey)
)
);
var over1000Results = table.ExecuteQueryAsync(treanslationsQuery).Result.Cast<Translation>();
return over1000Results.Where(x => x.expireAt > DateTime.Now)
.Where(x => x.effectiveAt < DateTime.Now);
}
在这些执行期间,当有100个使用者时,如您所见,请求将聚集并形成峰值:
在这些高峰期间,请求通常需要1分钟以上的时间:
我们如何提高查询速度?