在达梦数据库中分页查询性能非常差

[king-neter]

达梦数据库自带的这个 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 性能是pagehelper改写使用的rownum 的100倍以上差距。建议修改达梦数据库的分页方式

总共2个语句正依次执行...

-- [执行语句1]:
SELECT * FROM 
           ( SELECT * 
                   FROM ( SELECT DISTINCT hz.id, 
                                hz.sskcjszz AS sbzt, 
                                '' AS ygh, 
                                hz.fylx, 
                                hz.ZJE AS sqje, 
                                 TO_CHAR(hz.SBSJ, 'yyyy-MM-dd HH24:mi:ss') AS sbsj, 
                               
                                hz.zt, 
                                hz.GZDWBM 
                           FROM t_gh_jhsy_sxf_hz hz 
                           JOIN WFWIPARTICIPANT w 
                             ON hz.PROCESS_ID = w.PROCESSINSTID 
                          WHERE hz.is_delete = '0' 
                            AND w.CURRENTSTATE = '10' 
                            AND w.participantid = '62979' 
                               ) hz 
             ORDER BY hz.sbsj DESC
                         ) TMP_PAGE
                          OFFSET 0 ROWS FETCH NEXT 10 ROWS O
-- 执行成功, 执行耗时265毫秒. 执行号:7460100

-- [执行语句2]:
SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( SELECT * FROM (  SELECT DISTINCT hz.id, 
                                hz.sskcjszz AS sbzt, 
                                '' AS ygh, 
                                hz.fylx, 
                                hz.ZJE AS sqje, 
                                 TO_CHAR(hz.SBSJ, 'yyyy-MM-dd HH24:mi:ss') AS sbsj, 
                               
                                hz.zt, 
                                hz.GZDWBM 
                           FROM t_gh_jhsy_sxf_hz hz 
                           JOIN WFWIPARTICIPANT w 
                             ON hz.PROCESS_ID = w.PROCESSINSTID 
                          WHERE hz.is_delete = '0' 
                            AND w.CURRENTSTATE = '10' 
                            AND w.participantid = '62979' 
                               ) hz 
             ORDER BY hz.sbsj DESC
                         ) 
                          TMP_PAGE) WHERE ROW_ID <= 10 AND RO
-- 执行成功, 执行耗时1分 43秒 427毫秒. 执行号:7460101

-- 2条语句执行成功

[abel533]

和 FirebirdDialect 语法一样，可以配置下面参数试试效果：

<property name="dialectAlias" value="dm=com.github.pagehelper.dialect.helper.FirebirdDialect"/>