查询显示数据大于300条明显慢,什么原因.

badnjcf

action:
select PCD.*,JOBPBOL.QTY_F as JOBPBOLQTY_F,JOBPBOL.QTY as JOBPBOLQTY,JOBPBOL.RQTY_F as JOBPBOLRQTY_F,JOBPBOL.QTY - SQL.ISNULL(JOBPBOL.QTY_F, 0) as JOBPBOLQTY_REMAIN
    from PCD PCD
     optional  join JOBPBOL JOBPBOL on JOBPBOL = PCD.HID

filter:还有两个金额计算列

总数据也只有一两千条..
分页300条开始就慢了....不分页就更慢了..但是用到最好不分页..
重点:查询时会刚开始只显示30条等了很久才会显示下面的.很慢

badnjcf

有人回答吗?给个回复呀,是什么原因,还是说就是这样的?

jishuang

把sql数据拿到数据库执行看看，确认相关的索引都创建了吗?

badnjcf

数据库 很快的啊..下面是控制台 也只用几秒,就是显示的时候慢...会不会是配置哪里问题..
下面图是效果图,,前面30条马上出来...后面就要很久..

2021-11-16 11:43:00 [SQL Time]sql 执行时间：17ms, exec KSQL: SELECT PCD.*,JOBPBOL.QTY_F as JOBPBOLQTY_F,JOBPBOL.QTY as JOBPBOLQTY,JOBPBOL.RQTY_F as JOBPBOLRQTY_F,JOBPBOL.QTY - SQL.ISNULL(JOBPBOL.QTY_F, 0) as JOBPBOLQTY_REMAIN  FROM PCD PCD  optional  join JOBPBOL JOBPBOL on JOBPBOL = PCD.HID WHERE ((APPR_FLAG='V') AND ((PCD.TRAN_DATE>='2021-11-01' or '2021-11-01'='') and (PCD.TRAN_DATE<='2021-11-30' or '2021-11-30'='') and sql.isnull(PCD.EMPCODE,'') like '%%' and sql.isnull(PCD.FRM_NAMEC,'') like '%%' and sql.isnull(PCD.PBOL_CODE,'') like '%%' and sql.isnull(PCD.PBOL_DESC,'') like '%%' and sql.isnull(PCD.JOB_NO,'') like '%%')) ORDER BY PCD.APPR_FLAG2 ASC,PCD.TRAN_DATE ASC,PCD.JOB_NO ASC,PCD.PBOL_CODE ASC,PCD.BOL_CODE ASC LIMIT 0, -1
  --> sql:SELECT  PCD.QTY_F AS QTY_F, PCD.PIECE_TYPE AS PIECE_TYPE, PCD.P_COST AS P_COST, PCD.ITEM_NO AS ITEM_NO, PCD.APPR_FLAG AS APPR_FLAG, PCD.APPR_DATE AS APPR_DATE, PCD.PBOL_DESC AS PBOL_DESC, PCD.FRM_NAMEC AS FRM_NAMEC, PCD.SYSUSER AS SYSUSER, PCD.APPR_FLAG2 AS APPR_FLAG2, PCD.UNIT_PRICE AS UNIT_PRICE, PCD.PCFLAG AS PCFLAG, PCD.YOURCOLOR AS YOURCOLOR, PCD.RBOL_CODE AS RBOL_CODE, PCD.RBOL_DESC AS RBOL_DESC, JOBPBOL.QTY_F AS JOBPBOLQTY_F, PCD.BOL_DESC AS BOL_DESC, PCD.QTY AS QTY, JOBPBOL.RQTY_F AS JOBPBOLRQTY_F, PCD.APPR_USER AS APPR_USER, PCD.PBOL_CODE AS PBOL_CODE, PCD.JOB_NO AS JOB_NO, PCD.REMARK AS REMARK, JOBPBOL.QTY AS JOBPBOLQTY, PCD.HID AS HID, ( JOBPBOL.QTY - ISNULL(JOBPBOL.QTY_F, ?) ) AS JOBPBOLQTY_REMAIN, PCD.APPR_DATE2 AS APPR_DATE2, PCD.BOL_CODE AS BOL_CODE, PCD.SYSDATE AS SYSDATE, PCD.VERSION AS version, PCD.EMPCODE AS EMPCODE, PCD.APPR_USER2 AS APPR_USER2, PCD.fID AS PCD, PCD.FILE_NO AS FILE_NO, PCD.SOFID AS SOFID, PCD.HOURS AS HOURS, PCD.BOL_TYPE AS BOL_TYPE, PCD.P_TIME AS P_TIME, PCD.TRAN_DATE AS TRAN_DATE, PCD.YOURMODEL AS YOURMODEL FROM PCD PCD LEFT JOIN JOBPBOL JOBPBOL ON (JOBPBOL.fID = PCD.HID)  AND (JOBPBOL.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')   WHERE ( (APPR_FLAG = ?) AND ( ( (PCD.TRAN_DATE >= ?) OR (? = ?) ) AND ( (PCD.TRAN_DATE <= ?) OR (? = ?) ) AND (isnull(PCD.EMPCODE, ?) LIKE ?) AND (isnull(PCD.FRM_NAMEC, ?) LIKE ?) AND (isnull(PCD.PBOL_CODE, ?) LIKE ?) AND (isnull(PCD.PBOL_DESC, ?) LIKE ?) AND (isnull(PCD.JOB_NO, ?) LIKE ?) ) ) AND (PCD.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  ORDER BY PCD.APPR_FLAG2 ASC, PCD.TRAN_DATE ASC, PCD.JOB_NO ASC, PCD.PBOL_CODE ASC, PCD.BOL_CODE ASC
--> binds:[0, V, 2021-11-01, 2021-11-01, , 2021-11-30, 2021-11-30, , , %%, , %%, , %%, , %%, , %%]

badnjcf

jishuang 发表于 2021-11-16 09:08
把sql数据拿到数据库执行看看，确认相关的索引都创建了吗?

好了,解决了应该是两个计算列慢......前台的计算列好慢啊.加到action应该会好