问题描述
在.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)的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！