sql-server - tipos - subquery sql server ejemplos

¿Cómo puedo forzar una subconsulta para que se desempeñe tan bien como una tabla#temp?

Question

Reitero la pregunta formulada por Mongus Pong. ¿Por qué utilizar una tabla temporal sería más rápido que una consulta anidada? Lo que no tiene una respuesta que funcione para mí.

La mayoría de nosotros, en algún momento, descubrimos que cuando una consulta anidada alcanza una cierta complejidad, debe dividirse en tablas temporales para mantener su rendimiento. Es absurdo que esta pueda ser la forma más práctica de avanzar y significa que estos procesos ya no pueden convertirse en una vista.

Estoy convencido de que debe haber una configuración simple de queryplan para que el motor simplemente enrute cada subconsulta a su vez, trabajando desde adentro hacia afuera. No hay duda de cómo puede hacer que la subconsulta sea más selectiva (lo que a veces lo hace con mucho éxito) y no hay posibilidad de subconsultas correlacionadas. Solo la pila de datos que el programador pretende devolver por el código autocontenido entre los corchetes.

Es común para mí encontrar que simplemente cambiar de una subconsulta a una #table lleva el tiempo de 120 segundos a 5. Esencialmente, el optimizador está cometiendo un error importante en alguna parte. Claro, puede haber muchas maneras de consumir mucho tiempo para convencer al optimizador de que mire las tablas en el orden correcto, pero incluso esto no ofrece ninguna garantía. No estoy pidiendo el tiempo de ejecución ideal de 2 segundos aquí, solo la velocidad que la tabla temporal me ofrece dentro de la flexibilidad de una vista .

Nunca he publicado aquí antes, pero he estado escribiendo SQL durante años y he leído los comentarios de otras personas experimentadas que también acaban de aceptar este problema y ahora me gustaría que el genio apropiado diera un paso adelante y dijera sugerencia especial es X ...

Answer 1

Hay algunas explicaciones posibles de por qué ve este comportamiento. Algunos comunes son

1. La subconsulta o CTE pueden ser reevaluados repetidamente.
2. La materialización de resultados parciales en una tabla #temp puede forzar un orden de combinación más óptimo para esa parte del plan al eliminar algunas opciones posibles de la ecuación.
3. La materialización de los resultados parciales en una tabla #temp puede mejorar el resto del plan al corregir las estimaciones de cardinalidad deficientes.

El método más confiable es simplemente usar una tabla #temp y materializarla usted mismo.

En su defecto con respecto al punto 1, consulte Brindar una sugerencia para forzar la materialización intermedia de CTE o tablas derivadas . El uso de TOP(large_number) ... ORDER BY menudo puede hacer que el resultado se coloque en lugar de reevaluarlo repetidamente.

Incluso si eso funciona, sin embargo, no hay estadísticas en la cola.

Para los puntos 2 y 3 tendría que analizar por qué no estaba obteniendo el plan deseado. Posiblemente, volver a escribir la consulta para usar predicados sargable, o actualizar las estadísticas podría obtener un mejor plan. Si falla, puede intentar usar sugerencias de consulta para obtener el plan deseado.

Answer 2

No creo que haya una sugerencia de consulta que indique al motor que enrute cada subconsulta a su vez.

Existe la sugerencia de consulta de OPTION (FORCE ORDER) que obliga al motor a realizar las UNIONES en el orden especificado, lo que podría convencerlo para que logre ese resultado en algunos casos. Esta sugerencia a veces resultará en un plan más eficiente para una consulta compleja y el motor sigue insistiendo en un plan subóptimo. Por supuesto, generalmente se debe confiar en el optimizador para determinar el mejor plan.

Idealmente, habría una sugerencia de consulta que le permitiría designar un CTE o subconsulta como "materializada" o "tabla temporal anónima", pero no la hay.

Answer 3

Otra opción (para futuros lectores de este artículo) es utilizar una función definida por el usuario. Las funciones de múltiples enunciados (como se describe en Cómo compartir datos entre procedimientos almacenados ) parecen forzar al SQL Server a materializar los resultados de su subconsulta. Además, le permiten especificar claves primarias e índices en la tabla resultante para ayudar al optimizador de consultas. Esta función se puede utilizar en una declaración de selección como parte de su vista. Por ejemplo:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
BEGIN
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   RETURN
END

CREATE VIEW SalesData As
SELECT * FROM SalesByStore(''6380'')

Answer 4

Tras encontrar este problema, descubrí que (en mi caso) SQL Server estaba evaluando las condiciones en un orden incorrecto, porque tenía un índice que podía usarse ( IDX_CreatedOn en TableFoo ).

SELECT bar.*
FROM
    (SELECT * FROM TableFoo WHERE Deleted = 1) foo
    JOIN TableBar bar ON (bar.FooId = foo.Id)
WHERE
foo.CreatedOn > DATEADD(DAY, -7, GETUTCDATE())

Logré solucionarlo forzando a la subconsulta a usar otro índice (es decir, uno que se usaría cuando la subconsulta se ejecutara sin la consulta principal). En mi caso, cambié a PK, que no tenía sentido para la consulta, pero permitió que las condiciones de la subconsulta se evaluaran primero.

SELECT bar.*
FROM
    (SELECT * FROM TableFoo WITH (INDEX([PK_Id]) WHERE Deleted = 1) foo
    JOIN TableBar bar ON (bar.FooId = foo.Id)
WHERE
foo.CreatedOn > DATEADD(DAY, -7, GETUTCDATE())

Filtrar por la columna Deleted fue realmente simple y filtrar los pocos resultados por CreatedOn después fue incluso más fácil. Pude averiguarlo comparando el Plan de ejecución real de la subconsulta y la consulta principal.

Una solución más intrincada (y no realmente recomendada) es forzar a la subconsulta a ejecutarse primero limitando los resultados usando TOP , sin embargo, esto podría llevar a problemas extraños en el futuro si los resultados de la subconsulta exceden el límite (siempre se podría establecer El limite a algo ridículo). Desafortunadamente, TOP 100 PERCENT no se puede usar para este propósito ya que SQL Server simplemente lo ignora.