2 Pregunta: La búsqueda CHARINDEX vs LIKE da un rendimiento muy diferente, ¿por qué?

pregunta creada en Fri, Sep 25, 2015 12:00 AM

Utilizamos Entity Frameworks para el acceso a la base de datos y cuando "pensamos" en la declaración LIKE, en realidad genera cosas de CHARINDEX. Entonces, aquí hay 2 consultas simples, después de que las simplifiqué para demostrar un punto en nuestro servidor determinado:

 
-- Runs about 2 seconds
SELECT * FROM LOCAddress WHERE Address1 LIKE '%1124%' 
-- Runs about 16 seconds
SELECT * FROM LOCAddress WHERE ( CAST(CHARINDEX(LOWER(N'1124'), LOWER([Address1])) AS int)) = 1

La tabla contiene alrededor de 100k registros en este momento. Address1 es el campo VarChar (100), nada especial.

Aquí hay un recorte de 2 planes de lado a lado. No tiene ningún sentido, muestra 50% y 50% pero los tiempos de ejecución son 1: 8 ingrese la descripción de la imagen aquí

Busqué en línea y el consejo general es usar CHARINDEX en lugar de LIKE. En nuestra experiencia es todo lo contrario. Mi pregunta es qué causó esto y cómo podemos solucionarlo sin cambiar el código.

    
8
  1. Revisa esta otra pregunta SO : posiblemente pueda usar anotaciones de datos para indicar EF que esta columna es un VARCHAR y provocar que se detenga interpretando esa columna como un nvarchar cuando realmente no es ...
    2015-09-28 15: 50: 13Z
2 Respuestas                              2                         

Responderé a mi propia pregunta, ya que fue difícil encontrar la respuesta correcta y el resultado del Plan de ejecución de SQL Server 2012 me señaló el problema. Como se ve en la pregunta original, todo se ve bien en la superficie. Esto es SQL Server 2008.

Cuando ejecuto la misma consulta en 2012, recibí una advertencia en la consulta CHARINDEX. El problema es que SQL Server tuvo que hacer la conversión de tipos. Address1 es VarChar y la consulta tiene N'1124 'que es Unicode o NVarChar. Si cambio esta consulta como tal:

 
SELECT * 
FROM LOCAddress 
WHERE (CAST(CHARINDEX(LOWER('1124'), LOWER([Address1])) AS int)) 

A continuación, se ejecuta igual que la consulta LIKE. Por lo tanto, la conversión de tipo causada por el generador de Entity Framework estaba causando este horrible impacto en el rendimiento.

    
6
2015-09-28 15: 48: 30Z
  1. También estaba a punto de mencionarlo, pero preferí señalar la perspectiva T-SQL de CHARINDEX y LIKE. Sin embargo, me alegra que haya resuelto su problema usted mismo :). ¡Debería marcar su respuesta como respuesta para futuros lectores!
    2015-09-25 19: 39: 27Z

Primero, como puede ver, ambas consultas son idénticas y ninguna puede usar el índice. CHARINDEX y LIKE realizan lo mismo con el comodín. Ej:% YourValue%. Sin embargo, el rendimiento varía cuando usas comodines como 'YourValue%'. En este caso, es probable que el operador LIKE tenga un rendimiento más rápido que CHARINDEX porque puede permitir una exploración parcial del índice. Ahora, en su caso, ambas consultas son iguales, pero el rendimiento es diferente debido a las siguientes razones posibles:

Estadísticas : SQL Server mantiene estadísticas para subcadena en columnas de cadena que son utilizadas por el operador LIKE pero no se pueden utilizar completamente para CHARINDEX. En ese caso, el operador LIKE trabajará más rápido que CHARINDEX. Puede forzar a SQL Server a usar el índice para CHARINDEX con sugerencias de tabla adecuadas

  

Ej: FROM LOCAddress WITH (INDEX (index_name))

Lea más Aquí , que en la sección " resumen de cadenas de pruebas " dice:

  

SQL Server 2008 incluye tecnología patentada para estimar la selectividad de las condiciones de LIKE. Construye un resumen estadístico de   Distribución de frecuencia de subcadenas para columnas de caracteres (una cadena   resumen). Esto incluye columnas de texto de tipo, ntext, char, varchar,   y nvarchar. Usando el resumen de la cadena, SQL Server puede con precisión   estimar la selectividad de las condiciones de LIKE whantes de que el patrón pueda tener   cualquier número de comodines en cualquier combinación.

    
4
2015-09-25 18: 54: 57Z
  1. LIKE utilizará los índices solo para las exploraciones de prefijo. Esto no tiene nada que ver con la selectividad. Una exploración de prefijo es efectivamente una búsqueda de rango que comienza con el prefijo y termina con la siguiente palabra en orden alfabético. Esto permite que el servidor use el índice para realizar el equivalente de where field>='YourValue' and field<'YourValuf'
    2015-09-28 15: 31: 16Z
fuente colocada aquí