March 13, 2014 at 3:50 pm
I am trying to speed up the below code, which includes numerous subselect statements. Any thoughts on how I could alter the statements for the code to run faster. The queries will be running in Oracle environment.
Thanks
Select prod_month, lockbox, company, workgrp, checks, Unprocs, NoChk, frontim, checktotal, keystrokes,
(checks+unprocs+NoChk) as TotalCKNCUP,
case when checktotal > 0 and checks > 0
then round(checktotal / checks,2)
else 0 end as AvgDollarCheck,
ocr_stub,
nvl((Select sum(error_count) from errors_kpi_isd err
where err.lockboxnumber = oq.lockbox
and err.product = oq.donorsite
and substr(err.errortype,1,3) not in ('DKY','FYI','IMG')
and err.inquirydate between oq.prod_month and add_months(oq.prod_month,1)-1),0) as NonDKYerrorsforMonth,
nvl((Select sum(error_count) from errors_kpi_isd err
where err.lockboxnumber = oq.lockbox
and err.product = oq.donorsite
and substr(err.errortype,1,3) in ('DKY')
and err.inquirydate between oq.prod_month and add_months(oq.prod_month,1)-1),0) as DKYerrorsforMonth,
(Select checks from r1_monthly_volumes iq
Where iq.prod_month = add_months(oq.prod_month,-1)
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) as ckslastmo,
case when (Select checks from r1_monthly_volumes iq
Where iq.prod_month = add_months(oq.prod_month,-1)
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) > 0
and oq.checks > 0
then round(((Select checks from r1_monthly_volumes iq
Where iq.prod_month = add_months(oq.prod_month,-1)
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid)-oq.checks)/(Select checks from r1_monthly_volumes iq
Where iq.prod_month = add_months(oq.prod_month,-1)
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid),4)*-1
else 0 end as ckchangefromlastmo,
(Select sum(checks) from r1_monthly_volumes iq
where iq.prod_month between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and oq.prod_month
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) as YTD_Checks,
case when (Select sum(checks) from r1_monthly_volumes iq
where iq.prod_month between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and oq.prod_month
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) > 0
then round((Select sum(checks) from r1_monthly_volumes iq
where iq.prod_month between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and oq.prod_month
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) / to_number(to_char(prod_month,'MM'),'99'),0)
else 0 end as YTDMonthlyAvgChecks,
nvl((Select sum(error_count) from errors_kpi_isd err
where err.lockboxnumber = oq.lockbox
and err.product = oq.donorsite
and substr(err.errortype,1,3) not in ('DKY','FYI','IMG')
and err.inquirydate between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and add_months(oq.prod_month,1)-1),0) as YTDNonDKYErrorCount,
nvl((Select sum(error_count) from errors_kpi_isd err
where err.lockboxnumber = oq.lockbox
and err.product = oq.donorsite
and substr(err.errortype,1,3) in ('DKY')
and err.inquirydate between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and add_months(oq.prod_month,1)-1),0) as YTDDKYErrorCount,
case when (Select sum(checks) from r1_monthly_volumes iq
where iq.prod_month between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and oq.prod_month
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) > 0
and (Select sum(error_count) from errors_kpi_isd err
where err.lockboxnumber = oq.lockbox
and err.product = oq.donorsite
and substr(err.errortype,1,3) not in ('DKY','FYI','IMG')
and err.inquirydate between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and add_months(oq.prod_month,1)-1) > 0
then round((Select sum(checks) from r1_monthly_volumes iq
where iq.prod_month between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and oq.prod_month
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) /
(Select sum(error_count) from errors_kpi_isd err
where err.lockboxnumber = oq.lockbox
and err.product = oq.donorsite
and substr(err.errortype,1,3) not in ('DKY','FYI','IMG')
and err.inquirydate between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and add_months(oq.prod_month,1)-1),0)
else (Select sum(checks) from r1_monthly_volumes iq
where iq.prod_month between add_months(prod_month,-to_number(to_char(Prod_month,'MM'),'99')+1)
and oq.prod_month
and iq.lockbox = oq.lockbox
and iq.siteid = oq.siteid) end as ytdcheckspererror
from r1_monthly_volumes oq
where DONORSITE = :p161_SITENAME
and Prod_month = to_date(to_date(:P161_monthyear,'MON-YY'),'DD-MON-YY')
March 13, 2014 at 4:24 pm
I would like to help you (as well as others in this site), however, due to the differences between Oracle and SQL Server, I'm not sure that I can suggest an improvement that will work for you.
You'll get more help in an Oracle forum as this one is dedicated to SQL Server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply