sql-server - por - substring de derecha a izquierda sql server

SQL Server usa una alta CPU cuando busca dentro de cadenas nvarchar

Question

Mira el siguiente ejemplo. Muestra que la búsqueda dentro de una cadena Unicode (nvarchar) es casi ocho veces más mala que la búsqueda dentro de una cadena Varchar. Y a la par con las conversiones implícitas. Buscando una explicación para esto. O una forma de buscar dentro de las cadenas nvarchar de manera más eficiente.

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

Buscando una explicación para esto.

NVarchar es de 16 bits y las reglas de comparación de Unicode son mucho más complicadas que ASCII: los caracteres especiales para los distintos idiomas que se admiten al mismo tiempo requieren un procesamiento más.

Answer 2

Es porque las reglas de clasificación de los caracteres Unicode son más complicadas que las reglas de clasificación para los caracteres que no son Unicode.

Pero, las cosas no son tan simples como varchar vs nvarchar

También debe considerar la intercalación de SQL frente a la intercalación de Windows como se explica here .

SQL Server realiza comparaciones de cadenas de datos no Unicode definidos con una intercalación de Windows mediante el uso de reglas de clasificación Unicode. Debido a que estas reglas son mucho más complejas que las reglas de clasificación no Unicode, son más intensivas en recursos. Por lo tanto, aunque las reglas de clasificación de Unicode son a menudo más caras, generalmente hay poca diferencia en el rendimiento entre los datos de Unicode y los datos que no son de Unicode definidos con una intercalación de Windows.

Como se indica, para Windows Collation, SQL Server utilizará las reglas de clasificación de Unicode para varchar, por lo tanto, no tendrá ninguna ganancia de rendimiento.

Aquí hay un ejemplo:

-- 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.

Como puede ver, no hay diferencia entre varchar y nvarchar con la intercalación de ventanas.

Nota: parece que las intercalaciones de SQL solo se incluyen con fines heredados y no deben utilizarse para proyectos nuevos (incluso si parecen tener un mejor rendimiento).

Answer 3

He visto problemas similares en SQL Server. Hubo un caso en el que estaba usando consultas parametrizadas, y mi parámetro era UTF-8 (predeterminado en .net) y el campo era varchar (por lo tanto, no utf-8). Terminé con la conversión de cada valor de índice a utf-8 solo para hacer una búsqueda de índice simple. Esto podría estar relacionado en que la cadena completa podría traducirse a otro conjunto de caracteres para hacer la comparación. También para nvarchar, "a" sería lo mismo que "á", lo que significa que hay mucho más trabajo para determinar si 2 cadenas son iguales en Unicode. Además, es posible que desee utilizar la indexación de texto completo, aunque no estoy seguro de si eso resuelve su problema.

Answer 4

Mi conjetura es que LIKE se implementa utilizando un algoritmo O (n ^ 2) en lugar de un algoritmo O (n); probablemente tendría que ser para que el % inicial funcione. Dado que la cadena Unicode es el doble de larga, eso parece ser consistente con sus números.

Answer 5

Una búsqueda LIKE %% se implementa como> y <. Ahora más el número de filas, más el tiempo de procesamiento, ya que SQL no puede hacer un uso efectivo de las estadísticas para %% como búsquedas.

Además, la búsqueda en Unicode requiere almacenamiento adicional y, junto con las complicaciones de la intercalación, normalmente no sería tan eficiente como la simple búsqueda de varchar de vainilla. La búsqueda de intercalación más rápida, como ha observado, es la búsqueda de intercalación binaria.

Este tipo de búsquedas se adaptan mejor a la búsqueda de texto completo o se implementan mediante FuzzyLookup con una tabla hash en memoria en caso de que tengas mucha RAM y una tabla bastante estática.

HTH