Solved

Optimize query to run faster...

Posted on 2012-03-14
6
267 Views
Last Modified: 2012-03-20
All,

I am trying to optimize the query below to execute faster. The current query takes around 2:30 minutes. I cannot seem to find out where the query is getting stuck. Any of you see where\how I can try to fix this?


select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
 isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated', 
 isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
 isnull(f.[Total Renewals],0) as 'Total Renewals',
 isnull([Inbound Connect],0) as 'Inbound Connect',
 isnull([Inbound Waiting],0) as 'Inbound Waiting',
 isnull([Inbound ACW],0) as 'Inbound ACW',
 isnull([Inbound Paused],0) as 'Inbound Paused',
 isnull([Inbound Deassign],0) as 'Inbound Deassign',
 isnull([Inbound Calls],0) as 'Inbound Calls',
 isnull([outbound Connect],0) as 'outbound Connect',
 isnull([outbound Waiting],0) as 'outbound Waiting',
 isnull([outbound ACW],0) as 'outbound ACW',
 isnull([outbound Paused],0) as 'outbound Paused',
 isnull([outbound Deassign],0) as 'outbound Deassign',
 isnull([outbound Calls],0) as 'outbound Calls'
from

--ALL DATES

(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda, Associates$ a
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a

left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, Sum(TimeHours)/COUNT(disti<wbr ></wbr>nct(a.EMP_<wbr ></wbr>CODE)) AS hoursworked
FROM usonlinereporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda 
INNER JOIN EBGOperationalReporting.db<wbr ></wbr>o.Kronos_H<wbr ></wbr>ours_EBG k ON bda.Date = k.date
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b

on a.date = b.date and a.fullname = b.fullname


left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc<wbr ></wbr>_id)) as Faxes
from usonlinereporting.dbo.bo_a<wbr ></wbr>pp_fax_doc<wbr ></wbr>s baf
inner join usonlinereporting.dbo.bo_f<wbr ></wbr>ax_docs bfd on bfd.FAX_DOC_ID = baf.FAX_DOC_ID
inner join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on bda.Date = dateadd(dd,0,datediff(dd,0<wbr ></wbr>,bfd.creat<wbr ></wbr>ed_date))
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, fiscalweek, bda.date, fullname
)c

on a.date = c.date and a.fullname = c.fullname

left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname, 
count(distinct(app_no)) as 'PDL Originated'
from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
left join usonlinereporting.dbo.st_l<wbr ></wbr>o_master slm on bia.app_no = slm.loan_code
left join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on dateadd(dd,0,datediff(dd,0<wbr ></wbr>,bia.loan_<wbr ></wbr>originated<wbr ></wbr>)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d

on a.date = d.date and a.fullname = d.fullname

left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname, 
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
left join usonlinereporting.dbo.st_i<wbr ></wbr>l_master slm on bia.app_no = slm.iloan_code
left join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on dateadd(dd,0,datediff(dd,0<wbr ></wbr>,bia.loan_<wbr ></wbr>originated<wbr ></wbr>)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e

on a.date = e.date and a.fullname = e.fullname

left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, ren.DATE, prev.FULLNAME, 
sum(case when loan_type = 'pdl' then 1 else 0 end) as 'PDL Renewals',
sum(case when loan_type = 'ilp' then 1 else 0 end) as 'ILP Renewals',
COUNT(distinct(ren.app_no)<wbr ></wbr>) as 'Total Renewals'

from

	(
	select FiscalMonth, fiscalweek, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
	from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
	inner join USOnlineReporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda on bda.Date = dateadd(day,0,datediff(dd,<wbr ></wbr>0,bia.LOAN<wbr ></wbr>_ORIGINATE<wbr ></wbr>D))
	where APP_TYPE = 'ren'
	and PROCESSING_STATUS = 'originated'
	)ren

	inner join
	(
	select distinct(bn.app_no), a.fullname
	from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
	inner join usonlinereporting.dbo.bo_n<wbr ></wbr>otes bn  on bn.APP_NO = bia.APP_NO
	inner join Associates$ a on a.EMP_CODE = bn.CREATED_BY
	inner join TeamLeads$ tl on tl.FULLNAME = a.team_lead
	--inner join USOnlineReporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda on bda.Date = dateadd(day,0,datediff(dd,<wbr ></wbr>0,bn.DATE_<wbr ></wbr>CREATED))
	where notes = 'Initiate Renewal button on the Courtesy Call'
	)prev

	on ren.PREV_LOAN_NO = prev.app_no

	group by ren.fiscalmonth, fiscalweek, ren.date, prev.fullname
)f

on a.Date = f.Date and a.FULLNAME = f.fullname

left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek,  date, fullname,
SUM(time_connect) as 'Inbound Connect',
SUM(time_waiting) as 'Inbound Waiting',
SUM(time_acw) as 'Inbound ACW',
SUM(time_paused) as 'Inbound Paused',
SUM(time_deassigned) as 'Inbound Deassign',
SUM(tot_calls) as 'Inbound Calls'
from noble_call_history n 
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on bda.Date = call_date
where appl = 'lsin'
group by fiscalmonth, fiscalweek, date, fullname
)g

