Как оптимизировать запрос (nested loops)?

Question

[BaJar]

Есть запрос, часть которого поедает очень много ресурсов. Есть ли способ оптимизивать сие чудо?

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  --жрёт ресурсы

последняя строка тормозит запрос
-редиска, которая мешает жить людям.

Answer 1

[d-stream]

я бы начал с некоего переосмысливания - что же он такого делает
думаю для понимания можно вынести сабселекты в джойнах во временные таблицы в памяти (@table)

ну и посмотреть на предмет distinct в главном селекте - есть большие подозрения что от него можно избавится сгруппировав значения

Comments

[unfilled]

"временные таблицы в памяти (@table)"
которые называются табличные переменные и до sql server 2016, емнип, ожидалось что в них будет 1 запись, а после 2016 - 100. Имхо, использовать табличные переменные - это в 99,(9)% случаев очень плохая идея

[d-stream]

unfilled, как показывает практика - при правильном приготовлении это очень и очень удобственно. По крайней мере в рамках широкого диапазона версий sql
На практике и миллионные количества строк приносят пользу в плане оптимизации. А уж в ситуациях "таблица-фильтр" которая набирается из кучки разлапистых отборов - просто "то что доктор прописал".

[unfilled]

d-stream, я бы с радостью посмотрел на такой пример, где табличная переменная с большим количеством сторону помогает запросу работать быстрее, но понимаю что это вряд ли возможно (nda, коммерческая тайна, всё дела).

[d-stream]

Примитивнейший пример:
скармливание репортеру десятка-двух датасетов, на входе с десяток параметров, которые дают отбор

так вот insert into @f from select1 ... (достаточно большой)

и далее кучка
select2 ... inner join @f
select3 ... inner join @f
select4 ... inner join @f
select5 ... inner join @f

работает эффективнее чем попытки делать

select2 ... inner join (select1...)
select3 ... inner join (select1...)
select4 ... inner join (select1...)
select5 ... inner join (select1...)

/ притом этот запрос еще более "управляем" и отлаживаем при изменении структуры параметров отбора (нет рисков упустить копипаст и т.п.)
--

зачастую, как оказалось замена cte на @table - тоже приносит положительный эффект

p.s. могу и боевые целостные примерчики накидать, но не хочу - мне лень.

[unfilled]

d-stream, если вы сначала выполняете сложный запрос и сохраняете результат в табличную переменную, а потом делаете пять разных запросов, используя полученный результат, вместо пяти выполнения того запроса - да, тут конечно может быть выигрыш. А если вы вместо переменной используете временную таблицу, у оптимизатора будет больше вариантов выполнить запросы более оптимально.
Пассаж про cte не понял - в SQL server, в отличие от postgres (хотя и там вроде в последних версиях есть варианты), cte всегда "инлайнятся", как вьюхи - вместо имени cte подставляется выражение и план строится для всего запроса целиком.
Боевые примерчики, конечно,хотелось бы, да... Жаль, что лень.

[d-stream]

да, тут конечно может быть выигрыш.

безусловно он есть по факту.

А если вы вместо переменной используете временную таблицу, у оптимизатора будет больше вариантов выполнить запросы более оптимально.

Как показала фактическая практика последних лет 15 - @table работает эффективнее #table/##table. И планировщик прекрасно с ними обращается.

Пассаж про cte не понял - в SQL server, в отличие от postgres (хотя и там вроде в последних версиях есть варианты), cte всегда "инлайнятся", как вьюхи - вместо имени cte подставляется выражение и план строится для всего запроса целиком.

Опять же фактическая практика показала, что во многих случаях join @table оказывается быстрее join cte (@table наполняется тем же что было в cte)

Answer 2

[unfilled]

Я бы начал с того, что убрал все подзапросы, джойнил таблицы напрямую. Скорее всего у этого не будет никакого результата, но читать будет проще.
Второе, что нужно сделать - это избавиться от этого:

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

Попробуйте выкинуть это в нормальную временную таблицу (не табличную переменную) и делать джойн с ней

Nested Loops далеко не всегда плохо, проблема с ним в этом месте в том, что sql server думает, что вернётся 70 тыщ строк (Estimated Number of Rows), а возвращается (Actual Number of Rows) на несколько порядков больше. Дайте ему временную таблицу по которой он сможет построить статистику и он, скорее всего, сам построит приемлемый план.

Если @StartDate и @EndDate - это переменные, а не параметры SP, попробуйте выполнить запрос в том виде, в котором он есть, но явно подсунув туда даты, без переменных. Если это хранимая процедура и это её параметры, попробуйте выполнить её после sp_recompile. Этот абзац актуален только в том случае, если запрос не всегда работает медленно.

Ну и от distinct надо избавляться, да.