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 inclause, 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

Popular posts from this blog

java - activate/deactivate sonar maven plugin by profile? -

python - TypeError: can only concatenate tuple (not "float") to tuple -

java - What is the difference between String. and String.this. ? -