on a.Date = g.Date and a.FULLNAME = g.FULLNAME

left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, date, fullname,
SUM(time_connect) as 'Outbound Connect',
SUM(time_waiting) as 'Outbound Waiting',
SUM(time_acw) as 'Outbound ACW',
SUM(time_paused) as 'Outbound Paused',
SUM(time_deassigned) as 'Outbound Deassign',
SUM(tot_calls) as 'Outbound Calls'
from noble_call_history n 
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on bda.Date = call_date
where appl <> 'lsin'
group by fiscalmonth, fiscalweek, date, fullname
)h

on a.Date = h.Date and a.FULLNAME = h.FULLNAME

where a.FiscalMonth = '201203'
order by a.fullname, a.Date

Open in new window


Thanks,
Dustin
0
Comment
Question by:dplowman
6 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Did you looked at the Query execution plan? Are there any indexes missing?
Can you post the graphical plan or run the command below before rerun thsi query and post the text plan here:

set showplan_text on;
set statistics io on;
set statistics time on;
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Also can you post the query withoutb the garbage like:

...<wbr ></wbr>....<wbr ></wbr>_All....

First tought is to try use CTE in SQL but would need the clean query to provide a re-write with CTE besides of what I posted above.
0
 

Author Comment

by:dplowman
Comment Utility
Sorry, not sure why the code contained the <wbr>,
select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
 isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated', 
 isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
 isnull(f.[Total Renewals],0) as 'Total Renewals',
 isnull([Inbound Connect],0) as 'Inbound Connect',
 isnull([Inbound Waiting],0) as 'Inbound Waiting',
 isnull([Inbound ACW],0) as 'Inbound ACW',
 isnull([Inbound Paused],0) as 'Inbound Paused',
 isnull([Inbound Deassign],0) as 'Inbound Deassign',
 isnull([Inbound Calls],0) as 'Inbound Calls',
 isnull([outbound Connect],0) as 'outbound Connect',
 isnull([outbound Waiting],0) as 'outbound Waiting',
 isnull([outbound ACW],0) as 'outbound ACW',
 isnull([outbound Paused],0) as 'outbound Paused',
 isnull([outbound Deassign],0) as 'outbound Deassign',
 isnull([outbound Calls],0) as 'outbound Calls'
from

--ALL DATES

(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Business_Dates_All bda, Associates$ a
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a

left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, Sum(TimeHours)/COUNT(distinct(a.EMP_CODE)) AS hoursworked
FROM usonlinereporting.dbo.Business_Dates_All bda 
INNER JOIN EBGOperationalReporting.dbo.Kronos_Hours_EBG k ON bda.Date = k.date
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b

on a.date = b.date and a.fullname = b.fullname


left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc_id)) as Faxes
from usonlinereporting.dbo.bo_app_fax_docs baf
inner join usonlinereporting.dbo.bo_fax_docs bfd on bfd.FAX_DOC_ID = baf.FAX_DOC_ID
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = dateadd(dd,0,datediff(dd,0,bfd.created_date))
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, fiscalweek, bda.date, fullname
)c

on a.date = c.date and a.fullname = c.fullname

left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname, 
count(distinct(app_no)) as 'PDL Originated'
from usonlinereporting.dbo.bo_in_app_queue bia
left join usonlinereporting.dbo.st_lo_master slm on bia.app_no = slm.loan_code
left join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d

on a.date = d.date and a.fullname = d.fullname

left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname, 
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_in_app_queue bia
left join usonlinereporting.dbo.st_il_master slm on bia.app_no = slm.iloan_code
left join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e

on a.date = e.date and a.fullname = e.fullname

left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, ren.DATE, prev.FULLNAME, 
sum(case when loan_type = 'pdl' then 1 else 0 end) as 'PDL Renewals',
sum(case when loan_type = 'ilp' then 1 else 0 end) as 'ILP Renewals',
COUNT(distinct(ren.app_no)) as 'Total Renewals'

