1/7用友股份-LE支持服务业务本部技术方案--《总账-集团对账-协同凭证-协同中心查询SQL执行效率较低的解决方案》建立日期：2014-01-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2014-00112/7文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2014-01-20宋文凯技术支持部V1.0版本领域模块备注3/7目录总账-集团对账-协同凭证-协同中心查询SQL执行效率较低的解决方案............4一、问题情景............................................................................................................4二、问题原因............................................................................................................6三、解决方案............................................................................................................64/7总账-集团对账-协同凭证-协同中心查询SQL执行效率较低的解决方案注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！一、问题情景总账-集团对账-协同凭证-协同中心查询SQL执行效率较低--效率SQL执行计划信息1SQL_ID81duk1hdfdscg,childnumber02-------------------------------------3selectdistinctgl_reconcile.pk_reconcile,gl_reconcile.code,4gl_reconcile.name,gl_reconcile.shortname,gl_reconcile.pk_corp,5gl_reconcile.pk_glorgbookfromgl_reconcilewhere(6gl_reconcile.pk_reconcilein(selectgl_reconcilesub.pk_reconcilefrom7gl_reconcilesubwheregl_reconcilesub.pk_selfglorgbook=8'0001781000000000C30L'orgl_reconcilesub.pk_otherglorgbook=9'0001781000000000C30L'))orgl_reconcile.pk_glorgbook=10'0001781000000000C30L'orderbygl_reconcile.code1112Planhashvalue:27312742581314---------------------------------------------------------------------------------------15|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|16---------------------------------------------------------------------------------------17|0|SELECTSTATEMENT||||53(100)||18|1|SORTORDERBY||461|37341|53(4)|00:00:01|19|*2|FILTER||||||20|3|TABLEACCESSFULL|GL_RECONCILE|8857|700K|51(0)|00:00:01|5/721|*4|TABLEACCESSFULL|GL_RECONCILESUB|1|63|76(2)|00:00:01|22---------------------------------------------------------------------------------------2324PredicateInformation(identifiedbyoperationid):25---------------------------------------------------26272-filter(("GL_RECONCILE"."PK_GLORGBOOK"='0001781000000000C30L'ORISNOT28NULL))294-filter(("GL_RECONCILESUB"."PK_RECONCILE"=:B1AND30("GL_RECONCILESUB"."PK_SELFGLORGBOOK"='0001781000000000C30L'OR31"GL_RECONCILESUB"."PK_OTHERGLORGBOOK"='0001781000000000C30L')))3233Note34-----35-dynamicsamplingusedforthisstatement(level=4)36--如下为修改前的SQL语句—正式环境下执行耗时27831msselectdistinctgl_reconcile.pk_reconcile,gl_reconcile.code,gl_reconcile.name,gl_reconcile.shortname,gl_reconcile.pk_corp,gl_reconcile.pk_glorgbookfromgl_reconcilewhere(gl_reconcile.pk_reconcilein(selectgl_reconcilesub.pk_reconcilefromgl_reconcilesubwheregl_reconcilesub.pk_selfglorgbook='0001781000000000C30L'orgl_reconcilesub.pk_otherglorgbook='0001781000000000C30L'))orgl_reconcile.pk_glorgbook='0001781000000000C30L'orderbygl_reconcile.code6/7二、问题原因--SQl中有降低执行效率的语句，使用相率更高的union/unionall来代替or通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.三、解决方案--根据执行计划修改SQL语句，替换影响效率的语句or--如下为修改后的SQL语句（使用union代替or）--执行耗时204msselect*from(selectdistinctgl_reconcile.pk_reconcile,gl_reconcile.code,gl_reconcile.name,gl_reconcile.shortname,gl_reconcile.pk_corp,gl_reconcile.pk_glorgbookfromgl_reconcilewhere(gl_reconcile.pk_reconcilein(selectgl_reconcilesub.pk_reconcilefromgl_reconcilesubwheregl_reconcilesub.pk_selfglorgbook='0001781000000000C30L'orgl_reconcilesub.pk_otherglorgbook='0001781000000000C30L'))unionselectdistinctgl_reconcile.pk_reconcile,gl_reconcile.code,gl_reconcile.name,gl_reconcile.shortname,gl_reconcile.pk_corp,gl_reconcile.pk_glorgbookfromgl_reconcilewheregl_reconcile.pk_glorgbook='0001781000000000C30L')a7/7orderbya.code--经过调试SQL语句后执行效率提升百倍
