We help IT Professionals succeed at work.

Query taking forever to execute, any advice?

Is there anyway to speed up the performance of this query.
select a.FiscalMonth, a.date, a.FULLNAME, hoursworked, isnull(b.Faxes,0) as Faxes,
 isnull(c.[PDL Originated],0) as 'PDL Originated', isnull(c.[ILP Originated],0) as 'ILP Originated', 
 isnull(d.[PDL Renewals],0) as 'PDL Renewals', isnull(d.[ILP Renewals],0) as 'ILP Renewals',
 isnull(d.[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

(
--KRONOS HOURS WORKED
SELECT fiscalmonth, bda.DATE, a.fullname, 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
inner join TeamLeads$ t on t.FULLNAME = a.TEAM_LEAD 
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and a.STATUS = 'a'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, bda.date, a.FULLNAME
)a

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, bda.date, fullname
)b

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

left join
(
--PDL AND DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, bda.date, fullname, 
SUM(case when bia.loan_type = 'pdl' and processing_status = 'originated' then 1 else 0 end)-SUM(case when slm.loan_status_id = 'v' then 1 else 0 end) as 'PDL Originated',
SUM(case when bia.loan_type = 'ilp' and processing_status = 'originated' then 1 else 0 end)-SUM(case when sim.loan_status_id = 'v' then 1 else 0 end) as 'ILP Originated',
SUM(case when processing_status = 'originated' then 1 else 0 end)-SUM(case when slm.loan_status_id = 'v' then 1 else 0 end)-SUM(case when sim.loan_status_id = 'v' then 1 else 0 end) as 'Total 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.st_il_master sim on bia.app_no = sim.iloan_code
inner join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
inner join Associates$ a on a.emp_code = bia.originated_by
where bia.app_type = 'std'
group by fiscalmonth, bda.date, fullname
)c

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

left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, 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(ren.app_no) as 'Total Renewals'

from

	(
	select FiscalMonth, 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, ren.date, prev.fullname
)d

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

left join
(
--Noble Inbound AHT
select fiscalmonth, 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, date, fullname
)e

on a.Date = e.Date and a.FULLNAME = e.FULLNAME

left join
(
--Noble Outbound AHT
select fiscalmonth, 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, date, fullname
)f

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

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

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
The short answer is: Probably

Download this app, and paste the query in it and run it to get the actual execution plan.  Then either go through the plan yourself, or save it and post it here so we can see where the slow downs are occurring.

http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

Jini JoseSenior .Net Developer

Commented:
you have to first create views for all the nested queries.

Author

Commented:
So I created views and ran the query, which executed in the same amount of time as the original query.
select a.fiscalmonth, a.date, a.fullname, hoursworked, faxes
from vw_ls_hoursworked a
left join vw_assigned_faxes b on a.date = b.date and a.fullname = b.fullname 
left join vw_originated_by c on a.date = c.date and a.fullname = c.fullname 
where a.fiscalmonth = '201111'

Open in new window

Author

Commented:
lluden, I do not have SHOWPLAN permissions on one of the databases I am connecting to in the query.
Jini JoseSenior .Net Developer

Commented:
is the fullname is a primary key or indexed  ?

Author

Commented:
Fullname is an index. The primary key is an ID field. The reason Fullname is not the primary key because an individual may have one or more employee codes. So the associate table looks like this:

ID, Employee_Code, Fullname
Jini JoseSenior .Net Developer

Commented:
but the fullname also having duplicates..
why couldnt you use ID or employee_code ?

Author

Commented:
An associate that has two employee codes would show up as two different records correct? No matter which emp_code is linked to say "faxes" I only want to report distinct fullnames. There are only 60 associates with maybe 5 with multiple employee codes, so I wouldnt think this would slow down the query. I may be wrong though!

Author

Commented:
For some reason the code below is what was slowing the query down.

left join
(
--PDL AND DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, bda.date, fullname,
SUM(case when bia.loan_type = 'pdl' and processing_status = 'originated' then 1 else 0 end)-SUM(case when slm.loan_status_id = 'v' then 1 else 0 end) as 'PDL Originated',
SUM(case when bia.loan_type = 'ilp' and processing_status = 'originated' then 1 else 0 end)-SUM(case when sim.loan_status_id = 'v' then 1 else 0 end) as 'ILP Originated',
SUM(case when processing_status = 'originated' then 1 else 0 end)-SUM(case when slm.loan_status_id = 'v' then 1 else 0 end)-SUM(case when sim.loan_status_id = 'v' then 1 else 0 end) as 'Total 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.st_il_master sim on bia.app_no = sim.iloan_code
inner join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
inner join Associates$ a on a.emp_code = bia.originated_by
where bia.app_type = 'std'
group by fiscalmonth, bda.date, fullname
)c

I created two seperate queries and fixed the issue.
CERTIFIED EXPERT
Top Expert 2012
Commented:
You need to refactor that whole query and make sure it is indexed appropriately.  That means:
Combining queries that are similar into a single query such as your PDL AND DILP STANDARD ORIGINATIONS BY EMP_CODE and Initiate and Originated Renewals by Emp_Code as well as your Noble Inbound AHT and Noble Outbound AHT
Splitting up the subqueries into temporary tables and then using them in the final SELECT.
Make sure you have indexes on all columns that in a JOIN, WHERE, GROUP BY and ORDER BY clause.
Add all the missing alias will not improve performance, but it will certainly make it more readable.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.