from

	(
	select FiscalMonth, fiscalweek, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
	from usonlinereporting.dbo.bo_in_app_queue bia
	inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = dateadd(day,0,datediff(dd,0,bia.LOAN_ORIGINATED))
	where APP_TYPE = 'ren'
	and PROCESSING_STATUS = 'originated'
	)ren

	inner join
	(
	select distinct(bn.app_no), a.fullname
	from usonlinereporting.dbo.bo_in_app_queue bia
	inner join usonlinereporting.dbo.bo_notes bn  on bn.APP_NO = bia.APP_NO
	inner join Associates$ a on a.EMP_CODE = bn.CREATED_BY
	inner join TeamLeads$ tl on tl.FULLNAME = a.team_lead
	--inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = dateadd(day,0,datediff(dd,0,bn.DATE_CREATED))
	where notes = 'Initiate Renewal button on the Courtesy Call'
	)prev

	on ren.PREV_LOAN_NO = prev.app_no

	group by ren.fiscalmonth, fiscalweek, ren.date, prev.fullname
)f

on a.Date = f.Date and a.FULLNAME = f.fullname

left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek,  date, fullname,
SUM(time_connect) as 'Inbound Connect',
SUM(time_waiting) as 'Inbound Waiting',
SUM(time_acw) as 'Inbound ACW',
SUM(time_paused) as 'Inbound Paused',
SUM(time_deassigned) as 'Inbound Deassign',
SUM(tot_calls) as 'Inbound Calls'
from noble_call_history n 
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = call_date
where appl = 'lsin'
group by fiscalmonth, fiscalweek, date, fullname
)g

on a.Date = g.Date and a.FULLNAME = g.FULLNAME

left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, date, fullname,
SUM(time_connect) as 'Outbound Connect',
SUM(time_waiting) as 'Outbound Waiting',
SUM(time_acw) as 'Outbound ACW',
SUM(time_paused) as 'Outbound Paused',
SUM(time_deassigned) as 'Outbound Deassign',
SUM(tot_calls) as 'Outbound Calls'
from noble_call_history n 
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = call_date
where appl <> 'lsin'
group by fiscalmonth, fiscalweek, date, fullname
)h

on a.Date = h.Date and a.FULLNAME = h.FULLNAME

where a.FiscalMonth = @fiscalmonth
order by a.fullname, a.Date

Open in new window

must have happened when I pasted. Here is the cleaner version.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:dplowman
Comment Utility
Not familiar with set showplan_text on. When I paste this code at the beginning of the query I am getting.... The Set Showplan statement must be the only statement in the batch.
0
 
LVL 8

Expert Comment

by:rushShah
Comment Utility
try to move similar sub-queries outside into temp table, and use temp table in main query, like this....

hope this helps......

select fiscalmonth, fiscalweek, date, fullname, appl,
SUM(time_connect) as 'Connect',
SUM(time_waiting) as 'Waiting',
SUM(time_acw) as 'ACW',
SUM(time_paused) as 'Paused',
SUM(time_deassigned) as 'Deassign',
SUM(tot_calls) as 'Calls'
INTO #temp_Noble_Call_History
from noble_call_history n
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = call_date
group by fiscalmonth, fiscalweek, date, fullname, appl

select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
 isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated',
 isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
 isnull(f.[Total Renewals],0) as 'Total Renewals',
 isnull([Inbound Connect],0) as 'Inbound Connect',
 isnull([Inbound Waiting],0) as 'Inbound Waiting',
 isnull([Inbound ACW],0) as 'Inbound ACW',
 isnull([Inbound Paused],0) as 'Inbound Paused',
 isnull([Inbound Deassign],0) as 'Inbound Deassign',
 isnull([Inbound Calls],0) as 'Inbound Calls',
 isnull([outbound Connect],0) as 'outbound Connect',
 isnull([outbound Waiting],0) as 'outbound Waiting',
 isnull([outbound ACW],0) as 'outbound ACW',
 isnull([outbound Paused],0) as 'outbound Paused',
 isnull([outbound Deassign],0) as 'outbound Deassign',
 isnull([outbound Calls],0) as 'outbound Calls'
from

--ALL DATES

