sql server - datos - SELECT TOP es lento, independientemente de ORDER BY

order by sql server ejemplos

Question

Tengo una consulta bastante compleja en SQL Server que se ejecuta en una vista, en la forma:

SELECT *
   FROM myview, foo, bar 
   WHERE shared=1 AND [joins and other stuff]
   ORDER BY sortcode;

El plan de consulta como se muestra arriba muestra una operación de Sort justo antes del SELECT final, que es lo que esperaría. Solo hay 35 registros coincidentes, y la consulta tarda menos de 2 segundos.

¡Pero si agrego TOP 30 , la consulta toma casi 3 minutos! Usar SET ROWCOUNT es igual de lento.

Mirando el plan de consulta, ahora parece ordenar los más de 2 millones de registros en myview antes de las combinaciones y filtros.

Esta "clasificación" se muestra en el plan de consulta como una Exploración de índice en el índice de sortcode de sortcode , una Búsqueda de índice agrupada en la tabla principal y un Bucle anidado entre ellos, todo antes de las combinaciones y los filtros.

¿Cómo puedo forzar a SQL Server a SORT justo antes de TOP , como ocurre cuando no se especifica TOP ?

No creo que la construcción de myview sea ​​el problema, pero por si acaso, es algo como esto:

CREATE VIEW myview AS
   SELECT columns..., sortcode, 0 as shared FROM mytable
   UNION ALL
   SELECT columns..., sortcode, 1 as shared FROM [anotherdb].dbo.mytable

El mytable local tiene unos pocos miles de registros, y mytable en la otra base de datos en la misma instancia de MSSQL tiene unos pocos millones de registros. Ambas tablas tienen índices en sus respectivas columnas de sortcode .

Answer 1

Y así comienza el desafortunado juego de "tratar de ser más astuto que el optimizador (porque no siempre se sabe mejor)".

Puede intentar colocar las partes de filtrado en una subconsulta o CTE:

SELECT TOP 30 *
FROM
   (SELECT *
   FROM myview, foo, bar 
   WHERE shared=1 AND [joins and other stuff]) t
ORDER BY sortcode;

Lo que puede ser suficiente para forzarlo a filtrar primero (pero el optimizador se vuelve "más inteligente" con cada lanzamiento y, a veces, puede ver a través de estos chanchullos). O puede que tenga que ir tan lejos como para poner este código en un UDF . Si escribe el UDF como una función con valores de tabla de múltiples estados, con el filtro dentro, y luego consulta ese UDF con su TOP x / ORDER BY , ha forzado bastante el orden de consulta (porque SQL Server actualmente no puede optimizar) UDF de múltiples estados).

Por supuesto, pensándolo bien, presentar el UDF es solo una forma de ocultar lo que realmente estamos haciendo: crear una tabla temporal, usar una consulta para rellenarla (según los filtros DÓNDE), luego otra consulta para encontrar el TOP x de la tabla de temperatura