分页优化
在程序中涉及分页SQL的一些性能问题,这里总结一下:
- 优化在查询列中有子查询时优化效果明显
耗时(s) | 不分页 | 未处理前分页 | 处理后分页 | 提升 |
---|---|---|---|---|
未排序 | 0.5 | 13.9 | 0.25 | 98% |
排序 | 0.5 | 14.25 | 0.296 | 97.9% |
- 不分页SQL
-- 耗时0.5s
SELECT ROWNUM ROWINDEX,
(select tbzt
from t_dw_sbbg_bgsblb
where bgsbid = t.obj_id
and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
from T_SB_ZWYC_JJ T
WHERE 1 = 1
AND exists (select bgsbid
from t_dw_sbbg_bgsblb
where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
and bgsbid = t.obj_id)
- 未处理前的分页
-- 耗时13.9s
select *
from (SELECT ROWNUM ROWINDEX,
(select tbzt
from t_dw_sbbg_bgsblb
where bgsbid = t.obj_id
and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
from T_SB_ZWYC_JJ T
WHERE 1 = 1
AND exists
(select bgsbid
from t_dw_sbbg_bgsblb
where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
and bgsbid = t.obj_id))
WHERE ROWINDEX > 20
AND ROWINDEX <= 40
- 处理后的分页
--耗时 0.25
select *
from (SELECT ROWNUM ROWINDEX,
(select tbzt
from t_dw_sbbg_bgsblb
where bgsbid = t.obj_id
and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
from T_SB_ZWYC_JJ T
WHERE 1 = 1
AND exists
(select bgsbid
from t_dw_sbbg_bgsblb
where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
and bgsbid = t.obj_id) and ROWNUM<= 40 )
WHERE ROWINDEX > 20
排序
- 不分页
-- 0.5
SELECT ROWNUM ROWINDEX,
(select tbzt
from t_dw_sbbg_bgsblb
where bgsbid = t.obj_id
and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
from T_SB_ZWYC_JJ T
WHERE 1 = 1
AND exists (select bgsbid
from t_dw_sbbg_bgsblb
where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
and bgsbid = t.obj_id)
order by obj_id
- 未处理
--14.25s
select *
from (select ROWNUM ROWINDEX, tbzt from (SELECT
(select tbzt
from t_dw_sbbg_bgsblb
where bgsbid = t.obj_id
and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
from T_SB_ZWYC_JJ T
WHERE 1 = 1
AND exists
(select bgsbid
from t_dw_sbbg_bgsblb
where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
and bgsbid = t.obj_id) order by OBJ_ID))
WHERE ROWINDEX > 20
AND ROWINDEX <= 40
- 处理后
--0.296s
select *
from ( select ROWNUM ROWINDEX, tbzt from (SELECT
(select tbzt
from t_dw_sbbg_bgsblb
where bgsbid = t.obj_id
and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
from T_SB_ZWYC_JJ T
WHERE 1 = 1
AND exists
(select bgsbid
from t_dw_sbbg_bgsblb
where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
and bgsbid = t.obj_id)) where 1=1 and ROWNUM<= 40 )
WHERE ROWINDEX > 20
如果对你有帮助请点赞哦