sql server - SQL Query Optimization with Union Select -

i have query, returns 570 rows, runs 2m 35s. in sql query execute, in solution, gives timeout. how can optimize run under 1m, pref 30s.

select  [region] = region.firstname,         [patient] = patient.name,         [patientstatus] = accountrating.name,         [medicalaid] = accounttype.name,         [quoteamount] = (   select top 1 a.response                             dbo.questionnaire q                             join dbo.questionnairedefinition qrd                                 on qrd.questionnairedefinitionid = q.questionnairedefinitionid                                 , qrd.name = 'internal admin'                             left join questiondefinition qd                                 on q.questionnairedefinitionid = qd.questionnairedefinitionid                                 , qd.questiondefinitionid = 5966                             left outer join answer                                 on a.questionnaireid = q.questionnaireid                                 , a.questiondefinitionid = qd.questiondefinitionid                             q.isactive = 1                             , q.subscriberid = 240                             , q.accountid = patient.accountid                         ),         [invoiceamount] =   (   select top 1 a.response                                 dbo.questionnaire q                                 join dbo.questionnairedefinition qrd                                     on qrd.questionnairedefinitionid = q.questionnairedefinitionid                                     , qrd.name = 'internal admin'                                 left join questiondefinition qd                                     on q.questionnairedefinitionid = qd.questionnairedefinitionid                                     , qd.questiondefinitionid = 5969                                 left outer join answer                                     on a.questionnaireid = q.questionnaireid                                     , a.questiondefinitionid = qd.questiondefinitionid                                 q.isactive = 1                                 , q.subscriberid = 240                                 , q.accountid = patient.accountid                             ),         [datesubmitted] =   (   select top 1 a.response                                 dbo.questionnaire q                                 join dbo.questionnairedefinition qrd                                     on qrd.questionnairedefinitionid = q.questionnairedefinitionid                                     , qrd.name = 'internal admin'                                 left join questiondefinition qd                                     on q.questionnairedefinitionid = qd.questionnairedefinitionid                                     , qd.questiondefinitionid = 5965                                 left outer join answer                                     on a.questionnaireid = q.questionnaireid                                     , a.questiondefinitionid = qd.questiondefinitionid                                 q.isactive = 1                                 , q.subscriberid = 240                                 , q.accountid = patient.accountid                             ),         [dateapproveddeclined] =    (   select top 1 a.response                                         dbo.questionnaire q                                         join dbo.questionnairedefinition qrd                                             on qrd.questionnairedefinitionid = q.questionnairedefinitionid                                             , qrd.name = 'internal admin'                                         left join questiondefinition qd                                             on q.questionnairedefinitionid = qd.questionnairedefinitionid                                             , qd.questiondefinitionid = 5968                                         left outer join answer                                             on a.questionnaireid = q.questionnaireid                                             , a.questiondefinitionid = qd.questiondefinitionid                                         q.isactive = 1                                         , q.subscriberid = 240                                         , q.accountid = patient.accountid                                     ),         [intadmformcreateddate]= q.datecreated,         [hasadminform] = 'yes',         [createdbyuser] = patientcreatedby.name dbo.account patient join dbo.accountrating     on patient.accountratingid = accountrating.accountratingid join dbo.accounttype     on patient.accounttypeid = accounttype.accounttypeid join dbo.[user] region     on patient.userid = region.userid join dbo.[user] patientcreatedby     on patient.createdby = patientcreatedby.userid join dbo.questionnaire q     on patient.accountid = q.accountid  patient.subscriberid = 240     , (q.datecreated < dateadd(d, 26, dateadd(month, datediff(month, convert(datetime, '1900-01-01 00:00:00', 102), getdate()), convert(datetime, '1900-01-01 00:00:00', 102))))     , q.questionnairedefinitionid = 235     , q.isactive = 1     , region.firstname <> 'rubbish'  union select    [region] = region.firstname,                  [patient] = patient.name,                  [patientstatus] = accountrating.name,                  [medicalaid] = accounttype.name,                  [quoteamount] = '0',                  [invoiceamount] = '0',                  [datesubmitted] = '',                  [dateapproveddeclined] = '',                 [intadmformcreateddate] = '',                 [hasadminform] = 'no',                 [createdbyuser] = patientcreatedby.name  dbo.account patient  join dbo.accountrating     on patient.accountratingid = accountrating.accountratingid  join dbo.accounttype     on patient.accounttypeid = accounttype.accounttypeid  join dbo.[user] region     on patient.userid = region.userid  join dbo.[user] patientcreatedby     on patient.createdby = patientcreatedby.userid not exists(   select *                      questionnaire q                      patient.accountid = q.accountid                      , q.questionnairedefinitionid = 235                     , patient.subscriberid = 240                     , q.subscriberid = 240                 )     , patient.subscriberid = 240     , patient.datecreated < dateadd(d, 26, dateadd(month, datediff(month, convert(datetime, '1900-01-01 00:00:00', 102), getdate()), convert(datetime, '1900-01-01 00:00:00', 102)))     , region.firstname <> 'rubbish' 

here version of query tried, runs same time.

