optimization - Extremely Slow Query which contains multiple Sub-Queries SQL Server 2008 R2 -
i working on query audit of form. there several pages worth of questions audit. when form filled out answers stored in 2 tables in following fashion:
table 1: smsmir.obsv obs episode no | form usage | quest | answer | ... 123456789 | admission | question 1 | yes | ... 123456789 | admission | question 2 | 150 | ... ... table 2: smsdss.qoc_vst_summ qoc episode no | ht ind | wt ind | adv dir | ... 123456789 | 1 | 1 | 0 | ... ...
table 1: smsmir.obsv obs
stores information in vector, each question there row. table 2: smsdss.qoc_vst_summ qoc
stores answers in row, there 1 row per visit. table 3 same, there 1 row per visit id.
my query starts off collecting visit ids
stored in table , passed off next set in order answer questions. reason pull visit ids other table because visit start , end date stored. table looks this:
table 3: smsdss.bmh_plm_ptacct_v pav episode no | adm date | ... 123456789 | 2013-08-01 | ... ...
my desired output of following:
episode no | question 1 | question 2 | ht ind | wt ind | adv dir | ... 123456789 | 1 | 1 | 1 | 1 | 0 | ... ...
in above table 1 represent question answered using case statement , 0 indicate not answered. query has been re-written , producing correct results, horrendously slow, 40 records took 53 minutes 36 seconds. query unfinished 7 columns returning, have expand have total of 65 columns.
the reason have sub-queries answers stored in vector, each row question , answer since want show answers , questions in columns sub-query. there better way speed up?
here query:
-- query perform audit of admission assessment , -- other required questions nursing informatics ----------------------------------------------------------------------- -- variable declaration , initialization. declaring start , -- end date user can change parameters , audit -- inpatient admission assessments time period declare @sd datetime declare @ed datetime set @sd = '2013-08-01' set @ed = '2013-08-01' -- query 1 -- query creates table house visit id numbers -- going included inside of admission assessment audit -- table declaration ################################################## declare @t1 table ( visit_id varchar(20)) -- #################################################################### -- these items going inserted table insert @t1 -- column selection select a.ptno_num -- db(s) used (select distinct ptno_num smsdss.bmh_plm_ptacct_v adm_date between @sd , @ed , plm_pt_acct_type = 'i') --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++// ----------------------------------------------------------------------- -- query two. query take visit id's query 1 , run -- them through set of rules decide wheather or not addmisison -- assessment done ----------------------------------------------------------------------- -- column selection select distinct obs.episode_no [visit id] -- case statement, if preferred language not 'null' consider -- complete , score 1 else consider incomplete , score 0 , case when qoc.prim_lng not null 1 else 0 end [pref lang complete?], qoc.ht_chtd_ind [ht ind], qoc.wt_chtd_ind [wt ind], qoc.adv_dir_ind [adv directive] -- seperate select statement used here because results of -- admission consent stored in vector, necessary -- make selection list, here value of 1 = yes , -- 0 = no , case when obs.episode_no not in (select episode_no smsmir.obsv form_usage = 'admission') 0 else 1 end [admit assessment done], case when obs.episode_no not in (select episode_no smsmir.obsv form_usage = 'admission' , obsv_cd_ext_name = 'admission consent signed:') 0 else 1 end [admit consent signed?] -- db(s) used --------------------------------------------------------- smsmir.obsv obs join smsdss.qoc_vst_summ qoc on obs.episode_no = qoc.episode_no join @t1 t1 on obs.episode_no = t1.visit_id -- filters ------------------------------------------------------------ t1.visit_id = obs.episode_no group obs.episode_no, qoc.prim_lng, qoc.ht_chtd_ind, qoc.wt_chtd_ind, qoc.adv_dir_ind, obs.obsv_cd_ext_name --##################################################################### -- end report ...[]...[]...[]
you notice using not in
clause, reason if question not asked or answered, there no record, not null
, if not use that, person have other things done if not particular item, excluded final result set.
if need make clarification please let me know.
** query actual execution plan xml ** query exec actual xml
thank you
smsmir.obsv
view union
-s 155,569,000 row table , 15,375,000 row one.
the execution plan shows these tables scanned 42 times.
the vast majority of because of default poor cardinality estimates table variables meaning inappropriate choice of nested loops. replacing #temp
table should solve issue.
also using pivot
technique instead of individual sub queries can reduce further. there may additional optimisations can applied in terms of adding missing indexes can try , let me know timings , execution plan?
declare @sd datetime = '2013-08-01'; declare @ed datetime = '2013-08-01'; create table #t1 ( visit_id varchar(20) unique clustered ) insert #t1 select distinct ptno_num smsdss.bmh_plm_ptacct_v adm_date between @sd , @ed , plm_pt_acct_type = 'i' option (recompile); obs (select episode_no, max(case when form_usage = 'admission' 1 end) [admit assessment done], max(case when form_usage = 'admission' , obsv_cd_ext_name = 'admission consent signed:' 1 end) [admit consent signed?] smsmir.obsv form_usage = 'admission' group episode_no) select obs.episode_no [visit id], case when qoc.prim_lng not null 1 else 0 end [pref lang complete?], qoc.ht_chtd_ind [ht ind], qoc.wt_chtd_ind [wt ind], qoc.adv_dir_ind [adv directive], isnull(obs.[admit assessment done], 0) [admit assessment done], isnull(obs.[admit consent signed?], 0) [admit consent signed?] smsdss.qoc_vst_summ qoc join #t1 on #t1.visit_id = qoc.episode_no left join obs on obs.episode_no = qoc.episode_no drop table #t1
Comments
Post a Comment