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.
Comments
Post a Comment