日期时间的Azure Cosmos DB UDF严重减慢了查询速度 [英] Azure Cosmos DB UDF for date time is seriously slowing down query

问题描述

我们的日期存储为:"2/22/2008 12:00:00 AM".我们需要过滤结果，以便两次之间获取文档.

Our dates are stored as: "2/22/2008 12:00:00 AM". We need to filter results so that we get documents between two times.

如果我们比较两个查询，一个查询使用UDF，另一个不使用UDF，则使用UDF的查询要慢几个数量级.

If we compare two queries, one using a UDF and the other not, the one with the UDF is orders of magnitude slower.

使用:

SELECT DISTINCT 
    c.eh, c.wcm, w AS wt 
FROM 
    c 
    JOIN w IN c.wt 
WHERE 
    (udf.toValue(w.ced) BETWEEN udf.toValue('03/02/2023') AND udf.toValue('09/02/2023'))
    AND w.ty = 'FW' 
OFFSET 0 
LIMIT 10

并且没有:

SELECT DISTINCT 
    c.eh, c.wcm, w AS wt 
FROM 
    c 
    JOIN w IN c.wt 
WHERE 
    w.ty = 'FW' 
OFFSET 0 
LIMIT 10

这是UDF:

function userDefinedFunction(datestr){
    return new Date(datestr).getTime();
}

根据此处的第二个答案(由一位从事Cosmos工作的MS员工组成)，我应该能够直接进行比较:

According to the second answer here (by one of the MS employees who works on Cosmos) I should just be able to do a direct compare:

(w.ced BETWEEN '03/02/2023' AND '09/02/2023')

但是返回0个结果.我对Cosmos非常陌生.如何优化此查询?我应该补充，wt/ced上已经有一个同上符号.

But that returns 0 results. I'm extremely new to Cosmos. How can this query be optimized? I should add, there is already an idex on wt/ced.

推荐答案

通常来说，如果可以使用系统函数代替UDF，则性能会更好.

Generally speaking if you can use a system function instead of a UDF the performance will be much better.

但是，在这种情况下，您应该以一致的格式保留日期，以避免每次都要使用UDF对其进行修正.

In this scenario however, you should persist your dates in a consistent format to avoid having to use UDF's the fix them up each time.

我建议查看我们的最佳工作方法日期.

如果您能够以一致的格式将日期存储在Cosmos DB中(推荐的格式遵循ISO 8601 UTC标准)，则可以避免在查询本身内转换格式(这会很昂贵).如果需要，任何数据格式转换都应在应用程序内完成.例如，在运行查询之前将日期"03/02/2023"转换为ISO 8601 UTC标准(然后在查询中使用此文本).

If you are able to store the dates in a consistent format in Cosmos DB (recommended format is following the ISO 8601 UTC standard), then you can avoid needing to convert the format within the query itself (this will be costly). Any data formatting conversions, if needed, should be done within the app. For example, converting the date "03/02/2023" to ISO 8601 UTC standard before running the query (and then use this text in the query).

希望这会有所帮助.

这篇关于日期时间的Azure Cosmos DB UDF严重减慢了查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！