场景:
新物资系统中发现有不少sql使用了视图,从语句上来看,可读性稍好些,但性能很差,经加适当索引后,语句的性能有改观,但仍然存在表扫描的情况。笔者尝试去掉视图后,发现执行计划改善,性能提高。
新物资系统中发现有不少sql使用了视图,从语句上来看,可读性稍好些,但性能很差,经加适当索引后,语句的性能有改观,但仍然存在表扫描的情况。笔者尝试去掉视图后,发现执行计划改善,性能提高。
修改前的语句:
with temp as(
select *
from (
select MATERIAL_ID, rownumber() over () as rn
from MAT_MATERIAL
where REQPLAN_ID=2119011
and ABANDON_STATE=0 ) as tableTemp
where tableTemp.rn between 1 and 15 )
select mm.MAT_UNIQCODE, mm.MATERIAL_ID, mm.ANNEXFLAG , bc.CORP_NAME as
needCorp, pv.PKGNAME, pv.PROJECT_NAME, pv.ENGINEER_CODE,
pv.ENGINEER_NAME, pv.KYPFNUMBER, pv.ASSETATTR_NAME, pv.PRONATURE_NAME,
pv.PROJECTATTR_NAME, mmt.ZBGS_NAME, mmt.TYPE_NAME, mmt.SPECTYPE,
mpl.TECH_PARA, mmt.MEAUNIT_NAME, mmt.VOL_NAME, mm.REQ_AMOUNT,
mm.DELIVER_TIME, mm.DELIVER_TIME_FLAG, mm.DELIVER_ADDRESS,
mm.PROPH_MANUF, mm.REMARK, mm.BUDGET, mm.ANNEXFLAG, a.PLANSOURCE,
a.REQPLAN_CODE, a.WORKFLOW_STATE, su.user_name, mg.WORKFLOW_STATE as
gatherState, rownumber() over (order by mm.MATERIAL_ID) as rn
from MAT_MATERIAL mm
left join MAT_REQPLAN a
on mm.REQPLAN_ID=a.REQPLAN_ID
left join BAS_CORP as bc
on a.CROP_CODE=bc.CORP_CODE
left join MATERIALINFO_VIEW mmt
on mm.THESAURUS_ID=mmt.THESAURUS_ID
left join MAT_PLANMATTECHPAR mpl
on mm.MATERIAL_ID=mpl.MATERIAL_ID
left join PROJECTINFO_VIEW pv
on mm.PROJECT_ID=pv.PROJECT_ID
left join sys_user su
on su.user_id=a.AUTHOR_ID
left join MAT_GATHERPLAN mg
on mm.GATHERPLAN_ID=mg.GATHERPLAN_ID
where exists (
select temp.MATERIAL_ID
from temp
where temp.MATERIAL_ID=mm.MATERIAL_ID )
with ur
分析过程:
运用questcentral的sql tuning查看其 cost为 5077.70.
主要消耗在两个表(MAT_THESAURUS、MAT_PROJECT)的表扫描上。而这两个表分别在视图(MATERIALINFO_VIEW、PROJECTINFO_VIEW)里调用。从连接条件上看索引已经加上了,但没有使用到。尝试去掉视图后可以使用索引。
优化后的sql:
with temp as(
select *
from (
select MATERIAL_ID, rownumber() over () as rn
from MAT_MATERIAL
where REQPLAN_ID=2119011
and ABANDON_STATE=0 ) as tableTemp
where tableTemp.rn between 1 and 15 )
select mm.MAT_UNIQCODE, mm.MATERIAL_ID, mm.ANNEXFLAG , bc.CORP_NAME as
needCorp, cc.PKGNAME,bb.PROJECT_NAME, bb.ENGINEER_CODE,bb.ENGINEER_NAME,
bb.KYPFNUMBER, dd.ASSETATTR_NAME, ee.PRONATURE_NAME, ff.PROJECTATTR_NAME
, e.NAME as ZBGS_NAME, b.TYPE_NAME, aa.SPECTYPE, mpl.TECH_PARA, d.NAME
as MEAUNIT_NAME, c.name as VOL_NAME, mm.REQ_AMOUNT, mm.DELIVER_TIME,
mm.DELIVER_TIME_FLAG, mm.DELIVER_ADDRESS, mm.PROPH_MANUF, mm.REMARK,
mm.BUDGET, mm.ANNEXFLAG, a.PLANSOURCE, a.REQPLAN_CODE, a.WORKFLOW_STATE,
su.user_name, mg.WORKFLOW_STATE as gatherState, rownumber() over (order
by mm.MATERIAL_ID) as rn
from MAT_MATERIAL mm
left join MAT_REQPLAN a
on mm.REQPLAN_ID=a.REQPLAN_ID
left join BAS_CORP as bc
on a.CROP_CODE=bc.CORP_CODE
left join MAT_THESAURUS aa
on mm.THESAURUS_ID=aa.THESAURUS_ID
left join MAT_MATTYPE b
on aa.MATTYPE_ID = b.MATTYPE_ID
left join MAT_VOLLEV c
on aa.VOLLEV_ID = c.VOLLEV_ID
left join MAT_MEAUNIT d
on aa.MEAUNIT_ID = d.MEAUNIT_ID
left join MAT_ZBGS e
on aa.ZBGS_ID=e.ZBGS_ID
left join MAT_PLANMATTECHPAR mpl
on mm.MATERIAL_ID=mpl.MATERIAL_ID
left join MAT_PROJECT bb
on mm.PROJECT_ID=bb.PROJECT_ID
left join MAT_PROPKG cc
on bb.PROPKG_ID = cc.PROPKG_ID
left join MAT_ASSETATTR dd
on bb.ASSETATTR_ID = dd.ASSETATTR_ID
left join MAT_PRONATURE ee
on bb.PRONATURE_ID =ee.PRONATURE_ID
left join MAT_PROJECTATTR ff
on ff.PROJECTATTR_ID=bb.PROJECTATTR_ID
left join sys_user su
on su.user_id=a.AUTHOR_ID
left join MAT_GATHERPLAN mg
on mm.GATHERPLAN_ID=mg.GATHERPLAN_ID
where mm.REQPLAN_ID=2119011 - 增加的条件,以告知优化器使用索引
and exists (
select temp.MATERIAL_ID
from temp
where temp.MATERIAL_ID=mm.MATERIAL_ID )
with ur
优化后没有表扫描,cost为 261.31
需要说明的是,本案例只是说明有这种情况存在,并不是表明视图的性能差,不能用,而是这种情况下(查询条件里没有视图的关键字)没有用到索引,如果查询语句里有明确的视图关键字条件的话,视图还是会用到索引的。
-- The End --