soporta - El parámetro SQL ralentiza la consulta

optimizar consultas lentas mysql

Question

Tengo una consulta que estoy usando con SQL Server 2008R2 a través de ADO.NET. Cuando uso una cláusula LIKE en línea, funciona en menos de un segundo, con 5 filas devueltas desde 2 millones. Si declaro el parámetro como lo hago en .NET al comienzo de la consulta en SSMS, lleva una eternidad.

Es la misma consulta, pero parametrizada.

El primero (que funciona bien) es (que funciona bien):

;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY PK_ID) AS RowNum  
    FROM list..List_Data ld 
    WHERE Name IS NOT NULL  AND 
    Postcode LIKE ''SW14 1xx%'' 
) SELECT * FROM Results_CTE 

El segundo que toma para siempre es:

declare @postcode varchar(10) = ''SW14 1xx''
;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY PK_ID) AS RowNum  
    FROM list..List_Data ld
    WHERE Name IS NOT NULL  AND 
    Postcode LIKE @postcode +''%'' 
) SELECT * FROM Results_CTE 

Creo que esto tiene algo que ver con el funcionamiento interno de SQL Server, pero realmente no tengo ni idea.

Answer 1

Esto parece un problema causado por la detección de parámetros: durante la compilación del plan, SQL Server "olfatea" los valores de los parámetros actuales y los utiliza para optimizar la consulta. El problema más común que esto puede causar es si la consulta se ejecuta con un valor de parámetro "impar" la primera vez que se ejecuta / compila, en cuyo caso el plan de consulta se optimizará para ese valor de parámetro, el parámetro olfateo puede causar todos los demás problemas

En su caso, si la consulta se ejecuta con un valor vacío / nulo para @postcode , la consulta está utilizando una cláusula LIKE ''%'' , que es muy probable que cause un análisis de tabla cuando se está utilizando un comodín LIKE al comienzo del filtrar. Parece que el plan se ejecutó / compiló inicialmente con un parámetro @postcode vacío, o SQL Server de alguna manera se confunde con este parámetro.

Hay un par de cosas que puedes probar:

1. Marque la consulta para la recompilación y luego vuelva a ejecutar la consulta con un valor no nulo para @postcode .
2. "Enmascarar" el parámetro para tratar de evitar el olfateo de parámetros,

por ejemplo:

declare @postcode varchar(10) = ''SW14 1xx''
declare @postcode_filter varchar(10) = @postcode + ''%''
-- Run the query using @postcode_filter instead of @postcode

Aunque esta consulta parece que debería comportarse exactamente de la misma manera, he descubierto que SQL Server trata los parámetros de formas extrañas: las reglas sobre cuándo se usa exactamente la detección de parámetros pueden ser un tanto extrañas en el momento, por lo que es posible que desee jugar. con variaciones en lo anterior.

Answer 2

Puede usar optimize for que la consulta parametrizada use el mismo plan de ejecución que el que tiene un parámetro específico:

SELECT * 
FROM Results_CTE 
OPTION (OPTIMIZE FOR (@postcode = ''SW14 1xx''))

Answer 3

Utilizar

SELECT * 
FROM Results_CTE 
OPTION (RECOMPILE)

SQL Server no detecta el valor de la variable, por lo que no tiene idea de qué tan selectiva será y probablemente asuma que la consulta devolverá significativamente más filas de lo que realmente es y le dará un plan optimizado para eso.

En su caso, estoy bastante seguro de que, en el buen plan, encontrará que utiliza un índice no agrupado que no cubre para evaluar el predicado PostCode y algunas búsquedas para recuperar las columnas faltantes, mientras que en el plan incorrecto (como adivina la consulta devuelve un mayor número de filas) lo evita a favor de un escaneo de tabla completo.

Answer 4

Estaba buscando en Google problemas potenciales con SqlCommand.Parameters.Add () en C #, y encontré esta página. Sé que esta es una publicación de SQL Server, pero otros pueden encontrarla a través de Google y puede ayudarles con C #.

Para mí, ninguna de las respuestas anteriores funcionó, así que probé con otro método.

En lugar de:

cmd.Parameters.Add(new SqlParameter("@postcode", postcode));

Usé esto en su lugar:

// Replace SqlDbType enumeration with whatever SQL Data Type you''re using.
cmd.Parameters.Add("@postcode", SqlDbType.VarChar).Value = postcode;

Y no olvides el espacio de nombres:

using System.Data;

¡Espero que esto ayude a alguien!