SQL Server использует высокий процессор при поиске внутри строк nvarchar

Question

Посмотрите на следующий пример. Это показывает, что поиск в строке юникода (nvarchar) почти в восемь раз хуже, чем поиск в строке varchar. И наравне с неявными преобразованиями. Ищу объяснение этому. Или способ более эффективного поиска в строках nvarchar.

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.

Answer 1

Ищу объяснение этому.

NVarchar является 16-битным, а правила сравнения Unicode намного сложнее, чем ASCII - специальные символы для различных языков, которые поддерживаются в одно и то же время, требуют дополнительной обработки кавычек.

Answer 2

Я думаю, что LIKE реализован с использованием алгоритма O(n^2) в отличие от алгоритма O(n); это, вероятно, должно быть для ведущих % работать. Поскольку строка в Юникоде в два раза длиннее, это похоже на ваши цифры.

Answer 3

LIKE %% search реализован как> и <. Теперь больше количество строк, больше время обработки, так как SQL не может эффективно использовать статистику для %% подобных поисков.

Кроме того, поиск в Юникоде требует дополнительного хранилища и, наряду с усложнениями сортировки, обычно не так эффективен, как поиск в простом ванильном varchar. Самый быстрый поиск сопоставления, который вы наблюдали, - это поиск двоичного сопоставления.

Этот вид поиска лучше всего подходит для полнотекстового поиска или реализован с использованием FuzzyLookup с хэш-таблицей в памяти, если у вас много оперативной памяти и довольно статическая таблица.

НТН

Answer 4

Я видел похожие проблемы в SQL Server. Был случай, когда я использовал параметризованные запросы, и мой параметр был UTF-8 (по умолчанию в.net), а поле было varchar (поэтому не utf-8). В конечном итоге все значения индекса конвертировались в utf-8 просто для простого поиска индекса. Это может быть связано с тем, что вся строка может быть переведена в другой набор символов для сравнения. Также для nvarchar, "a" будет таким же, как "á", что означает, что там будет гораздо больше работы, чтобы выяснить, равны ли 2 строки в юникоде. Кроме того, вы можете использовать полнотекстовое индексирование, хотя я не уверен, решит ли это вашу проблему.

Answer 5

Это связано с тем, что правила сортировки символов Юникод сложнее, чем правила сортировки не-Юникод символов.

Но все не так просто, как varchar vs nvarchar

Вы также должны рассмотреть SQL Collation против Windows Collation, как описано здесь.

SQL Server выполняет сравнение строк данных не в Юникоде, определенных с помощью параметров сортировки Windows, используя правила сортировки Юникод. Поскольку эти правила намного сложнее, чем правила сортировки не в Юникоде, они более ресурсоемки. Таким образом, хотя правила сортировки в Юникоде часто стоят дороже, как правило, разница в производительности между данными Юникод и данными, не относящимися к Юникоду, определена с помощью параметров сортировки Windows.

Как уже говорилось, для Windows Collation SQL Server будет использовать правила сортировки в юникоде для varchar, поэтому вы не получите никакого выигрыша в производительности.

Вот пример:

-- Server default collation is Latin1_General_CI_AS
create table test
(
    testid int identity primary key,
    v varchar(36) COLLATE Latin1_General_CI_AS, --windows collation
    v_sql varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AS, --sql collation
    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_sql like '%abcd%' option (maxdop 1)
-- CPU time = 625 ms,  elapsed time = 620 ms.

-- search utf8 string
select COUNT(1) from test where v like '%abcd%' option (maxdop 1)
-- CPU time = 3141 ms,  elapsed time = 3389 ms.

-- search utf8 string using unicode (uses convert_implicit)
select COUNT(1) from test where v like N'%abcd%' option (maxdop 1)
-- CPU time = 3203 ms,  elapsed time = 3209 ms.

-- search unicode string
select COUNT(1) from test where nv like N'%abcd%' option (maxdop 1)
-- CPU time = 3156 ms,  elapsed time = 3151 ms.

Как видите, нет разницы между varchar и nvarchar с сортировкой окон.

Примечание. Похоже, что параметры сортировки SQL включены только для унаследованных целей и не должны использоваться для новых проектов (даже если они, кажется, имеют более высокую производительность).