tipos - cómo estructurar un índice para el grupo por en Servidor Sql

mantenimiento de indices sql server

Question

La siguiente consulta simple lleva mucho tiempo (varios minutos) para ejecutarse.

Tengo un índice:

create index IX on [fctWMAUA] (SourceSystemKey, AsAtDateKey)

SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
FROM [fctWMAUA] (NOLOCK) AS [t0]
WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)
GROUP BY [t0].[SourceSystemKey]

Las estadísticas son las siguientes:

• lecturas lógicas 1827978
• lecturas físicas 1113
• leer ahead 1806459

Tomar exactamente la misma consulta y reformatearla de la siguiente manera me da estas estadísticas:

• lecturas lógicas 36
• lecturas físicas 0
• leer aheads 0

Tarda 31ms en ejecutarse.

SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 1
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 2
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]
/* AND SO ON TO 9 */

¿Cómo puedo hacer un índice que haga el grupo rápidamente?

Answer 1

 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]

No necesita agrupar por un campo fijo.

De cualquier manera prefiero la primera oración. Puede ser que voy a reemplazar el

 WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)

para algo como

 WHERE SourceSystemKey BETWEEN 1 AND 9

o

 WHERE SourceSystemKey >= 1 AND SourceSystemKey <= 9

si SourceSystemKey es un número entero. Pero no creo que cause un gran cambio.

Lo que probaré primero es reconstruir las estadísticas y reconstruir todos los índices de la tabla y esperar un tiempo. La reconstrucción no es instantánea, dependerá de qué tan ocupado esté el servidor, pero esta oración está bien estructurada para que el índice sea utilizado por el optimizador.

Saludos.

Answer 2

Intenta decirle a SQL Server que use el índice:

...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

Asegúrese de que las estadísticas de la tabla estén actualizadas.

UPDATE STATISTICS [fctWMAUA]

Para obtener mejores respuestas, active el plan de presentación para ambas consultas:

SET SHOWPLAN_TEXT ON

y agrega los resultados a tu pregunta.

También puede escribir la consulta sin un GROUP BY. Por ejemplo, puede usar un exclusivo LEFT JOIN excluyendo las filas con fechas anteriores:

select cur.SourceSystemKey, cur.date
from fctWMAUA cur
left join fctWMAUA next
    on next.SourceSystemKey = next.SourceSystemKey
    and next.date > cur.date
where next.SourceSystemKey is null
and cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)

Esto puede ser sorprendentemente rápido, pero no creo que pueda vencer a la UNIÓN.

Answer 3

¿Has intentado crear otro índice solo en la columna SourceSystemKey? El alto número de lecturas lógicas cuando usa esa columna en su cláusula where me hace pensar que está haciendo un análisis de índice / tabla. ¿Podría ejecutar el plan de ejecución en esto y ver si ese es el caso? El plan de ejecución también puede presentar una sugerencia de índice.

Answer 4

Es difícil de decir sin mirar un plan de ejecución, sin embargo, es posible que desee probar lo siguiente:

SELECT * FROM
(
    SELECT MAX(t0.AsAtDateKey) AS [Date], t0.SourceSystemKey AS SourceSystem
    FROM fctWMAUA (NOLOCK) AS t0
    GROUP BY t0.SourceSystemKey
)
WHERE SourceSystem in (1,2,3,4,5,6,7,8,9)

Es difícil de decir sin mirar un plan de ejecución, pero creo que lo que está sucediendo es que el servidor SQL no es lo suficientemente inteligente como para darse cuenta de que la cláusula WHERE especificada está filtrando los grupos y no tiene ningún efecto en los registros incluidos para cada grupo. Tan pronto como SQL Server se da cuenta de esto, puede usar algunas búsquedas de índices más inteligentes para calcular los valores máximos (que es lo que está sucediendo en su segunda consulta).

Solo una teoría, pero podría valer la pena intentarlo.

Answer 5

Use HAVING en lugar de WHERE, de modo que el filtrado ocurra DESPUÉS de que se haya producido la agrupación:

SELECT MAX(AsAtDateKey) AS [Date], SourceSystemKey AS SourceSystem
FROM fctWMAUA (NOLOCK)
GROUP BY SourceSystemKey
HAVING SourceSystemKey in (1,2,3,4,5,6,7,8,9)

Tampoco me interesa especialmente la cláusula IN, especialmente cuando podría reemplazarse con "<10" o "Entre 1 y 9", que se utilizan mejor por índices ordenados.

Answer 6

He encontrado que la mejor solución es la siguiente. Imita la versión de unión de la consulta y se ejecuta muy rápido.

40 lecturas lógicas y un tiempo de ejecución de 3 ms.

SELECT [t3].[value]
FROM [dimSourceSystem] AS [t0]
OUTER APPLY (
    SELECT MAX([t2].[value]) AS [value]
    FROM (
        SELECT [t1].[AsAtDateKey] AS [value], [t1].[SourceSystemKey]
        FROM [fctWMAUA] AS [t1]
        ) AS [t2]
    WHERE [t2].[SourceSystemKey] = ([t0].[SourceSystemKey])
    ) AS [t3]