login.j执行时间长

doris

3.8版本兼容5.2.7，使用3.8版本portal。

login.j执行时间长，大约4s，发现每次登录时都执行下面的全表查询，由于我们SA_OPPerson、SA_OPOrg都有近两万条数据，所以登录速度很慢。是否可以在哪里优化一下？

2020-01-14 18:58:28 [http-8080-24] [com.justep.system.data.impl.TableUtilsImplement] WARN: queryLimit: 10000, exec KSQL: SELECT SA_OPOrg.*,SA_OPPerson.sPhotoLastModified as sPhotoLastModified,SA_OPPerson.sNumb as sNumb  FROM SA_OPOrg SA_OPOrg optional join SA_OPPerson SA_OPPerson on SA_OPOrg.sPersonID = SA_OPPerson WHERE (SA_OPOrg.sValidState >= 0) ORDER BY SA_OPOrg.sSequence asc LIMIT 0, -1
--> sql:SELECT  SA_OPOrg.sID AS SA_OPOrg, SA_OPOrg.SNAME AS sName, SA_OPOrg.SCODE AS sCode, SA_OPOrg.SLONGNAME AS sLongName, SA_OPOrg.sFName AS sFName, SA_OPOrg.SFCODE AS sFCode, SA_OPOrg.sFID AS sFID, SA_OPOrg.SORGKINDID AS sOrgKindID, SA_OPOrg.SPERSONID AS sPersonID, SA_OPOrg.SVALIDSTATE AS sValidState, SA_OPOrg.SSEQUENCE AS sSequence, SA_OPPerson.SPHOTOLASTMODIFIED AS sPhotoLastModified, SA_OPPerson.SNUMB AS sNumb FROM SA_OPOrg SA_OPOrg LEFT JOIN SA_OPPerson SA_OPPerson ON SA_OPOrg.SPERSONID = SA_OPPerson.sID  WHERE SA_OPOrg.SVALIDSTATE >= ?  ORDER BY SA_OPOrg.SSEQUENCE ASC
--> binds:[0]
--> dataModel|M|/system/data
--> fnModelnull
java.lang.RuntimeException: queryLimit: 10000, ksql: SELECT SA_OPOrg.*,SA_OPPerson.sPhotoLastModified as sPhotoLastModified,SA_OPPerson.sNumb as sNumb  FROM SA_OPOrg SA_OPOrg optional join SA_OPPerson SA_OPPerson on SA_OPOrg.sPersonID = SA_OPPerson WHERE (SA_OPOrg.sValidState >= 0) ORDER BY SA_OPOrg.sSequence asc LIMIT 0, -1
        at com.justep.system.data.impl.TableUtilsImplement.queryWarn(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.BizData.query(Unknown Source)
        at com.justep.system.opm.OrgQuery.query(Unknown Source)
        at OPMProcedure.query(OPMProcedure.java:141)
        at sun.reflect.GeneratedMethodAccessor420.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.justep.micro.service.MicroServiceFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:879)
        at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:617)
        at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1774)
        at java.lang.Thread.run(Thread.java:745)

jishuang

把sql的执行时间打出来是多少

用的什么数据库？到数据库中执行sql看看索引是否起作用了

doris

jishuang 发表于 2020-1-17 16:40
把sql的执行时间打出来是多少

用的什么数据库？到数据库中执行sql看看索引是否起作用了

sqlserver数据库，在数据库直接执行，时间1583毫秒，两张表都是使用的 主键聚集索引。

jishuang

login.j每次请求都这样慢吗？是稳定重现的吗？开发版能不能重现这个问题，本地测试开发版中登录就没有执行这个sql语句的

1.把平台中的sql语句执行时间打出来
2.平台默认创建的不是只有主键这个索引的，在平台提供的初始化脚本中就能看出来


create index IDX_OPORG_CODE on SA_OPOrg (sCode)
create index IDX_OPORG_FCODE on SA_OPOrg (sFCode)
create index IDX_OPORG_FID on SA_OPOrg (sFID)
create index IDX_OPORG_KV on SA_OPOrg (sValidState, sOrgKindID)
create index IDX_OPORG_PARENT on SA_OPOrg (sParent)
create index IDX_OPORG_PERSON on SA_OPOrg (sPersonID)
create index IDX_OPORG_SEQUENCE on SA_OPOrg (sSequence)



create unique index IDX_OPPERSON_SCODE on SA_OPPerson (sCode)
create index IDX_OPPERSON_VALID on SA_OPPerson (sValidState)
create index IDX_OPPERSON_LN_C_VS on SA_OPPerson (sLoginName, sCode, sValidState)
create index IDX_OPPERSON_ORG on SA_OPPerson (sMainOrgID)

malixin

doris 发表于 2020-1-17 17:08
sqlserver数据库，在数据库直接执行，时间1583毫秒，两张表都是使用的 主键聚集索引。
...

遇到同样的问题，我这卡的比你时间还长，用户3000多人，求优化。

jishuang

请先看看4楼的回复