dplowman
asked on
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
you have to first create views for all the nested queries.
ASKER
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'ASKER
lluden, I do not have SHOWPLAN permissions on one of the databases I am connecting to in the query.
is the fullname is a primary key or indexed ?
ASKER
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
ID, Employee_Code, Fullname
but the fullname also having duplicates..
why couldnt you use ID or employee_code ?
why couldnt you use ID or employee_code ?
ASKER
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!
ASKER
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_i
left join usonlinereporting.dbo.st_l
left join usonlinereporting.dbo.st_i
inner join usonlinereporting.dbo.busi
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.
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_i
left join usonlinereporting.dbo.st_l
left join usonlinereporting.dbo.st_i
inner join usonlinereporting.dbo.busi
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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