子字符串与左右组合的 SQLServer 中的相对性能 [英] Relative Performance in SQLServer of Substring vs a Right-Left combo
问题描述
This is a performance based question, not a "I don't understand" or "best practice" question.
我在 SQLServer 数据库中有一个 varchar 字段，该字段保证长度超过 7 个字符.我需要提取一个由 varchar 中的第 2、3、4 和 5 个字符组成的 char(4) 字段.
I have a varchar field in a SQLServer database that is guaranteed to be longer than 7 chars. I need to extract a char(4) field consisting of the 2nd, 3rd, 4th and 5th chars in the varchar.
例如，如果 varchar 的值为 1234567890，我将查找 2345 部分.
For example if the varchar had the value 1234567890 I would be looking for the 2345 part.
使用子字符串而不是左右组合是否有性能优势?
Is there a performance benefit to using a substring over a right-left combo?
SELECT SUBSTRING(account,2,4) FROM payment
或
SELECT RIGHT(LEFT(account,5),4) FROM payment
我注意到在一个有 1,760,335 条记录的表上使用右向左键有轻微优势，但我不确定这是由于缓存查询还是类似原因.
I have noticed a slight advantage by using a right-left on a table with 1,760,335 records, but I am not sure if this is due to caching queries or the like.
更新我做了更多的功课.在这种情况下，似乎右左最终作为右子串执行.这是规则吗?或者它只是 SQLServer 决定给这只猫剥皮的方式?
UPDATE I've done a bit more homework. It seems that in this case the Right-Left is ultimately performed as a Right-Substring. Is this a rule? or is it just the way SQLServer decided to skin this particular cat?
推荐答案
+1 一个有趣的问题.您认为 SQL Server 可能会通过优化显着更改每个语句的评估可能是准确的；与您的评估相同，在如此大的集合中，SQL Server 可能能够比另一个更好地缓存一个查询.
+1 for an interesting question. Your assessment that SQL Server may significantly change each statement through optimization is probably accurate; same as your assessment that in such a large set SQL Server may be able to cache one query better than another.
我想到了另外两件可能(模糊地)相关的事情:
Two other things come to mind that might be (vaguely) relevant:
内存消耗；我很好奇 LEFT/RIGHT 组合是否会消耗更多的内存.理论上，需要存储第一个函数的返回值，以便将其传递给第二个函数，尽管可能会反复使用相同的寄存器.
Memory consumption; I would be curious if the LEFT/RIGHT combo consumes slightly more memory. In theory, the return value of the first function would need to be stored so that it could be passed into the second function, though the same register might be used over and over.
边界检查.varchar 基本上是一个指向 char[] 开头的指针，其中有 2 个额外的字节用于指示长度.这表明在通过索引访问值时需要执行某种边界检查，方法是查看这 2 个字节中包含的值，以确保它没有超出范围.
Bounds checking. A varchar is basically a pointer to the beginning of a char[] with 2 extra bytes for indicating length. This suggests that some sort of bounds checking would need to be performed when accessing a value by an index by looking at the value contained in those 2 bytes to make sure it wasn't out of range.
SQL Server 在使用 chars 和 varchars 发出超出字符串限制的请求时也非常宽容.以下代码将运行而不会出现任何错误.
SQL Server is also very forgiving when making requests outside the limits of the string with both chars and varchars. The following code will run without any errors.
DECLARE @Test varchar(50);
SET @Test = 'Hello World';
SELECT substring(@Test, 2, 4);
SELECT substring(@Test, 2000, 5000);
也会:
SELECT right(left(@test, 500), 400);
我的猜测是，对您问题的回答的解释与某些相关；不幸的是，我不知道您问题的答案.
My guess is that the explanation for the answer to your question lies in something related; unfortunately I don't know the answer to your question.
如果您使用更长的字符串或 char 与 varchar 获得相同的性能结果，我会很好奇.这些测试可以让您更深入地了解 SQL Server 的内部结构.
I would be curious if you got the same performance results using a longer string, or a char versus a varchar. Those tests could yield more insights into the internals of SQL Server.
这篇关于子字符串与左右组合的 SQLServer 中的相对性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