(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Business_Dates_All bda, Associates$ a
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a

left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, Sum(TimeHours)/COUNT(distinct(a.EMP_CODE)) AS hoursworked
FROM usonlinereporting.dbo.Business_Dates_All bda
INNER JOIN EBGOperationalReporting.dbo.Kronos_Hours_EBG k ON bda.Date = k.date
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b

on a.date = b.date and a.fullname = b.fullname


left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc_id)) as Faxes
from usonlinereporting.dbo.bo_app_fax_docs baf
inner join usonlinereporting.dbo.bo_fax_docs bfd on bfd.FAX_DOC_ID = baf.FAX_DOC_ID
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = dateadd(dd,0,datediff(dd,0,bfd.created_date))
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, fiscalweek, bda.date, fullname
)c

on a.date = c.date and a.fullname = c.fullname

left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname,
count(distinct(app_no)) as 'PDL Originated'
from usonlinereporting.dbo.bo_in_app_queue bia
left join usonlinereporting.dbo.st_lo_master slm on bia.app_no = slm.loan_code
left join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d

on a.date = d.date and a.fullname = d.fullname

left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname,
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_in_app_queue bia
left join usonlinereporting.dbo.st_il_master slm on bia.app_no = slm.iloan_code
left join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e

on a.date = e.date and a.fullname = e.fullname

left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, ren.DATE, prev.FULLNAME,
sum(case when loan_type = 'pdl' then 1 else 0 end) as 'PDL Renewals',
sum(case when loan_type = 'ilp' then 1 else 0 end) as 'ILP Renewals',
COUNT(distinct(ren.app_no)) as 'Total Renewals'

from

      (
      select FiscalMonth, fiscalweek, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
      from usonlinereporting.dbo.bo_in_app_queue bia
      inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = dateadd(day,0,datediff(dd,0,bia.LOAN_ORIGINATED))
      where APP_TYPE = 'ren'
      and PROCESSING_STATUS = 'originated'
      )ren

      inner join
      (
      select distinct(bn.app_no), a.fullname
      from usonlinereporting.dbo.bo_in_app_queue bia
      inner join usonlinereporting.dbo.bo_notes bn  on bn.APP_NO = bia.APP_NO
      inner join Associates$ a on a.EMP_CODE = bn.CREATED_BY
      inner join TeamLeads$ tl on tl.FULLNAME = a.team_lead
      --inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = dateadd(day,0,datediff(dd,0,bn.DATE_CREATED))
      where notes = 'Initiate Renewal button on the Courtesy Call'
      )prev

      on ren.PREV_LOAN_NO = prev.app_no

      group by ren.fiscalmonth, fiscalweek, ren.date, prev.fullname
)f

on a.Date = f.Date and a.FULLNAME = f.fullname

left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek,  date, fullname,
Connect as 'Inbound Connect',
Waiting as 'Inbound Waiting',
ACW as 'Inbound ACW',
Paused as 'Inbound Paused',
Deassign as 'Inbound Deassign',
Calls as 'Inbound Calls'
from #temp_Noble_Call_History
where appl = 'lsin'
)g

on a.Date = g.Date and a.FULLNAME = g.FULLNAME

left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, date, fullname,
Connect as 'Outbound Connect',
Waiting as 'Outbound Waiting',
ACW as 'Outbound ACW',
Paused as 'Outbound Paused',
Deassign as 'Outbound Deassign',
Calls as 'Outbound Calls'
from #temp_Noble_Call_History
where appl <> 'lsin'
)h

on a.Date = h.Date and a.FULLNAME = h.FULLNAME

where a.FiscalMonth = @fiscalmonth
order by a.fullname, a.Date

DROP TABLE #temp_Noble_Call_History
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
Probably the easiest and fastest improvement would be to add the fiscalmonth condition to every subquery.

The main/outermost query specifies:

where a.FiscalMonth = @fiscalmonth

so add a check for FiscalMonth = @fiscalmonth to all the subqueries to reduce the number of rows they each have to process.


Also, the first subquery:
"
(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Business_Dates_All bda, Associates$ a
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a
"
appears to have an implied CROSS JOIN.  You should convert the ", Associates$" into an explicit JOIN:

FROM usonlinereporting.dbo.Business_Dates_All bda
INNER JOIN Associates$ a$

And it uses the same alias in a inner query and the outer query: "a" above.  That's ambiguous, and SQL will sometimes chose the column you didn't expect from the "wrong" table.  NEVER re-use an alias in subqueries and outer query.


Finally, for queries that join tables, **ALWAYS** specify the alias on **EVERY** column [ESPECIALLY when asking someone with no knowledge of the underlying tables to look at the code].  Naturally I have no clue which table each column comes from ... and if you work on something else and come back to this 10 months later, you might not either.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now