Início / dba / Perguntas / 233722
Accepted
Fza
Asked: 2019-04-03 00:05:16 +0800 CST

Ingresso de loops aninhados caros do SQL Server e spool de tabela lento

  • 772

Estou tentando ajustar a consulta abaixo, que leva de 15 a 16 segundos, independentemente do valor passado como parâmetro, a consulta é:

select distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from datagatheringruntime dgr 
 inner join processentitymapping pem on pem.entityid = dgr.entityid 
 inner join document d on d.entityid = pem.entityid or d.unitofworkid = pem.processid
 left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE)

Eu atualizei as estatísticas para todas as tabelas tocadas pela consulta (excluindo a DataGatheringRuntimetabela que é bastante grande em ~ 100GB) e tentei refatorar a consulta usando um CTEmas obtive o mesmo plano de execução e preciso de alguma assistência.

O plano de execução real pode ser encontrado aqui:

https://www.brentozar.com/pastetheplan/?id=ByUVIqlFE

Está claro no plano de execução que o problema está na entrada externa nested loop joinespecificamente com o lazy table spoolseguinte do índice scannão clusterizado na tabela, mas não tenho ideia de como resolver esse problema e gostaria de receber qualquer orientação.IX_Camunda_1Document

insira a descrição da imagem aqui

sql-server t-sql

2 respostas

  1. Best Answer

    Eu tentaria remover a ORcláusula na junção entre documenteprocessingentitymapping

    Você poderia fazer isso comUNION

    SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr 
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid 
INNER JOIN document d on d.entityid = pem.entityid 
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
UNION
SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr 
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid 
INNER JOIN document d on d.unitofworkid = pem.processid
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE);

    A razão é que o carretel da mesa está alimentando o NESTED LOOPSoperador insira a descrição da imagem aqui

    E neste operador de loops aninhados está o ORpredicado. insira a descrição da imagem aqui

    Filtrando até que tenhamos 9 linhas restantes.

    Alterar o ORpara a UNIONdeve remover o spool, talvez seja necessário examinar a indexação após remover o arquivo OR.

    Índices que podem melhorar o desempenho após reescrever comUNION

    CREATE INDEX IX_EntityId
on document(EntityId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

CREATE INDEX IX_UnitOfWorkId
on document(UnitOfWorkId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

    Veja aqui outro exemplo sobre isso

    • 5

  2. Em vez de processar DataGatheringRuntime table which is quite big at ~100GB)várias vezes, processe-as uma única vez, colocando-as em #temptabela ouCTE

    Em seguida, remova Distinct. Se houver dados duplicados, encontre o motivo por trás dos dados duplicados e remova os dados duplicados escrevendo a consulta correta.

    Qual é o propósito DistincteUNION na mesma consulta?

    Create table #temp(entityid int,processid int)
select pem.entityid,pem.processid
from datagatheringruntime dgr 
 inner join processentitymapping pem on pem.entityid = dgr.entityid 
 where rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
 --OPTION(RECOMPILE)

 select d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from document d
left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where exists(select 1 from  #temp pem where  d.entityid = pem.entityid )

 UNION ALL

 select d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from document d
left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where exists(select 1 from  #temp pem where  d.unitofworkid = pem.processid )
    • 0

relate perguntas