DocumentDB:内置字符串函数(如UPPER)的性能影响 [英] DocumentDB: Performance impact of built-in string functions (like UPPER)

问题描述

在.NET应用程序中，我们使用DocumentDB SDK查询我们的Azure DocumentDB. 当我们意识到查询中的内置字符串函数似乎对性能有很大影响时，我们试图找到导致性能问题的原因.

In our .NET application we use the DocumentDB SDK to query our Azure DocumentDB. We were trying to find the cause of a performance problem when we realized that the built-in string functions in queries seems to have a big impact on performance.

我要粘贴从应用程序中获得的一些统计信息，但是我已经能够在这里的操场上复制情况: https://www.documentdb.com/sql/demo (单击沙箱选项卡)

I was going to paste some stats that I got from our application but I've been able to replicate the situation with the playground here: https://www.documentdb.com/sql/demo (click on the sandbox tab)

使用以下查询:

SELECT *
FROM food 
WHERE food.description="Babyfood, dessert, fruit pudding, orange, strained"

我得到:

并具有UPPER字符串功能:

SELECT *
FROM food 
WHERE UPPER(food.description)=UPPER("Babyfood, dessert, fruit pudding, orange, strained")

我得到:

绝对数字在这里并不重要，在我们的应用程序中，我们将UPPER应用于电子邮件字段，我们看到了很大的不同.没有UPPER的操作需要1秒，而没有UPPER的需要20秒！

The absolute numbers don't really matter here, in our application we apply UPPER on an email field and we see a big difference. Operation takes 1s with no UPPER vs 20s with it!

推荐答案

除少数例外，任何时候只要您对字段值使用函数，都无法使用索引，因此查询将成为全表扫描.解决此问题的最佳方法是将值存储在已经为UPPER的另一个字段中，并对此进行查询.另外，如果您可以将选择性更高的子句与UPPER()子句结合使用，则会获得更好的性能.

With a few exceptions, any time you use functions on field values it can't use indexes so the query becomes a full table scan. The best way around this is to store the values in another field already UPPER and query against that. Alternatively, if you can combine a more highly selective clause with an UPPER() clause you'll get better performance.

这篇关于DocumentDB:内置字符串函数(如UPPER)的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！