tabla - Consulta de SQL Server: rápido con literal pero lento con variable

sql server print console

Question

Tengo una vista que devuelve 2 entradas de una tabla usando un CTE. Si consulto la vista de esta manera, se ejecuta en menos de un segundo

SELECT * FROM view1 WHERE ID = 1

Sin embargo, si consulto la vista de esta manera, tardaré 4 segundos.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id

Revisé los 2 planes de consulta y la primera consulta realiza una búsqueda de índice agrupada en la tabla principal que devuelve 1 registro y luego aplica el resto de la consulta de vista a ese conjunto de resultados, mientras que la segunda consulta realiza un análisis de índice que es devolviendo unos 3000 registros en lugar de solo el que me interesa y luego filtrando el conjunto de resultados.

¿Hay algo obvio que me falta para tratar de obtener la segunda consulta para utilizar Index Seek en lugar de un análisis de índice? Estoy usando SQL 2008, pero todo lo que hago debe ejecutarse también en SQL 2005. Al principio pensé que era algún tipo de problema de detección de parámetros, pero obtengo los mismos resultados incluso si borro el caché.

Answer 1

Cuando SQL comience a optimizar el plan de consulta para la consulta con la variable, coincidirá con el índice disponible en la columna. En este caso, había un índice, por lo que SQL pensó que escanearía el índice buscando el valor. Cuando SQL hizo el plan para la consulta con la columna y un valor literal, podría ver las estadísticas y el valor para decidir si debería escanear el índice o si una búsqueda sería correcta.

El uso de la sugerencia de optimización y un valor le dice a SQL que "este es el valor que se usará la mayor parte del tiempo, así que optimice para este valor" y un plan se almacena como si se usara este valor literal. Al usar la sugerencia de optimización y la sub-sugerencia de UNKNOWN le dice a SQL que usted no sabe cuál será el valor, SQL examina las estadísticas de la columna y decide qué, buscar o escanear, será mejor y hace el plan en consecuencia.

Answer 2

DECLARAR @id INT = 1

SELECCIONAR * FROM View1 WHERE ID = @id

Hacer esto

DECLARAR @sql varchar (max)

SET @ sql = ''SELECCIONAR * FROM View1 WHERE ID ='' + CAST (@id como varchar)

EXEC (@sql)

Resuelve tu problema

Answer 3

En mi caso, el tipo de columna de la tabla DB se definió como VarChar y en el parámetro parametrizado tipo de consulta se definió como NVarChar, esto introdujo CONVERT_IMPLICIT en el plan de ejecución real para que coincida con el tipo de datos antes de comparar y que fue el culpable del rendimiento de la cerda, 2 segundos frente a 11 segundos . El solo hecho de corregir el tipo de parámetro hizo una consulta parametrizada tan rápido como la versión sin parámetros.

Espero que esto pueda ayudar a alguien con un problema similar.

Answer 4

Me encontré con este mismo problema yo mismo y resultó ser un índice que faltaba que implicaba una unión (izquierda) en el resultado de una subconsulta.

select *
from foo A
left outer join (
  select x, count(*)
  from bar
  group by x
) B on A.x = B.x

Se agregó un índice llamado bar_x para bar.x

Answer 5

Me encontré con este problema yo mismo con una vista que corría <10ms con una asignación directa ( DONDE UtilAcctId = 12345 ), pero se tomó más de 100 veces más con una asignación variable ( WHERE UtilAcctId = @UtilAcctId ).
El plan de ejecución para este último no fue diferente de si hubiera ejecutado la vista en toda la mesa.

Mi solución no requirió toneladas de índices, sugerencias de optimizador o una actualización de estadísticas largas.

En cambio, convertí la vista en una User-Table-Function donde el parámetro era el valor necesario en la cláusula WHERE. De hecho, esta cláusula WHERE se anidó 3 consultas profundas y todavía funcionaba y estaba de vuelta a la velocidad <10ms.

Eventualmente cambié el parámetro para que fuera un TYPE que sea una tabla de UtilAcctIds (int). Entonces puedo limitar la cláusula WHERE a una lista de la tabla. WHERE UtilAcctId = [parameter-List] .UtilAcctId. Esto funciona aún mejor. Creo que las funciones de la tabla de usuario están precompiladas.

Answer 6

Probablemente se deba a que en el caso del parámetro, el optimizador no puede saber que el valor no es nulo, por lo que necesita crear un plan que devuelva los resultados correctos, incluso cuando lo esté. Si tiene SQL Server 2008 SP1, puede intentar agregar OPTION(RECOMPILE) a la consulta.

Answer 7

Puede agregar una hint OPTIMIZE FOR a su consulta, por ej.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id OPTION (OPTIMIZE FOR (@ID = 1))