I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:
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)
I have updated the statistics for all the tables touched by the query (excluding the
DataGatheringRuntime table which is quite big at ~
100GB) and have tried re-factoring the query using a
CTE but get the same execution plan and need some assistance.
The actual execution plan can be found here:
It's clear from the execution plan that the problem lies with the outer input on the
nested loop join specifically with the
lazy table spool following the
scan of the non-clustered
IX_Camunda_1 index on the
Document table but I have no idea how to tackle that issue and would appreciate any guidance.