Which of these two functions will have better performance to find the length of the input string?
这两个函数中哪一个具有更好的性能来查找输入字符串的长度?
PATINDEX ('%%{terminator}%%',inputString)
or
LEN(inputString)
1 个解决方案
#1
3
The length of a string in sql server is often stored separately from the text. You don't have to walk the string to know how long it is. It's just a lookup, or a lookup followed by walking from the end of the string (to check trailing spaces), rather than the beginning. Assuming you were able to construct a PATINDEX to return the last index of the string, that would still require you to walk the string to evaluate the length. I suppose you could construct a scenario where PATINDEX was faster, but only if you have strings that consist mostly of trailing space.
sql server中字符串的长度通常与文本分开存储。您不必走绳子就知道它有多长。它只是一个查找或查找,然后从字符串的末尾(检查尾随空格)开始,而不是从头开始。假设您能够构造PATINDEX以返回字符串的最后一个索引,那么仍然需要您遍历字符串以评估长度。我想你可以构建一个PATINDEX更快的场景,但前提是你的字符串主要由尾随空格组成。
So LEN() wins here in any way you can think of: semantics (best communicates meaning of what you're doing), correctness (getting the PATINDEX right would be tricky), and performance.
因此,LEN()以你能想到的任何方式在这里获胜:语义(最好地传达你正在做的事情的意义),正确性(让PATINDEX正确的将是棘手的)和性能。
#1
3
The length of a string in sql server is often stored separately from the text. You don't have to walk the string to know how long it is. It's just a lookup, or a lookup followed by walking from the end of the string (to check trailing spaces), rather than the beginning. Assuming you were able to construct a PATINDEX to return the last index of the string, that would still require you to walk the string to evaluate the length. I suppose you could construct a scenario where PATINDEX was faster, but only if you have strings that consist mostly of trailing space.
sql server中字符串的长度通常与文本分开存储。您不必走绳子就知道它有多长。它只是一个查找或查找,然后从字符串的末尾(检查尾随空格)开始,而不是从头开始。假设您能够构造PATINDEX以返回字符串的最后一个索引,那么仍然需要您遍历字符串以评估长度。我想你可以构建一个PATINDEX更快的场景,但前提是你的字符串主要由尾随空格组成。
So LEN() wins here in any way you can think of: semantics (best communicates meaning of what you're doing), correctness (getting the PATINDEX right would be tricky), and performance.
因此,LEN()以你能想到的任何方式在这里获胜:语义(最好地传达你正在做的事情的意义),正确性(让PATINDEX正确的将是棘手的)和性能。