用Oracle存储100万数据为何前端列表显示太慢？

Story123

用Oracle存储100万数据为何前端列表显示太慢？

jishuang

用分页查询
http://docs.wex5.com/bex5-deploy-question-list-10027/

Story123

jishuang 发表于 2019-2-20 15:25
用分页查询
http://docs.wex5.com/bex5-deploy-question-list-10027/

测试就是用这个方法的，但数据还是刷新很慢

jishuang

sql语句打出来看看sql语句中有分页了吗？
看看sql的执行时间
http://bbs.wex5.com/forum.php?mo ... 4&pid=165004035

Story123

jishuang 发表于 2019-2-20 15:53
sql语句打出来看看sql语句中有分页了吗？
看看sql的执行时间
http://bbs.wex5.com/forum.php?mod=redirect& ...

这是控制台打印的
2019-02-20 16:08:50  exec KSQL: SELECT Wl_customerinfo.fCustomerName as fCustomerName,Wl_customerinfo.fCertNo as fCertNo,Wl_businessapplication.fDocumentSequence as fDocumentSequence,Wl_businessapplication.fBusinessDate as fBusinessDate,Wl_businessapplication.fDealers as fDealers,Wl_businessapplication.fSalesman as fSalesman,Wl_businessapplication  FROM Wl_businessapplication Wl_businessapplication  join Wl_customerinfo Wl_customerinfo on Wl_customerinfo.fCustomerinfoParentlink = Wl_businessapplication WHERE (Wl_businessapplication.fOrgID = :currentOgnID()) ORDER BY fBusinessDate DESC LIMIT 0, 20
--> sql:SELECT  Wl_businessapplication.fID, Wl_businessapplication.FBUSINESSDATE AS fBusinessDate, Wl_customerinfo.FCERTNO AS fCertNo, Wl_customerinfo.FCUSTOMERNAME AS fCustomerName, Wl_businessapplication.FDEALERS AS fDealers, Wl_businessapplication.FDOCUMENTSEQUENCE AS fDocumentSequence, Wl_businessapplication.FSALESMAN AS fSalesman FROM wl_businessapplication Wl_businessapplication JOIN wl_customerinfo Wl_customerinfo ON Wl_customerinfo.FCUSTOMERINFOPARENTLINK = Wl_businessapplication.fID  WHERE Wl_businessapplication.FORGID =  ?   ORDER BY fBusinessDate DESC
--> binds:[ORG01]
--> dataModel|M|/SAAS/common/data
--> fnModelnull
2019-02-20 16:08:50
exec KSQL: SELECT Wl_customerinfo.fCustomerName as fCustomerName,Wl_customerinfo.fCertNo as fCertNo,Wl_businessapplication.fDocumentSequence as fDocumentSequence,Wl_businessapplication.fBusinessDate as fBusinessDate,Wl_businessapplication.fDealers as fDealers,Wl_businessapplication.fSalesman as fSalesman,Wl_businessapplication  FROM Wl_businessapplication Wl_businessapplication  join Wl_customerinfo Wl_customerinfo on Wl_customerinfo.fCustomerinfoParentlink = Wl_businessapplication WHERE (Wl_businessapplication.fOrgID = :currentOgnID()) ORDER BY fBusinessDate DESC LIMIT 0, 20
  --> sql:SELECT ROWNUM AS JUSTEP_RN__, JUSTEP_TABLE__.* FROM (SELECT  Wl_businessapplication.fID, Wl_businessapplication.FBUSINESSDATE AS fBusinessDate, Wl_customerinfo.FCERTNO AS fCertNo, Wl_customerinfo.FCUSTOMERNAME AS fCustomerName, Wl_businessapplication.FDEALERS AS fDealers, Wl_businessapplication.FDOCUMENTSEQUENCE AS fDocumentSequence, Wl_businessapplication.FSALESMAN AS fSalesman FROM wl_businessapplication Wl_businessapplication JOIN wl_customerinfo Wl_customerinfo ON Wl_customerinfo.FCUSTOMERINFOPARENTLINK = Wl_businessapplication.fID  WHERE Wl_businessapplication.FORGID =  ?   ORDER BY fBusinessDate DESC) JUSTEP_TABLE__  WHERE ROWNUM<21
--> binds:[ORG01]
2019-02-20 16:08:50 Connection product name:Oracle
2019-02-20 16:08:50 Connection product version:Oracle Database 11g Release 11.1.0.0.0 - Production
处理隔离字段前：SELECT ROWNUM AS JUSTEP_RN__, JUSTEP_TABLE__.* FROM (SELECT  Wl_businessapplication.fID, Wl_businessapplication.FBUSINESSDATE AS fBusinessDate, Wl_customerinfo.FCERTNO AS fCertNo, Wl_customerinfo.FCUSTOMERNAME AS fCustomerName, Wl_businessapplication.FDEALERS AS fDealers, Wl_businessapplication.FDOCUMENTSEQUENCE AS fDocumentSequence, Wl_businessapplication.FSALESMAN AS fSalesman FROM wl_businessapplication Wl_businessapplication JOIN wl_customerinfo Wl_customerinfo ON Wl_customerinfo.FCUSTOMERINFOPARENTLINK = Wl_businessapplication.fID  WHERE Wl_businessapplication.FORGID =  ?   ORDER BY fBusinessDate DESC) JUSTEP_TABLE__  WHERE ROWNUM<21
处理隔离字段后：SELECT ROWNUM AS JUSTEP_RN__, JUSTEP_TABLE__.* FROM (SELECT  Wl_businessapplication.fID, Wl_businessapplication.FBUSINESSDATE AS fBusinessDate, Wl_customerinfo.FCERTNO AS fCertNo, Wl_customerinfo.FCUSTOMERNAME AS fCustomerName, Wl_businessapplication.FDEALERS AS fDealers, Wl_businessapplication.FDOCUMENTSEQUENCE AS fDocumentSequence, Wl_businessapplication.FSALESMAN AS fSalesman FROM wl_businessapplication Wl_businessapplication JOIN wl_customerinfo Wl_customerinfo ON Wl_customerinfo.FCUSTOMERINFOPARENTLINK = Wl_businessapplication.fID  WHERE Wl_businessapplication.FORGID =  ?   ORDER BY fBusinessDate DESC) JUSTEP_TABLE__  WHERE ROWNUM<21
2019-02-20 16:08:50 Connection product name:Oracle
2019-02-20 16:08:50 Connection product version:Oracle Database 11g Release 11.1.0.0.0 - Production

jishuang

执行时间也打印出来啊

Story123

jishuang 发表于 2019-2-20 16:39
执行时间也打印出来啊

[Action Time]action执行时间：36ms,/SA/task/taskView/taskViewProcess,mainActivity,querySubmitTask2Action

平台不是自带分页吗？

jishuang

这个是执行36毫秒不是秒

任务数据查询慢？

Story123

jishuang 发表于 2019-2-21 11:04
这个是执行36毫秒不是秒

任务数据查询慢？

是的，就是打开一个菜单数据量大的话，查询列表数据很慢

jishuang

具体打开哪个功能慢