做了一次sql脚本的优化。
结果还是比较满意的。执行时间从2分半钟降到了3秒左右。但是从过程中看,其实得到这个结果并不是因为优化做得多么出色,而是因为以前的脚本写得太烂。换句话说,这一次的优化并不是把一个正常的脚本修改得很出色,而是把一个糟糕的脚本修改得比较正常。
不过我比较知足。一方面用户接受了这个结果。另一方面,那个糟糕的脚本本来也是我写的……
原先的脚本之所以这么糟糕,主要原因是子查询太多。几乎每一个字段都有一个子查询语句。而且每个子查询都是先获取一个全集,然后从全集中去查所需要的数据。子查询只需要以一个子集为依据。
所以这次优化的策略是:先把最终需要的字段分成三组,对应的把全集拆分成三个子集。先从子集里查出所需的每组字段,然后再对三组字段进行连接。从子集里查出各组字段也不再使用子查询的方式,而是尽可能的使用连接。
其实就只做了这么一点事情。简单地说就是去掉了不必要的重复操作,以及缩小查询的起始范围。另外,作为个人的一点收获,对sql的一些语法也更熟悉了些。
贴一小段代码吧。觉得前后一样烂的兄弟们请轻拍,IT何苦为难IT。
这是优化前的:
- select u.userCode,
- u.userName,
- u.comCode,
- (select o.comcname
- from t_atip_organization o
- where o.comcode = u.comcode) as comName,
- ym.sjmc,
- -- 主審項目數
- (select count(distinct p.projectapprovalcode)
- from t_atip_projectapproval p, t_atip_prostatustrack ps
- where p.chiefaudit = u.usercode
- and p.projectlevel = '1'
- and p.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = p.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(p.projectapprovalcode, 0, 21))) as chiefAuditCount,
- -- 主審項目中發現問題總數
- (select count(distinct(prob.seqno))
- from t_atip_problem prob,
- t_atip_projectapproval proj,
- t_atip_prostatustrack ps
- where prob.programcode = proj.projectapprovalcode
- and proj.chiefaudit = u.usercode
- and prob.isrecall = '1'
- and proj.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(proj.projectapprovalcode, 0, 21))) as chiefProbelCount,
- -- 主審項目中發現的重大問題總數
- (select count(distinct(prob.seqno))
- from t_atip_problem prob,
- t_atip_projectapproval proj,
- t_atip_prostatustrack ps
- where prob.programcode = proj.projectapprovalcode
- and prob.problemlevel in ('13', '14')
- and proj.chiefaudit = u.usercode
- and prob.isrecall = '1'
- and proj.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(proj.projectapprovalcode, 0, 21))) as chiefMainProbCount,
- -- 作為主審的被評分項目數
- (select count(distinct p.projectapprovalcode)
- from t_atip_projectevaluation eval,
- t_atip_projectapproval p,
- t_atip_prostatustrack ps
- where (eval.projectcode = p.projectapprovalcode or
- p.projectapprovalcode =
- substr(eval.projectcode, 0, length(eval.projectcode) - 3))
- and p.chiefaudit = u.usercode
- and eval.usercode = u.usercode
- and p.projectlevel = '1'
- and p.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = p.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(p.projectapprovalcode, 0, 21))) as chiefGradedProjectCount,
- --作為主審被評分的總分
- /*(select round(nvl(avg(sum(eval.score)), 0), 2) as avgscore
- from t_atip_projectevaluation eval,
- t_atip_projectapproval p,
- t_atip_prostatustrack ps
- where (eval.projectcode = p.projectapprovalcode or
- p.projectapprovalcode =
- substr(eval.projectcode, 0, length(eval.projectcode) - 3))
- and p.chiefaudit = u.usercode
- and eval.usercode = u.usercode
- and p.projectlevel = '1'
- and p.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = p.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(p.projectapprovalcode, 0, 21))
- group by eval.projectcode, eval.evaluationusercode)*/
- (SELECT SUM(ma.avgtotalscore)
- FROM (SELECT m.projectcode,
- m.usercode,
- AVG(projtotalscore) avgtotalscore
- FROM (select substr(eval.projectcode, 0, 21) projectcode,
- eval.usercode,
- eval.evaluationusercode,
- sum(nvl(eval.score,0)) as projtotalscore
- from t_atip_projectevaluation eval
- group by substr(eval.projectcode, 0, 21),
- eval.evaluationusercode,
- eval.usercode) m
- GROUP BY m.projectcode, m.usercode) ma,
- t_atip_projectapproval p,
- t_atip_prostatustrack ps
- WHERE ma.projectcode = p.projectapprovalcode
- AND p.projectlevel = '1'
- AND p.projectyear = substr(ym.sjmc, 0, 4)
- AND ma.usercode = p.chiefaudit
- AND p.chiefaudit = u.usercode
- and ps.projectapprovalcode = p.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(p.projectapprovalcode, 0, 21))
- GROUP BY ma.usercode)
- as chiefTotalGrade,
- -- 作為主審被質檢打分的項目數
- (select count(distinct qual.projectcode)
- from t_atip_qualitytype qual,
- t_atip_projectapproval proj,
- t_atip_prostatustrack ps
- where qual.projectcode = proj.projectapprovalcode
- and proj.chiefaudit = u.usercode
- and proj.projectlevel = '1'
- and proj.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(proj.projectapprovalcode, 0, 21))) as chiefQualifiedProjectCount,
- --作為主審被質檢打分的總分
- (select nvl(sum(qual.score), 0)
- from t_atip_qualitytype qual,
- t_atip_projectapproval proj,
- t_atip_prostatustrack ps
- where qual.projectcode = proj.projectapprovalcode
- and proj.chiefaudit = u.usercode
- and proj.projectlevel = '1'
- and proj.projectyear = substr(ym.sjmc, 0, 4)
- and ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and ym.sjmc =
- (select distinct to_char(max(updatedate), 'yyyy-MM')
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- substr(proj.projectapprovalcode, 0, 21))) as chiefTotalQualified
- from t_atip_user u,
- (select to_char(add_months(date '2003-12-01', 1 * rownum), 'yyyy-MM') sjmc
- from dual
- where 1 = 1
- connect by rownum <
- (select months_between(trunc(sysdate, 'mm'),
- trunc(date '2003-12-01', 'mm'))
- from dual) + 1) ym
- where u.validstatus = '1'
- and u.istemporary = '0'
这是优化后的:
- select USERCODE,
- COMCODE,
- SJMC,
- sum(CHIEFAUDITCOUNT) as CHIEFAUDITCOUNT,
- sum(CHIEFEVECTIONCOUNT) as CHIEFEVECTIONCOUNT,
- sum(CHIEFTOTALQUALIFIED) as CHIEFTOTALQUALIFIED,
- sum(CHIEFQUALIFIEDPROJECTCOUNT) as CHIEFQUALIFIEDPROJECTCOUNT,
- sum(CHIEFTOTALGRADE) as CHIEFTOTALGRADE,
- sum(CHIEFGRADEDPROJECTCOUNT) as CHIEFGRADEDPROJECTCOUNT,
- sum(CHIEFMAINPROBCOUNT) as CHIEFMAINPROBCOUNT,
- sum(CHIEFPROBELCOUNT) as CHIEFPROBELCOUNT
- from (
- -- 只計算項目數和出差天數,其它全部置為0
- select u.userCode,
- u.comCode,
- (to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
- -- 主審項目數
- count(proj.projectapprovalcode) as chiefAuditCount,
- -- 主審出差天數
- sum(proj.totalday) as chiefEvectionCount,
- -- 作為主審被質檢打分的總分,暫時繞過
- 0 as chiefTotalQualified,
- -- 作為主審被質檢打分的項目數
- 0 as chiefQualifiedProjectCount,
- -- 主審被評分項目總分,暫時繞過吧……
- 0 as chiefTotalGrade,
- -- 主審被評分項目數
- 0 as chiefGradedProjectCount,
- -- 主審項目中發現重大問題總數
- 0 as chiefMainProbCount,
- --主審項目中發現問題總數
- 0 as chiefProbelCount
- from t_atip_user u,
- t_atip_prostatustrack ps,
- t_atip_projectapproval proj
- where ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and proj.projectyear = to_char(ps.updatedate, 'yyyy')
- and proj.chiefaudit = u.usercode
- and proj.projectlevel = '1'
- and u.validstatus = '1'
- and u.istemporary = '0'
- group by u.usercode, u.comcode, to_char(ps.updatedate, 'yyyy-MM')
- union all
- -- 只計算主審項目中發現重大問題總數和發現問題總數,其它全部置為0
- select u.userCode,
- u.comCode,
- (to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
- -- 主審項目數
- 0 as chiefAuditCount,
- -- 主審出差天數
- 0 as chiefEvectionCount,
- -- 作為主審被質檢打分的總分,暫時繞過
- 0 as chiefTotalQualified,
- -- 作為主審被質檢打分的項目數
- 0 as chiefQualifiedProjectCount,
- -- 主審被評分項目總分,暫時繞過吧……
- 0 as chiefTotalGrade,
- -- 主審被評分項目數
- 0 as chiefGradedProjectCount,
- -- 主審項目中發現重大問題總數
- count(distinct mainprob.seqno) as chiefMainProbCount,
- --主審項目中發現問題總數
- count(distinct prob.seqno) as chiefProbelCount
- from t_atip_user u,
- t_atip_prostatustrack ps,
- t_atip_projectapproval proj
- left join t_atip_problem prob on prob.programcode =
- proj.projectapprovalcode
- and prob.isrecall = '1'
- left join t_atip_problem mainprob on mainprob.seqno = prob.seqno
- and mainprob.problemlevel in
- ('13', '14')
- where ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and proj.projectyear = to_char(ps.updatedate, 'yyyy')
- and proj.chiefaudit = u.usercode
- and proj.projectlevel = '1'
- and u.validstatus = '1'
- and u.istemporary = '0'
- group by u.usercode, u.comcode, to_char(ps.updatedate, 'yyyy-MM')
- union all
- -- 只計算主審被評分項目總分和主審被評分項目數,其它全部置為0
- -- 主審被評分總分的計算過程比較複雜,這裡採用的仍是優化之前的語句
- select USERCODE,
- COMCODE,
- SJMC,
- CHIEFAUDITCOUNT,
- CHIEFEVECTIONCOUNT,
- CHIEFTOTALQUALIFIED,
- CHIEFQUALIFIEDPROJECTCOUNT,
- round(nvl(sum(CHIEFTOTALGRADE), 0), 2) as CHIEFTOTALGRADE,
- nvl(sum(CHIEFGRADEDPROJECTCOUNT), 0) as CHIEFGRADEDPROJECTCOUNT,
- CHIEFMAINPROBCOUNT,
- CHIEFPROBELCOUNT
- from (select u.userCode,
- u.userName,
- u.comCode,
- o.comcname as comName,
- (to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
- -- 主審出差天數
- 0 as chiefEvectionCount,
- -- 作為主審被質檢打分的總分
- 0 as chiefTotalQualified,
- -- 作為主審被質檢打分的項目數
- 0 as chiefQualifiedProjectCount,
- -- 主審被評分項目總分
- (SELECT SUM(ma.avgtotalscore)
- FROM (SELECT m.projectcode,
- m.usercode,
- AVG(projtotalscore) avgtotalscore
- FROM (select substr(eval.projectcode, 0, 21) projectcode,
- eval.usercode,
- eval.evaluationusercode,
- sum(nvl(eval.score, 0)) as projtotalscore
- from t_atip_projectevaluation eval
- group by substr(eval.projectcode, 0, 21),
- eval.evaluationusercode,
- eval.usercode) m
- GROUP BY m.projectcode, m.usercode) ma
- WHERE ma.projectcode = proj.projectapprovalcode
- AND proj.projectlevel = '1'
- AND proj.projectyear =
- to_char(ps.updatedate, 'yyyy')
- AND ma.usercode = proj.chiefaudit
- AND proj.chiefaudit = u.usercode
- and ps.projectapprovalcode =
- proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- ps.projectapprovalcode)
- GROUP BY ma.usercode) as chiefTotalGrade,
- -- 主審被評分項目數
- (select sum(count(distinct eval.projectcode))
- from t_atip_projectevaluation eval
- where eval.projectcode = proj.projectapprovalcode
- and eval.usercode = proj.chiefaudit
- group by eval.projectcode) as chiefGradedProjectCount,
- -- 主審項目中發現重大問題總數
- 0 as chiefMainProbCount,
- --主審項目中發現問題總數
- 0 as chiefProbelCount,
- -- 主審項目數
- 0 as chiefAuditCount
- from t_atip_user u,
- t_atip_prostatustrack ps,
- t_atip_organization o,
- t_atip_projectapproval proj
- where o.comcode = u.comcode
- and ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno = (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode =
- ps.projectapprovalcode)
- and proj.projectyear = to_char(ps.updatedate, 'yyyy')
- and proj.chiefaudit = u.usercode
- and proj.projectlevel = '1')
- group by USERCODE, COMCODE, SJMC
- union all
- -- 只計算質檢項目總數和質檢總分,其它全部置為0
- select u.userCode,
- u.comCode,
- (to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
- -- 主審項目數
- 0 as chiefAuditCount,
- -- 主審出差天數
- 0 as chiefEvectionCount,
- -- 作為主審被質檢打分的總分
- round(nvl(sum(qual.score), 0), 2) as chiefTotalQualified,
- -- 作為主審被質檢打分的項目數
- count(distinct qual.projectcode) as chiefQualifiedProjectCount,
- -- 主審被評分項目總分
- 0 as chiefTotalGrade,
- -- 主審被評分項目數
- 0 as chiefGradedProjectCount,
- -- 主審項目中發現重大問題總數
- 0 as chiefMainProbCount,
- --主審項目中發現問題總數
- 0 as chiefProbelCount
- from t_atip_user u,
- t_atip_prostatustrack ps,
- t_atip_projectapproval proj
- left join t_atip_qualitytype qual on qual.projectcode =
- proj.projectapprovalcode
- where ps.projectapprovalcode = proj.projectapprovalcode
- and ps.projectstutas > '04'
- and ps.seqno =
- (select max(seqno)
- from t_atip_prostatustrack tempps
- where tempps.projectapprovalcode = ps.projectapprovalcode)
- and proj.projectyear = to_char(ps.updatedate, 'yyyy')
- and proj.chiefaudit = u.usercode
- and proj.projectlevel = '1'
- and u.validstatus = '1'
- and u.istemporary = '0'
- group by u.usercode, u.comcode, to_char(ps.updatedate, 'yyyy-MM'))
- group by USERCODE, COMCODE, SJMC
本文转自 斯然在天边 51CTO博客,原文链接:http://blog.51cto.com/winters1224/1111822,如需转载请自行联系原作者