Orden de SQL Server 2008 ROW_NUMBER() por Clustered PK lento

Orden de SQL Server 2008 ROW_NUMBER() por Clustered PK lento

Tengo una consulta simple a continuación que une 3 de mis tablas. Debido al hecho de que las instrucciones OFFSET y FETCH no están disponibles en SQL Server 2008, implementé ROW_NUMBER() en uno de mis informes de pedidos paginados.

SELECT * FROM 
( 
    SELECT
      ROW_NUMBER() OVER ( ORDER BY OrderProductDetail.ID ) AS RowNum, 
      *
    FROM 
      Order JOIN 
      OrderProduct ON Order.ID = OrderProduct.OrderID JOIN
      OrderProductDetail ON OrderProduct.ID = OrderProductDetail.OrderProductID
    WHERE 
      Order.Date BETWEEN '2018-01-01 00:00:00.000' AND '2018-02-01 00:00:00.000'
) AS OrderDetailView
WHERE RowNum BETWEEN 1 AND 1000;

Con más de 16 millones de 3 millones de registros en la tabla, la consulta anterior tardó 1 minuto en completarse, los registros encontrados tienen un límite de 1000.

Sin embargo, si simplemente elimino RowNum en la cláusula WHERE, la consulta se completa en 3 segundos y se devuelve un total de 1700 registros. (También el mismo resultado si solo ejecuto la parte de Subconsulta)

SELECT * FROM 
( 
    SELECT
      ROW_NUMBER() OVER ( ORDER BY OrderProductDetail.ID ) AS RowNum, 
      *
    FROM 
      Order JOIN 
      OrderProduct ON Order.ID = OrderProduct.OrderID JOIN
      OrderProductDetail ON OrderProduct.ID = OrderProductDetail.OrderProductID
    WHERE 
      Order.Date BETWEEN '2018-01-01 00:00:00.000' AND '2018-02-01 00:00:00.000'
) AS OrderDetailView
  • Order.ID = PK agrupado único (Int)
  • Pedido.Fecha = Índice no agrupado (marca de tiempo)
  • OrderProduct.ID = PK agrupado único (Int)
  • DetalleProductoPedido.ID = PK agrupado único (Int)

Algunos otros casos de prueba que he realizado:

  1. ( ORDER BY Order.Date ) AS RowNumber> Fast
  2. ( ORDER BY Order.ID ) AS RowNumber> Rápido

Pregunta: ¿Cómo puedo mejorar el rendimiento?

Mostrar la mejor respuesta

¿Puede compartir el plan de ejecución real para ambas consultas e incluirlo en la pregunta? Utilice este brentozar.com/pastetheplan

Avatar MK_

¿Podría intentar agregar OPTION (RECOMPILE) al final de su SELECT solo como un experimento y hacernos saber los resultados?

@MK_ Probé la OPCIÓN pero no hay diferencias notables, gracias de todos modos

Avatar MK_

@PeirHwa.Soo: ¿podría complacerme y terminar la subconsulta en un CTE en lugar de que sea solo una subconsulta simple?

@MK_ Sí, también lo intenté, el mismo resultado :(

Prueba UPDATE STATISTICS ON OrderProduct WITH (FULLSCAN) (o sp_updatestats); las estimaciones parecen inestables en general. Si eso no ayuda, ¿qué sucede si agrega OPTION (HASH JOIN)? Esta es una falla en el optimizador donde el número conocido de filas (1000) se filtra incorrectamente a través de las estimaciones en las uniones, lo que hace que juzgue mal la eficiencia de los tipos de unión que está utilizando. Una opción (más limpia) que HASH JOIN si esto funciona es reemplazar la constante 1000 con una variable (y posiblemente agregar OPTIMIZE FOR (@nums = UNKNOWN).

@JeroenMostert [1] ACTUALIZAR ESTADÍSTICAS no ayuda. [2] ¡OPCIÓN (HASH JOIN) funcionó! Pero, ¿podría dar más detalles sobre la "constante 1000 con una variable"? Traté de declarar una var @limit=1000 pero no ayuda con el rendimiento.

Mi teoría era que WHERE RowNum BETWEEN 1 AND @limit OPTION (OPTIMIZE FOR (@limit UNKNOWN)) bloquearía las estimaciones de recuento de filas del optimizador y forzaría un tipo de unión diferente. En lugar de UNKNOWN, un valor definido pero grande como @limit = 1000000 también es una opción. Esto obligará al optimizador a actuar como si estuvieran involucradas muchas más filas, alejándolo de las uniones de bucle anidado. Para ser justos, todas estas opciones son un poco bleh, pero forzar explícitamente uniones hash para todo (incluso entre las tablas donde podría no ser apropiado algún día) es definitivamente la más bleh.

UPDATE STATISTICS Order WITH FULLSCAN;
UPDATE STATISTICS OrderProduct WITH FULLSCAN;
UPDATE STATISTICS OrderProductDetail WITH FULLSCAN;

Finalmente, la consulta volvió a la normalidad después de ejecutar los comandos anteriores, mi DBA no incluyó la opción FULLSCAN en el primer intento, por lo tanto, no funcionó.

¡Gracias @Jeroen Mostert!