improve - Rendimiento de consultas de SQL Server: eliminar la necesidad de Hash Match(unión interna)

sql server performance monitoring

Question

Tengo la siguiente consulta, que está haciendo muy poco y es un ejemplo del tipo de combinaciones que estoy haciendo en todo el sistema.

select t1.PrimaryKeyId, t1.AdditionalColumnId
from TableOne t1
    join TableTwo t2 on t1.ForeignKeyId = t2.PrimaryKeyId
    join TableThree t3 on t1.PrimaryKeyId = t3.ForeignKeyId
    join TableFour t4 on t3.ForeignKeyId = t4.PrimaryKeyId
    join TableFive t5 on t4.ForeignKeyId = t5.PrimaryKeyId
where 
    t1.StatusId = 1
    and t5.TypeId = 68

Hay índices en todas las columnas de unión, sin embargo, el rendimiento no es excelente. La inspección del plan de consulta revela una gran cantidad de Hash Match (Inner Joins) cuando realmente quiero ver combinaciones de Nested Loop.

El número de registros en cada tabla es el siguiente:

select count(*) from TableOne

= 64393

select count(*) from TableTwo

= 87245

select count(*) from TableThree

= 97141

select count(*) from TableFour

= 116480

select count(*) from TableFive

= 62

¿Cuál es la mejor manera de mejorar el rendimiento de este tipo de consulta?

Answer 1

Primeros pensamientos:

1. Cambiar a EXISTS (cambia equi-join a semi-join)
2. Debe tener índices en t1.StatusId, t5.TypeId e INCLUDE t1.AdditionalColumnID

No me preocuparía por tu método de unión todavía ...

Personalmente, nunca he usado una sugerencia de ÚNETE. Solo funcionan para los datos, índices y estadísticas que tiene en ese momento. A medida que estos cambian, su sugerencia ÚNETE limita al optimizador.

select t1.PrimaryKeyId, t1.AdditionalColumnId
from
    TableOne t1
where 
    t1.Status = 1
    AND EXISTS (SELECT *
        FROM
          TableThree t3
          join TableFour t4 on t3.ForeignKeyId = t4.PrimaryKeyId
          join TableFive t5 on t4.ForeignKeyId = t5.PrimaryKeyId
        WHERE
          t1.PrimaryKeyId = t3.ForeignKeyId
          AND
          t5.TypeId = 68)
    AND EXISTS (SELECT *
        FROM
          TableTwo t2
        WHERE
          t1.ForeignKeyId = t2.PrimaryKeyId)

Índice para tableOne .. uno de

• (Status, ForeignKeyId) INCLUDE (AdditionalColumnId)
• (ForeignKeyId, Status) INCLUDE (AdditionalColumnId)

Índice para tableFive ... probablemente (typeID, PrimaryKeyId)

Edición: se unió a JOINS y EXISTS para que coincida con las correcciones de preguntas

Answer 2

SQL Server es bastante bueno optimizando consultas, pero también es conservador: optimiza las consultas para el peor de los casos. Una unión de bucle generalmente da como resultado una búsqueda de índice y una búsqueda de marcador para cada fila. Debido a que las uniones de bucle causan una degradación dramática para los conjuntos grandes, SQL Server no se atreve a usarlos a menos que esté seguro del número de filas.

Puede usar la forceseek consulta de forceseek para forzar una búsqueda de índice:

inner join TableTwo t2 with (FORCESEEK) on t1.ForeignKeyId = t2.PrimaryKeyId

Alternativamente, puede forzar una unión de bucle con la palabra clave de loop :

inner LOOP join TableTwo t2 on t1.ForeignKeyId = t2.PrimaryKeyId

Las sugerencias de consulta limitan la libertad de SQL Server, por lo que ya no puede adaptarse a las circunstancias cambiadas. Es una buena práctica evitar las sugerencias de consulta, a menos que exista una necesidad comercial que no pueda satisfacerse sin ellas.