October 26, 2004 at 3:31 pm
query running longer
October 26, 2004 at 3:37 pm
Can you post DDL, including indexes and constraints, as well as the execution plan for the query?
--
Adam Machanic
whoisactive
October 26, 2004 at 3:41 pm
logical read23456
October 27, 2004 at 7:29 am
I like to move common subexpressions to subqueries. I find it easier to read, but I don't know that this version would be any faster. Your statistics show that the greatest number of reads are in the visits table. Any way to filter those rows better?
SELECT patient_sys, visit_sys, dob, name_full, reg_num, COUNT(patient_sys) as patient_count,
gen_age, room, bed, unit
FROM (
SELECT od.patient_sys, od.visit_sys, CONVERT(NVARCHAR, p.dob, 101) as dob,
p.name_last + ', ' + p.name_first as name_full, p.reg_num, od.patient_sys,
CASE
WHEN month_diff >= 25
THEN STR(month_diff/12) + ' yo '
WHEN month_diff = 24 THEN
CASE WHEN DAY(p.dob) <= DAY(GETDATE())
THEN '2 yo ' ELSE '1 yo ' END
WHEN month_diff >= 2 THEN
CASE WHEN DAY(p.dob) <= DAY(GETDATE())
THEN STR(month_diff) + ' mo '
ELSE STR(month_diff - 1) + ' mo ' END
WHEN DATEDIFF(DD, p.dob, GETDATE()) >= 0
THEN STR(DATEDIFF(DD, p.dob, GETDATE())) +' do '
ELSE '0 do '
END + p.gender as gen_age,
v.room, v.bed, v.unit
FROM (
SELECT patient_sys, name_last, name_first, dob, reg_num, gender,
DATEDIFF(MM, dob, GETDATE()) as month_diff
FROM patients WITH (NOLOCK)
) p
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
) x
GROUP BY patient_sys, visit_sys, dob, name_full, reg_num, gen_age, room, bed, unit
October 27, 2004 at 8:38 am
Is it my end, or have the original poster's posts mysteriously disappeared?
--
Adam Machanic
whoisactive
October 28, 2004 at 12:04 am
No ... not your end .... Same here, too!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply