July 2, 2009 at 3:09 pm
I have this query which is taking more than 6 minutes to run for 1600 records, and trying to tune it. I couldn't take it below 3 minutes which is still awful, please let me know if you have any solution, code is below
select o_id,
isnull((select distinct case when value = 'Complete' then 'Yes' else 'No' end
from information i inner join labels l
on i.i_l_id = l.l_id
where i.i_r_id = r.r_id and l.code = 'REVIEW'
), 'No') as [CRD],
isnull((select distinct case when value = 'Complete:Full' then 'Yes' else 'No' end
from information i inner join labels l
on i.i_l_id = l.l_id
where i.i_r_id = r.r_id and l.code = 'REVIEW'
), 'No') as [CRF],
isnull((select distinct case when value = 'Complete:Lost'
then 'Yes' else 'No' end
from information i inner join labels l
on i.i_l_id = l.l_id
where i.i_r_id = r.r_id and l.code = 'REVIEW'
), 'No') as [CRL],
isnull((select distinct case when value = 'Complete:Other' then 'Yes' else 'No' end
from information i inner join labels l
on i.i_l_id = l.l_id
where i.i_r_id = r.r_id and l.code = 'REVIEW'
), 'No') as [Complete_other],
isnull((select distinct value
from information i inner join labels l
on i.i_l_id = l.l_id
where i.i_r_id = r.r_id and l.code = 'CONSENT'
), 'No') as [IC Done],
isnull((select distinct value
from information i inner join labels l
on i.i_l_id = l.l_id
where i.i_r_id = r.r_id and l.code = 'SCREEN'
), 'No') as [EC Done]
from organization o inner join monitoring m
on o.o_id = m.m_id
inner join schedule s
on s.s_id = m.m_id
inner join protocol p
on p.p_id = s.s_id
inner join study st
on st.st_id = p.pr_id
inner join participant pp
on pp.pp_id = p.p_pid_id
inner join step sp
on sp.sp_id = pp.pp_sp_id
inner join report r
on r.r_id = pp.pp_rr_id
where o_id in (1, 2)
July 2, 2009 at 3:23 pm
I am looking at your where clause "where o_id in (1, 2)". If this eliminates a lot records it would make sense to use a common table expression to extract a smaller with initial query before you go through the pain of all of the joins.
Look up WITH common_table_expressions in BOL for complete syntax. But essentially select from the initial table to rows and columns you need and then run a select statement with all of the joins to get your result. Also the o_id column should at least have an index on it.
Alan
July 2, 2009 at 3:32 pm
You also have six correlated subqueries with select distincts. Each of those queries has to be run for each row.
It would help if you could provide use with the information based on the first two articles I have referenced below in my signature block regarding as for assistance and performance tuning.
July 2, 2009 at 3:39 pm
we are still using sql server 2000. the big culprit in the code is the derived columns; can we rewrite the code so that the information table is joined only ones
it is a very big table
Thanks a lot
July 2, 2009 at 3:40 pm
My very first thought was WOW, there sure are a lot of sub-selects in that..
I think you are trying to accomplish too much in a single query, you have like the equivalent of 18 tables in this query..
Take a look at this:
SELECT DISTINCT
r.r_id, value, code
INTO #Temp
FROM information i inner join labels l
on i.i_l_id = l.l_id
INNER JOIN organization o
ON i.i_r_id = r.r_id
inner join monitoring m
on o.o_id = m.m_id
inner join schedule s
on s.s_id = m.m_id
inner join protocol p
on p.p_id = s.s_id
inner join study st
on st.st_id = p.pr_id
inner join participant pp
on pp.pp_id = p.p_pid_id
inner join step sp
on sp.sp_id = pp.pp_sp_id
inner join report r
on r.r_id = pp.pp_rr_id
WHERE o_id in (1, 2)
AND l.code IN ( 'REVIEW', 'CONSENT', 'SCREEN' )
select o_id,
CASE WHEN trev.value = 'Complete' then 'Yes' else 'No' end AS [CRD],
CASE WHEN trev.value = 'Complete:Full' then 'Yes' else 'No' end AS [CRF],
CASE WHEN trev.value = 'Complete:Lost' then 'Yes' else 'No' end AS [CRF],
CASE WHEN trev.value = 'Complete:Lost' then 'Yes' else 'No' end AS [Complete_other],
ISNULL(tcon.value, 'No') as [IC Done],
ISNULL(tscn.value, 'No') as [EC Done]
from organization o inner join monitoring m
on o.o_id = m.m_id
inner join schedule s
on s.s_id = m.m_id
inner join protocol p
on p.p_id = s.s_id
inner join study st
on st.st_id = p.pr_id
inner join participant pp
on pp.pp_id = p.p_pid_id
inner join step sp
on sp.sp_id = pp.pp_sp_id
inner join report r
on r.r_id = pp.pp_rr_id
LEFT OUTER JOIN #temp trev
ON r.r_id = trev.r_id AND trev.code = 'REVIEW'
LEFT OUTER JOIN #temp tcon
ON r.r_id = tcon.r_id AND tcon.code = 'CONSENT'
LEFT OUTER JOIN #temp tscn
ON r.r_id = tscn.r_id AND tscn.code = 'SCREEN'
where o_id in (1, 2)
It is REALLY rough but I think it might do what you want and should run substantially faster..
CEWII
July 2, 2009 at 3:43 pm
With 2000 the reules are the same but applied differently:
1. Create a table variable to insert to org data
2. Add this table to the join
2. Add the information table to the join to stop using all of the sub selects as Lynn said
Should be quicker
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply