Solved

Optimize query to run faster...

Posted on 2012-03-14
6
279 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
ID: 37722192
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
ID: 37722213
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
ID: 37722391
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:dplowman
ID: 37722416
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
ID: 37724015
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:
Scott Pletcher earned 500 total points
ID: 37725215
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

791 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