sql语句优化

 

 

SELECT PPDMOBJECT.PUID, PPDMOBJECT.TIMESTAMP , PPDMOBJECT.POID, PPDMOBJECT.OWNINGSITEU, PPDMOBJECT.ISFROZEN, ppdmappobj.PARCHIVE_INFO, ppdmappobj.ROWNING_GROUPU, ppdmappobj.RLAST_MOD_USERU, ppdmappobj.ROWNING_USERU, ppdmappobj.PCREATION_DATE, ppdmappobj.PARCHIVE_DATE, ppdmappobj.PLAST_MOD_DATE, ppdmappobj.PBACKUP_DATE, pworkspaceobj.POBJECT_NAME, pworkspaceobj.POBJECT_DESC, pworkspaceobj.POBJECT_TYPE, pworkspaceobj.POBJECT_APPLICATION, pworkspaceobj.PREVISION_NUMBER, pworkspaceobj.PREVISION_LIMIT, pworkspaceobj.PDATE_RELEASED, pbomline.PARENTBVR FROM ppdmappobj INNER JOIN PPDMOBJECT ON ppdmappobj.PUID = PPDMOBJECT.PUID INNER JOIN pworkspaceobj ON ppdmappobj.PUID = pworkspaceobj.PUID INNER JOIN pbomline ON PPDMOBJECT.PUID = pbomline.PARENTBVR AND (pbomline.CHILDBV = 'LMxFeGa8SXUZVaK' OR pbomline.CHILDITEM = 'LMxFeGa8SXUZVaK' OR pbomline.PUID IN (SELECT PUID FROM PALTBOM WHERE PALTBOM.ALTPUID = 'LMxFeGa8SXUZVaK')) ORDER BY pworkspaceobj.POBJECT_NAME

  这一长段看了谁都不好受吧？

 

我来解释一下，ppdmappobj，PPDMOBJECT，pworkspaceobj是三个基表，也就是说工程里任意对象都会在这三个表中有记录，相同的对象PUID是唯一的。

 

仔细分析下来，这条语句先去内连接三个基表，找出有相同PUID的行来，然后再对后面的进行匹配

 

三个基表如果很大的，会慢死的。

 

优化如下。

 

select pbomline.PARENTBVR from pbomline where pbomline.CHILDBV =? union select pbomline.PARENTBVR from pbomline where pbomline.CHILDITEM = ? union select pbomline.PARENTBVR from pbomline inner join PALTBOM on PALTBOM.ALTPUID =? and pbomline.PUID=PALTBOM.puid

先找出符合条件的PUID，最后再去从基表中查找相关字段就快多了

 

有时候看着这些浪费时间的SQL语句，还真他妈的想杀人

评论

1 楼 xchbs 2008-09-10  

快把我看死了!