SQL Server 在 nvarchar 字符串中搜索时使用高 CPU [英] SQL Server uses high CPU when searching inside nvarchar strings
问题描述
看看下面的例子.它表明在 unicode 字符串 (nvarchar) 中搜索几乎是在 varchar 字符串中搜索的八倍.与隐式转换相当.寻找对此的解释.或者更有效地在 nvarchar 字符串中搜索的方法.
Check out the following example. It shows that searching within a unicode string (nvarchar) is almost eight times as bad as searching within a varchar string. And on par with implicit conversions. Looking for an explanation for this. Or a way to search within nvarchar strings more efficiently.
use tempdb
create table test
(
testid int identity primary key,
v varchar(36),
nv nvarchar(36),
filler char(500)
)
go
set nocount on
set statistics time off
insert test (v, nv)
select CAST (newid() as varchar(36)),
CAST (newid() as nvarchar(36))
go 1000000
set statistics time on
-- search utf8 string
select COUNT(1) from test where v like '%abcd%' option (maxdop 1)
-- CPU time = 906 ms, elapsed time = 911 ms.
-- search utf8 string using unicode (uses convert_implicit)
select COUNT(1) from test where v like N'%abcd%' option (maxdop 1)
-- CPU time = 6969 ms, elapsed time = 6970 ms.
-- search unicode string
select COUNT(1) from test where nv like N'%abcd%' option (maxdop 1)
-- CPU time = 6844 ms, elapsed time = 6911 ms.
推荐答案
正在寻找对此的解释.
Looking for an explanation for this.
NVarchar 是 16 位,Unicode 比较规则比 ASCII 复杂得多 - 同时支持的各种语言的特殊字符需要引用更多处理.
NVarchar is 16 bit and Unicode comparison rules are a lot more complicated than ASCII - special chars for the various languages that are supported at the same time require quote some more processing.
这篇关于SQL Server 在 nvarchar 字符串中搜索时使用高 CPU的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!