sql-server - temporales - optimizar while sql server

¿Por qué una consulta parametrizada produce un plan de consulta mucho más lento que una consulta no parametrizada?

Question

En una base de datos de SQL Server 2005 estoy trabajando en esta consulta:

seleccionar *
de foo
unirse a la barra en bar.x = foo.x
Únete a baz en baz.y = foo.y
donde foo.x = 1000

tiene un plan de consulta muy diferente y más rápido que la siguiente versión parametrizada.

declare @ p0 int
set @ p0 = 1000
seleccionar *
de foo
unirse a la barra en bar.x = foo.x
Únete a baz en baz.y = foo.y
donde foo.x = @ p0

En mi caso particular, la versión con el literal se ejecuta en segundos. La versión parametrizada tarda 2-3 segundos. Esperaba que fueran idénticos dado que son la misma consulta.

¿Por qué están obteniendo diferentes planes de consulta?

¿Hay alguna manera de hacer que la versión parametrizada tenga el mismo rendimiento que la versión literal?

Aquí están los planes de consulta. Mi consulta real es bastante diferente de la que hice anteriormente, pero la única diferencia entre las dos consultas que produjeron estos planes es el parámetro. ¿Por qué reemplazar un literal con un resultado de parámetro en planes tan diferentes?

• Plan de consulta literal
• Plan de consulta parametrizado

Answer 1

Creo que estás teniendo problemas con el " olfateo de parámetros ". Básicamente, lo que esto significa es que SQL Server intenta usar tanta información como tiene para calcular un plan de ejecución óptimo para su consulta. En el caso de su primera consulta, tiene un valor constante que se conoce en tiempo de compilación, por lo que el motor puede optimizar directamente el plan de consulta para ese valor.

En el segundo caso, el hecho de que esté usando una variable enmascara ese valor del motor en el tiempo de compilación (¡podría pensar que debería ser capaz de resolverlo, pero en realidad tuve problemas similares con una expresión constante simple! ), lo que conduce a un rendimiento pobre.

Una manera de tratar de evitar esto sería envolver la consulta en un procedimiento almacenado que toma el parámetro directamente y luego lo aplica a la consulta, algo como esto:

CREATE PROCEDURE test
  @p0 int
AS
BEGIN
  select *
  from foo
  join bar on bar.x = foo.x
  join baz on baz.y = foo.y
  where foo.x = @p0
END

Esto debería permitir al optimizador "olfatear" con precisión el parámetro que utiliza y generar un plan de consulta óptimo para usted.

Answer 2

Parece que el planificador de consultas ha tomado una decisión en la consulta literal que se basa en la información que ya tiene. Tendría estadísticas que puede consultar de manera eficiente en función de la difusión de los datos proporcionados en su literal específico.

La consulta parametrizada ha elegido la consulta que cree que es más justa para todos los datos en su tabla, que notará que hay muchos bucles anidados (rendimiento = malo).

Tal vez podría tratar de ejecutar las herramientas de optimización de bases de datos en su base de datos para ver si algunos índices podrían ayudarlo aquí.

Específicamente en su consulta, intente esto:

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0
OPTION ( OPTIMIZE FOR (@p0 = 1000))

Pero sería cauteloso al hacer esto sin estar seguro de que los datos contenidos en esta consulta no cambiarán y que su consulta sobre este plan SIEMPRE será más eficiente.

Answer 3

Su punto de partida debe ser el generador de perfiles SQL. Ejecute ambos a través del generador de perfiles y vea cuál es el plan de consulta en cada caso ... luego actualice la pregunta para describir los dos planes.

Una cosa que creo que puede ser un problema es que si tiene una consulta parametrizada con un conjunto de valores, el optimizador puede ver algunas de las estadísticas / índices y elegir una forma de hacerlo, luego reutilizar ese plan para todas las consultas: a pesar de que no es particularmente apropiado para un conjunto diferente de valores. Del mismo modo, si el plan se determina cuando hay un conjunto de datos (por ejemplo, cuando una tabla es pequeña, lo que fomenta un escaneo de tabla) y luego se agregan cargas de datos, el plan puede no ser apropiado. Sin embargo, ninguno de estos afectaría una consulta que era mala como la primera consulta para la declaración preparada.

Answer 4

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.