How to optimize query (nested loops)?

Question

SQL

There is a request, part of which eats up a lot of resources. Is there a way to optimize this miracle?

select 
 distinct 
 d.DocumentID
 --,null
 , c.PatientRef
 , d.DocumentCreationDate as N'Дата створення документу'
    ,UserName as N'Користувач, який створив'
                ,DepartmentName as N'Відділ користувача'
    ,DocumentKindName as N'Група документу'
    ,DocumentTemplateName as N'Назва документу'
    ,DocumentDLC  as N'Останнє зберігання документу'
    ,case when DocumentApproveStatusRef like N'APR' then N'Так' else N'Ні' end as Approved 
  from Document d 
  join (select CourseID, Patientref from Course) c on c.CourseID = d.CourseRef
  join (select DocumentTemplateID, DocumentTemplateName, DocumentKindRef from DocumentTemplate) dt on dt.DocumentTemplateID = d.DocumentTemplateRef
  join (select UserLogin, UserDepartmentRef, UserName from Users) u  on u.UserLogin=d.DocumentUserRef
  join (select DepartmentID, DepartmentName from Department) de on de.DepartmentID=u.UserDepartmentRef 
  join (select DocumentKindCode, DocumentKindName from DocumentKind) dk on dk.DocumentKindCode=dt.DocumentKindRef
 join (select DocumentRef, DocumentNodeStaticGUIDRef, DocumentNodeValue from DocumentNode) dn1  on dn1.DocumentRef = d.DocumentID  and dn1.DocumentNodeStaticGUIDRef in (  
  'd2c08bd8-3e52-4926-bbdf-4c4d37c968ad',	
 --'a07b13b1-8575-4d5e-afac-3e2b8d6e8171',	
 'bd6947b3-cee1-456c-9fc5-12efad75dec2' 	 

 )
 join (select DocumentRef, DocumentNodeStaticGUIDRef, DocumentNodeValue from DocumentNode) dn2 on dn2.DocumentRef = (select T.[value].value ('.', 'int') from DocumentSettingXML.nodes('/DocumentSetting/PreviousDocumentRef') as T([value]))
 and dn1.DocumentNodeStaticGUIDRef = dn2.DocumentNodeStaticGUIDRef
 where 
 (select T.[value].value ('.', 'int') from DocumentSettingXML.nodes('/DocumentSetting/PreviousDocumentRef') as T([value])) is not null

and 
d.DocumentCreationDate between @StartDate and @EndDate
and DocumentTemplateRef in (2125)
and dn1.DocumentNodeValue = dn2.DocumentNodeValue  --жрёт ресурсы

the last line slows down the request - a
radish that prevents people from living.

Answer 1

I would start with some rethinking - what does he do,
I think, for understanding, you can move the subselects in joins to temporary tables in memory (@table)
and look at the distinct object in the main select - there are big suspicions that you can get rid of it by grouping the values