help in fine tunning this query where is taking long time

[Machlink]

SELECT od.patient_sys,
od.visit_sys,
CONVERT(NVARCHAR, p.dob, 101),
p.name_last + ', ' + p.name_first,
p.reg_num,
COUNT(od.patient_sys),
(CASE
WHEN DATEDIFF(MM, p.dob, GETDATE())/12 >= 2 AND MONTH(p.dob) != MONTH(GETDATE()) THEN
CAST(DATEDIFF(MM, p.dob, GETDATE())/12 AS NVARCHAR) + ' yo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE())/12 >= 2 and DAY(p.dob) <= DAY(GETDATE()) THEN
CAST(DATEDIFF(MM, p.dob, GETDATE())/12 AS NVARCHAR) + ' yo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE())/12 >= 2 THEN
CAST(((DATEDIFF(MM, p.dob, getdate())/12) -1) AS NVARCHAR) + ' yo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE()) >= 2 AND DAY(p.dob) <= DAY(GETDATE()) THEN
CAST(DATEDIFF(MM, p.dob, GETDATE()) AS NVARCHAR) + ' mo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE()) >= 2 THEN
CAST((DATEDIFF(MM, p.dob, GETDATE()) - 1) AS NVARCHAR) + ' mo ' + p.gender
WHEN DATEDIFF(DD, p.dob, GETDATE()) >= 0 THEN
CAST(DATEDIFF(DD, p.dob, GETDATE()) AS NVARCHAR) +' do ' + p.gender
ELSE
'0 do ' + p.gender
END) as gen_age,
v.room,
v.bed,
v.unit
FROM patients p WITH (NOLOCK)
Join visits v WITH (NOLOCK)
ON v.patient_sys = p.patient_sys
JOIN default_devices d WITH (NOLOCK)
ON d.unit = v.unit
Inner LOOP JOIN orderdata od WITH (NOLOCK)
ON od.patient_sys = v.patient_sys
AND od.visit_sys = v.visit_sys
JOIN ordermaster om WITH (NOLOCK)
ON (od.order_sys = om.order_sys AND (om.orderflags & 1 = 1))
WHERE od.orderedby_sys = 400093
AND od.enteredby_sys <> 400093
AND od.cosignedby_sys IS NULL
AND (od.status > 99 or od.status in (30, 40, 50))
AND od.current_value = 1 -- MTR 4/7/03 - only count orders with current_value=1
AND (v.disch_date IS NULL OR v.disch_date > GETDATE() -300)
AND d.nurse_verify = 1
GROUP BY od.patient_sys,
od.visit_sys,
p.dob,
p.name_last + ', ' + p.name_first,
p.reg_num,
(CASE
WHEN DATEDIFF(MM, p.dob, GETDATE())/12 >= 2 AND MONTH(p.dob) != MONTH(GETDATE()) THEN
CAST(DATEDIFF(MM, p.dob, GETDATE())/12 AS NVARCHAR) + ' yo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE())/12 >= 2 and DAY(p.dob) <= DAY(GETDATE()) THEN
CAST(DATEDIFF(MM, p.dob, GETDATE())/12 AS NVARCHAR) + ' yo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE())/12 >= 2 THEN
CAST(((DATEDIFF(MM, p.dob, getdate())/12) -1) AS NVARCHAR) + ' yo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE()) >= 2 AND DAY(p.dob) <= DAY(GETDATE()) THEN
CAST(DATEDIFF(MM, p.dob, GETDATE()) AS NVARCHAR) + ' mo ' + p.gender
WHEN DATEDIFF(MM, p.dob, GETDATE()) >= 2 THEN
CAST((DATEDIFF(MM, p.dob, GETDATE()) - 1) AS NVARCHAR) + ' mo ' + p.gender
WHEN DATEDIFF(DD, p.dob, GETDATE()) >= 0 THEN
CAST(DATEDIFF(DD, p.dob, GETDATE()) AS NVARCHAR) +' do ' + p.gender
ELSE
'0 do ' + p.gender
END),
v.room,
v.bed,
v.unit

Thanks in advance




Table 'orderdata'. Scan count 141, logical reads 26257, physical reads 0, read-ahead reads 0.

 

[roswald]

One suggestion, to reduce the # of reads, would be to create an index in orderdata on orderedby_sys, enteredby_sys.
See if that helps

 

[Machlink]

I did no use it increase the logical read

Thanks

 

[SQLSister]

I've never heard of an inner loop join, but when I tested it with my own data, the execution plan for it made much slower than a plain old inner join. Is there some reason why a regular inner join won;t work?

Questions about posting. See faq183-874

 

[vongrunt]

LOOP is optimization hint - it tells optimizer to always perform nested loops for inner joins. Other options are HASH and MERGE.

This GROUP BY is damn bloated... can you GROUP only columns that are 100% unique together, then use derived table to resolve the rest? For example, entire CASE statement can be reconstructed from p.dob... and p.dob itself is CONVERTed for presentational purposes only.

 

[SQLSister]

Thank you vongrunt. Learn something new everyday. However, it looks as if this may still be the problem. BOL says that nested loops are most effective if you are combining a large indexed table with a small one. It goes onto say:

In large queries, however, nested loops joins are often not the optimal choice.

From the tablenames these look to me like two large tables.


Questions about posting. See faq183-874

 

[vongrunt]

I also thought about that - this JOIN is relatively deeply nested (patients -> visits -> orderdata). Plus all WHERE conditions are over joined tables, especially orderdata.