自定义sql视图处理效率是不是太低了呢

我写了一个自定义过滤sql（列出所有登陆用户参与（抄送或审批）某表单流程的记录)：

var userid =getWebUser().getId();
var sql="SELECT * FROM TLK_FM_DISPATCH where id in(select doc.id from  GALLOPSOA.T_DOCUMENT  doc,GALLOPSOA.T_FLOW_INTERVENTION pen,GALLOPSOA.T_FLOWSTATERT fs,GALLOPSOA.T_NODERT node,GALLOPSOA.T_ACTORRT actor  where doc.id = pen.id and doc.id =fs.docid and fs.id =node.flowstatert_id and  node.id = actor.nodert_id and doc.issubdoc is null and doc.parent is null and doc.state is not null and doc.statelabel is not null  and actor.actorid ='";
sql += userid;
sql += "' UNION select doc.id from  GALLOPSOA.T_DOCUMENT  doc,GALLOPSOA.T_ACTORHIS ahis,GALLOPSOA.T_RELATIONHIS rhis,GALLOPSOA.T_FLOW_INTERVENTION pen,GALLOPSOA.T_FLOWSTATERT fs where doc.id=rhis.docid and rhis.id = ahis.nodehis_id and doc.id = pen.id and doc.id =fs.docid and doc.issubdoc is null and doc.parent is null and doc.state is not null and doc.statelabel is not null  and ahis.actorid='";
sql += userid ;
sql +="')";
sql;

但是系统具体在外面包含了3层无用的sql
11:24:49,260  INFO AbstractDocStaticTblDAO:1228 - MY SQL: SELECT * FROM (SELECT * FROM (SELECT * FROM TLK_FM_DISPATCH where id in(select doc.id from  GALLOPSOA.T_DOCUMENT  doc,GALLOPSOA.T_FLOW_INTERVENTION pen,GALLOPSOA.T_FLOWSTATERT fs,GALLOPSOA.T_NODERT node,GALLOPSOA.T_ACTORRT actor  where doc.id = pen.id and doc.id =fs.docid and fs.id =node.flowstatert_id and  node.id = actor.nodert_id and doc.issubdoc is null and doc.parent is null and doc.state is not null and doc.statelabel is not null  and actor.actorid ='11e1-af7b-ac37c5d3-a3b2-4960a74ab202' UNION select doc.id from  GALLOPSOA.T_DOCUMENT  doc,GALLOPSOA.T_ACTORHIS ahis,GALLOPSOA.T_RELATIONHIS rhis,GALLOPSOA.T_FLOW_INTERVENTION pen,GALLOPSOA.T_FLOWSTATERT fs where doc.id=rhis.docid and rhis.id = ahis.nodehis_id and doc.id = pen.id and doc.id =fs.docid and doc.issubdoc is null and doc.parent is null and doc.state is not null and doc.statelabel is not null  and ahis.actorid='11e1-af7b-ac37c5d3-a3b2-4960a74ab202')) table_orderby WHERE DOMAINID ='11e1-ae18-d04ada40-a3b2-4960a74ab202' ORDER BY table_orderby.ID ) AS TB LIMIT 0,15

这极大的降低了系统效率。

您好，这样做是有用的。因为平台企业域数据、软件数据之间是隔离的。从这里可以体现。这里sql语句的作用具体请看下面的注释：

1. SELECT * FROM
   
2. (
   
3. SELECT * FROM
   
4. (
   
5. --根据过滤的ID查询表单数据
   
6. SELECT * FROM TLK_FM_DISPATCH where id in
   
7. (
   
8. --获取符合条件的记录id
   
9. select doc.id from  GALLOPSOA.T_DOCUMENT  doc,GALLOPSOA.T_FLOW_INTERVENTION pen,GALLOPSOA.T_FLOWSTATERT fs,GALLOPSOA.T_NODERT node,GALLOPSOA.T_ACTORRT actor  where doc.id = pen.id and doc.id =fs.docid and fs.id =node.flowstatert_id and  node.id = actor.nodert_id and doc.issubdoc is null and doc.parent is null and doc.state is not null and doc.statelabel is not null  and actor.actorid ='11e1-af7b-ac37c5d3-a3b2-4960a74ab202' UNION select doc.id from  GALLOPSOA.T_DOCUMENT  doc,GALLOPSOA.T_ACTORHIS ahis,GALLOPSOA.T_RELATIONHIS rhis,GALLOPSOA.T_FLOW_INTERVENTION pen,GALLOPSOA.T_FLOWSTATERT fs where doc.id=rhis.docid and rhis.id = ahis.nodehis_id and doc.id = pen.id and doc.id =fs.docid and doc.issubdoc is null and doc.parent is null and doc.state is not null and doc.statelabel is not null  and ahis.actorid='11e1-af7b-ac37c5d3-a3b2-4960a74ab202'
   
10. )
    
11. )
    
12. --根据企业域ID进行过滤，同时根据表单ID排序
    
13. table_orderby WHERE DOMAINID ='11e1-ae18-d04ada40-a3b2-4960a74ab202' ORDER BY table_orderby.ID
    
14. )
    
15. AS TB LIMIT 0,15

复制代码

xiexie,理解了。可以关闭