(FAQ)总账凭证基本信息查询，系统不响应，导致EAS Cloud系统整体性能慢
原因分析： 1.查询的sql语句中存在影响性能的distinct。Distinct 对sql结果集中所有的记录先进行排序，然后对重复的记录进行筛选。最后对重复的记录分别选出一条与原本不重复的记录一并显示出来。排序与筛选过程非常耗资源。 2.缺少必要的where条件。缺少必要的where条件，导致基表数据量大，与其它表走netsted loop连接耗用较多的cpu资源。由于nested loop连接是基表与附表的所有记录一一进行连接，发现附表有相等的记录后，返回相等的记录。如果基表数据量较大的话，与附表关连的次数较多，影响查询速度。 在OEM中抓取到对应的语句如下所示： INSERT INTO VTZI8JRL51QD152GWCQSTI6FN5D (KSQL_SEQ, ID, ENTRIESID) SELECT ROWNUM, KSQL_V1.* FROM (SELECT TMP_SUBSELECT_ALIAS."ID", TMP_SUBSELECT_ALIAS."ENTRIES.ID" FROM (SELECT * FROM (SELECT DISTINCT "VOUCHER".FID "ID","ENTRIES".FID "ENTRIES.ID", "VOUCHER".FNumber "NUMBER", "ENTRIES".FSeq "ENTRIES.SEQ" FROM T_GL_Voucher "VOUCHER" LEFT OUTER JOIN T_BD_Period "PERIOD" ON "VOUCHER".FPeriodID = "PERIOD".FID INNER JOIN T_ORG_Company "COMPANY" ON "VOUCHER".FCompanyID ="COMPANY".FID INNER JOIN T_GL_VoucherEntry "ENTRIES" ON "VOUCHER".FID = "ENTRIES".FBillID INNER JOIN T_BD_VoucherTypes "VOUCHERTYPE" ON "VOUCHER".FVoucherTypeID = "VOUCHERTYPE".FID INNER JOIN T_PM_User "CREATOR" ON "VOUCHER".FCreatorID = "CREATOR".FID INNER JOIN (SELECT DISTINCT "VOUCHER".FID "ID" FROM T_GL_Voucher "VOUCHER" LEFT OUTER JOIN T_BD_Period "PERIOD" ON "VOUCHER".FPeriodID ="PERIOD".FID INNER JOIN T_GL_VoucherEntry "ENTRIES" ON "VOUCHER".FID ="ENTRIES".FBillID WHERE ((("VOUCHER".FCompanyID = '10rwJm0RRV+NnAmjEvRTOcznrtQ=' AND "PERIOD".FNumber = 201101) AND "ENTRIES".FCurrencyID = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC') AND ("VOUCHER".FBizStatus <> 5))) "VCHIDQUERY" ON "VOUCHER".FID = "VCHIDQUERY"."ID" WHERE ((("COMPANY".FID = '10rwJm0RRV+NnAmjEvRTOcznrtQ=' AND "PERIOD".FNumber = 201101)) AND ("VOUCHER".FBizStatus >= 0)) AND ("VOUCHER".FBizStatus <> 5)) ORDER BY "NUMBER" DESC, "ID" ASC, "ENTRIES.SEQ" ASC) WHERE ROWNUM <= 2147483647 ) TMP_SUBSELECT_ALIAS) KSQL_V1 上图sql中灰色部分的WHERE条件在未打补丁前OEM中抓到相同语句是没有的。 解决方法： 1. 去掉上图sql中红色部分的DISTINCT，加上上图sql中灰色部分的WHERE条件。去掉不必要的sql排序与筛选以及减少连接的次数，提高查询效率。具体是通过打补丁来解决。 具体补下号如下所示: EAS6.0 版本的补丁号是PT043837 EAS7.0.1 版本的补丁号是PT044607 打上BOS补丁PT048903(适用于eas60、701,主要解决子过滤条件丢失的bug)。 关键字 凭证基本信息查询慢