index - update hint sql server

¿Cómo puedo optimizar/refactorizar una cláusula TSQL "ME GUSTA"?

Question

Tengo una tabla con aproximadamente 117000 registros. Necesito realizar una búsqueda que compruebe 3 campos separados para un patrón de cadena dado.

Mi cláusula where es la siguiente:

field1 LIKE ''%'' + @DESC + ''%''
OR field2 LIKE ''%'' + @DESC + ''%''
OR field3 LIKE ''%'' + @DESC + ''%''

Esto parece tomar alrededor de 24 segundos independientemente de la entrada ...

¿Hay una mejor manera de hacer esto? Menos de 10 (¡o 5!) Segundos serían mucho más preferibles.

Gracias por cualquier ayuda.

Answer 1

¿De verdad necesitas comenzar con un comodín? ¿Por qué? A menudo puede obligar a los usuarios a escribir al menos el primer carácter. Traigo esto a colación porque algunos desarrolladores solo usan el comodín como un hábito, no porque exista un requisito. En la mayoría de los casos, los usuarios podrán escribir el primer carácter a menos que el archivo guarde cadenas largas (como, por ejemplo, nombres de aeropuertos oficiales). De lo contrario, realmente necesita utilizar la indexación de texto completo, aunque el truco de KM con el revés es bastante bueno si no necesita el comodín al final.

Si puedes evitar hacer el performance matando cosas, entonces hazlo.

Answer 2

Cada vez que comienzas una búsqueda LIKE con un comodín, estás haciendo un escaneo. A menos que pueda restringir sus criterios de búsqueda para incluir el primer carácter (que puede no ser factible), deberá recurrir a la Búsqueda de texto completo.

Answer 3

Use la búsqueda de texto completo y CONTIENE . LIKE no se puede optimizar al buscar en el medio del campo, es decir. cuando la expresión LIKE comienza con un ''%'', por lo que siempre hará un escaneo completo de la tabla.

Answer 4

qué tal si

field1 + field2 + field3 LIKE ''%'' + @DESC + ''%''

o

CONTAINS(field1 + field2 + field3, @DESC)

Answer 5

Si bien estoy de acuerdo con la respuesta aceptada de que la indización de texto completo sería la mejor solución y de ninguna manera defiendo el uso de las principales búsquedas de comodines si deben realizarse, existen posibles pasos que pueden tomarse para que el rendimiento de ellos sea menor. malo.

Kalen Delaney en el libro " Microsoft SQL Server 2008 Internals " dice:

La intercalación puede hacer una gran diferencia cuando SQL Server tiene que ver casi todos los caracteres de las cadenas. Por ejemplo, mira lo siguiente:

SELECT COUNT(*) FROM tbl WHERE longcol LIKE ''%abc%''

Esto puede ejecutarse 10 veces más rápido o más con una intercalación binaria que una intercalación no binaria de Windows. Y con datos varchar , esto se ejecuta hasta siete u ocho veces más rápido con una intercalación de SQL que con una intercalación de Windows.

Answer 6

Intenté una posible solución. Antes de esta solución, incluso la consulta no devolvía el resultado y causaba un error de tiempo de espera de conexión.

Mi consulta tenía filtro de fecha y otros criterios. Todos los otros criterios fueron como búsqueda. Una palabra clave de columna estaba buscando como ''% abc%'' en la columna ntext y estaba haciendo un escaneo completo de la tabla.

Solución:

Divide la consulta en 2 partes. 1) Primera parte en CTE (Common Table Express) 2) Aplicar todos los criterios de búsqueda en CTE.

WITH SearchData(Column1,Column2,Column3,Column4,........)
    AS
    (
    SELECT Column1,Column2,Column3,Column4,...........
    FROM myTable1 WITH(NOLOCK) 
            INNER JOIN MyTable2 WITH(NOLOCK) 
                ON MyTable1.id = MyTable2.Id
    WHERE (MyTable1.CreationTime >= ''2014-04-27'' AND MyTable1.CreationTime <= ''2014-05-01'') 
 )

    SELECT DISTINCT top 250 Column1,Column2,Column3,Column4
    FROM SearchData
    WHERE   (ISNULL(Column1,'''') LIKE @Column1  +''%'' OR @Column1 IS NULL)
            and (Column2 LIKE @Column2+ ''%'' OR @Column2 IS NULL)
            ...
            ...
            ...
            ...
            AND (Column10 like ''%''+@Column10+''%'' or @Column10 IS NULL)
            AND @Column1+@Column2+@Column3+........@Column10 <> ''''  
            ORDER BY [CreationTime] DESC

Funcionó para mí

Answer 7

Si no puede usar FullTextSearch puede aumentar la velocidad en 10 veces. Has el siguiente:

1 Agregar campo calculado:

alter table TableName
add CalculatedColumnName as upper(Column1 + ''|'' + Column2...) collate Latin1_General_100_Bin2
persisted;

2 Agregue índice para el campo calculado:

create nonclustered index IDX_TableName_CalculatedColumnName
on TableName(CalculatedColumnName);

3 Cambia tu texto de consulta

select count(*)
from TableName
where CalculatedColumnName like ''%'' + upper(@ParameterValue) + ''%'' collate Latin1_General_100_Bin2

Fuente: http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server