select  [region] = region.firstname,         [patient] = patient.name,         [patientstatus] = accountrating.name,         [medicalaid] = accounttype.name,         [quoteamount] = q1.response,         [invoiceamount] = q2.response,         [datesubmitted] = q3.response,         [dateapproveddeclined] = q4.response,         [intadmformcreateddate]= q.datecreated,         [hasadminform] = 'yes',         [createdbyuser] = patientcreatedby.name dbo.account patient join dbo.accountrating     on patient.accountratingid = accountrating.accountratingid join dbo.accounttype     on patient.accounttypeid = accounttype.accounttypeid join dbo.[user] region     on patient.userid = region.userid join dbo.[user] patientcreatedby     on patient.createdby = patientcreatedby.userid join dbo.questionnaire q     on patient.accountid = q.accountid outer apply (     select top 1    q.accountid,             a.response     dbo.questionnaire q     join dbo.questionnairedefinition qrd         on qrd.questionnairedefinitionid = q.questionnairedefinitionid         , qrd.name = 'internal admin'     left join questiondefinition qd         on q.questionnairedefinitionid = qd.questionnairedefinitionid         , qd.questiondefinitionid = 5966     left outer join answer         on a.questionnaireid = q.questionnaireid         , a.questiondefinitionid = qd.questiondefinitionid     q.isactive = 1     q.subscriberid = 240     , q.accountid = patient.accountid ) q1 outer apply (     select top 1    q.accountid,                     a.response     dbo.questionnaire q     join dbo.questionnairedefinition qrd         on qrd.questionnairedefinitionid = q.questionnairedefinitionid         , qrd.name = 'internal admin'     left join questiondefinition qd         on q.questionnairedefinitionid = qd.questionnairedefinitionid         , qd.questiondefinitionid = 5969     left outer join answer         on a.questionnaireid = q.questionnaireid         , a.questiondefinitionid = qd.questiondefinitionid     q.isactive = 1     , q.accountid = patient.accountid ) q2 outer apply (     select top 1    q.accountid,             a.response     dbo.questionnaire q     join dbo.questionnairedefinition qrd         on qrd.questionnairedefinitionid = q.questionnairedefinitionid         , qrd.name = 'internal admin'     left join questiondefinition qd         on q.questionnairedefinitionid = qd.questionnairedefinitionid         , qd.questiondefinitionid = 5965     left outer join answer         on a.questionnaireid = q.questionnaireid         , a.questiondefinitionid = qd.questiondefinitionid     q.isactive = 1     , q.accountid = patient.accountid ) q3 outer apply (     select top 1    q.accountid,                     a.response     dbo.questionnaire q     join dbo.questionnairedefinition qrd         on qrd.questionnairedefinitionid = q.questionnairedefinitionid         , qrd.name = 'internal admin'     left join questiondefinition qd         on q.questionnairedefinitionid = qd.questionnairedefinitionid         , qd.questiondefinitionid = 5968     left outer join answer         on a.questionnaireid = q.questionnaireid         , a.questiondefinitionid = qd.questiondefinitionid     q.isactive = 1     , q.accountid = patient.accountid ) q4 patient.subscriberid = 240     , (q.datecreated < dateadd(d, 26, dateadd(month, datediff(month, convert(datetime, '1900-01-01 00:00:00', 102), getdate()), convert(datetime, '1900-01-01 00:00:00', 102))))     , q.questionnairedefinitionid = 235     , q.isactive = 1     , region.firstname <> 'rubbish'  union select    [region] = region.firstname,                  [patient] = patient.name,                  [patientstatus] = accountrating.name,                  [medicalaid] = accounttype.name,                  [quoteamount] = '0',                  [invoiceamount] = '0',                  [datesubmitted] = '',                  [dateapproveddeclined] = '',                 [intadmformcreateddate] = '',                 [hasadminform] = 'no',                 [createdbyuser] = patientcreatedby.name  dbo.account patient  join dbo.accountrating     on patient.accountratingid = accountrating.accountratingid  join dbo.accounttype     on patient.accounttypeid = accounttype.accounttypeid  join dbo.[user] region     on patient.userid = region.userid  join dbo.[user] patientcreatedby     on patient.createdby = patientcreatedby.userid not exists(   select *                      questionnaire q                      patient.accountid = q.accountid                      , q.questionnairedefinitionid = 235                     , patient.subscriberid = 240                     , q.subscriberid = 240                 )     , patient.subscriberid = 240     , patient.datecreated < dateadd(d, 26, dateadd(month, datediff(month, convert(datetime, '1900-01-01 00:00:00', 102), getdate()), convert(datetime, '1900-01-01 00:00:00', 102)))     , region.firstname <> 'rubbish' 

your scalar subqueries share same joins, qd.questiondefinitionid differs.

you can rewrite 4 tops single derived table , join instead:

... left join  (    select         q.accountid,        max(case when qd.questiondefinitionid = 5966 a.response end) [datesubmitted]        max(case when qd.questiondefinitionid = 5968 a.response end) [dateapproveddeclined]        ...    dbo.questionnaire q    join dbo.questionnairedefinition qrd        on qrd.questionnairedefinitionid = q.questionnairedefinitionid        , qrd.name = 'internal admin'    left join questiondefinition qd        on q.questionnairedefinitionid = qd.questionnairedefinitionid    left outer join answer        on a.questionnaireid = q.questionnaireid        , a.questiondefinitionid = qd.questiondefinitionid    q.isactive = 1    , q.subscriberid = 240    group q.accountid  ) q  on q.accountid = patient.accountid 

i used max because didn't have order in subqueries, exact value either doesn't matter or there's single row per value.