各位大侠,本人有一SQL文,急需性能改善,忘各位大侠多多指点指点:
SELECT
ENRT_REP.ENRT_REP_OID AS ENRT_REP_OID
, ENRT_REP.DATA_INPT_DVS AS DATA_INPT_DVS
, ENRT_REP.ENRT_REP_SEQ_NO AS ENRT_REP_SEQ_NO
, ENRT_REP.FLT_ID_DATE AS FLT_ID_DATE
, ENRT_REP.FIS_FLT_ID_DATE AS FIS_FLT_ID_DATE
FROM VWV_TBT_ENRT_REP ENRT_REP
, (
SELECT
DISTINCT ENRT_GRID.ENRT_REP_OID as ENRT_REP_OID
FROM VWV_TBT_ENRT_GRID ENRT_GRID
WHERE ( ( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SL' )
or ( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SM' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XS' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XT' ))
and ENRT_GRID.REC_DEL_FLG = '0') TMP_ENRT_GRID
WHERE ENRT_REP.OBS_DATE||ENRT_REP.OBS_TIME BETWEEN '200806261035'
AND '200806261435'
and ((ENRT_REP.OBS_ALTTD_FROM between '70'
and '450')
or (ENRT_REP.OBS_ALTTD_TO between '70'
and '450'))
and ENRT_REP.TURB_LEVL_CD IN ('LGTP'
,'MOD'
,'MODP'
,'SEV'
,'EXT')
and ENRT_REP.REC_DEL_FLG = '0'
and ENRT_REP.ENRT_REP_OID = TMP_ENRT_GRID.ENRT_REP_OID
ORDER BY ENRT_REP.OBS_DATE desc
, ENRT_REP.OBS_TIME desc主要是SQL文里下面这一块数据量比较大:
ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SL' )
or ( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SM' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XS' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XT' ))
有可能有几万到几十万条,所以当数据一多的时候,我的程序就会运行时间很长,最后导致超时,忘大侠多多指教!
SELECT
ENRT_REP.ENRT_REP_OID AS ENRT_REP_OID
, ENRT_REP.DATA_INPT_DVS AS DATA_INPT_DVS
, ENRT_REP.ENRT_REP_SEQ_NO AS ENRT_REP_SEQ_NO
, ENRT_REP.FLT_ID_DATE AS FLT_ID_DATE
, ENRT_REP.FIS_FLT_ID_DATE AS FIS_FLT_ID_DATE
FROM VWV_TBT_ENRT_REP ENRT_REP
, (
SELECT
DISTINCT ENRT_GRID.ENRT_REP_OID as ENRT_REP_OID
FROM VWV_TBT_ENRT_GRID ENRT_GRID
WHERE ( ( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SL' )
or ( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SM' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XS' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XT' ))
and ENRT_GRID.REC_DEL_FLG = '0') TMP_ENRT_GRID
WHERE ENRT_REP.OBS_DATE||ENRT_REP.OBS_TIME BETWEEN '200806261035'
AND '200806261435'
and ((ENRT_REP.OBS_ALTTD_FROM between '70'
and '450')
or (ENRT_REP.OBS_ALTTD_TO between '70'
and '450'))
and ENRT_REP.TURB_LEVL_CD IN ('LGTP'
,'MOD'
,'MODP'
,'SEV'
,'EXT')
and ENRT_REP.REC_DEL_FLG = '0'
and ENRT_REP.ENRT_REP_OID = TMP_ENRT_GRID.ENRT_REP_OID
ORDER BY ENRT_REP.OBS_DATE desc
, ENRT_REP.OBS_TIME desc主要是SQL文里下面这一块数据量比较大:
ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SL' )
or ( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SM' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XS' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XT' ))
有可能有几万到几十万条,所以当数据一多的时候,我的程序就会运行时间很长,最后导致超时,忘大侠多多指教!
ENRT_REP.DATA_INPT_DVS AS DATA_INPT_DVS,
ENRT_REP.ENRT_REP_SEQ_NO AS ENRT_REP_SEQ_NO,
ENRT_REP.FLT_ID_DATE AS FLT_ID_DATE,
ENRT_REP.FIS_FLT_ID_DATE AS FIS_FLT_ID_DATE
FROM VWV_TBT_ENRT_REP ENRT_REP,
(SELECT DISTINCT ENRT_GRID.ENRT_REP_OID as ENRT_REP_OID
FROM VWV_TBT_ENRT_GRID ENRT_GRID
WHERE ( ( ENRT_GRID.GRID_CD_FS ='PM95' AND ENRT_GRID.GRID_CD_SS in('SL','SM')
or ( ENRT_GRID.GRID_CD_FS ='QN02' AND ENRT_GRID.GRID_CD_SS in('XS','XT'))
and ENRT_GRID.REC_DEL_FLG = '0') TMP_ENRT_GRID
WHERE ENRT_REP.OBS_DATE||ENRT_REP.OBS_TIME BETWEEN '200806261035' AND '200806261435'
and ((ENRT_REP.OBS_ALTTD_FROM between '70' and '450')
or (ENRT_REP.OBS_ALTTD_TO between '70' and '450'))
and ENRT_REP.TURB_LEVL_CD IN ('LGTP','MOD','MODP','SEV','EXT')
and ENRT_REP.REC_DEL_FLG = '0'
and ENRT_REP.ENRT_REP_OID = TMP_ENRT_GRID.ENRT_REP_OID
ORDER BY ENRT_REP.OBS_DATE desc, ENRT_REP.OBS_TIME desc ;虽然改了一下,但原语句看不出什么问题啊。
or ( ENRT_GRID.GRID_CD_FS in ('PM95') AND ENRT_GRID.GRID_CD_SS = 'SM' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02') AND ENRT_GRID.GRID_CD_SS = 'XS' )
or ( ENRT_GRID.GRID_CD_FS in ('QN02') AND ENRT_GRID.GRID_CD_SS = 'XT' ))
关键是上面的这两个字段是成对出现的,不能和在一起的。
就看这段SQL有什么优化的方法没有?
ENRT_GRID.GRID_CD_FS ='QN02' AND ENRT_GRID.GRID_CD_SS in('XS','XT')
和你下面的是一样的。
SELECT ENRT_REP.ENRT_REP_OID AS ENRT_REP_OID,
ENRT_REP.DATA_INPT_DVS AS DATA_INPT_DVS,
ENRT_REP.ENRT_REP_SEQ_NO AS ENRT_REP_SEQ_NO,
ENRT_REP.FLT_ID_DATE AS FLT_ID_DATE,
ENRT_REP.FIS_FLT_ID_DATE AS FIS_FLT_ID_DATE
FROM VWV_TBT_ENRT_REP ENRT_REP
WHERE EXISTS
(SELECT 1
FROM VWV_TBT_ENRT_GRID ENRT_GRID
WHERE ENRT_REP.ENRT_REP_OID = TMP_ENRT_GRID.ENRT_REP_OID
AND ((ENRT_GRID.GRID_CD_FS = 'PM95' AND
ENRT_GRID.GRID_CD_SS IN ('SL', 'SM')) OR
(ENRT_GRID.GRID_CD_FS = 'QN02' AND
ENRT_GRID.GRID_CD_SS IN ('XS', 'XT')))
AND ENRT_GRID.REC_DEL_FLG = '0')
AND ENRT_REP.OBS_DATE || ENRT_REP.OBS_TIME BETWEEN '200806261035' AND
'200806261435'
AND ((ENRT_REP.OBS_ALTTD_FROM BETWEEN '70' AND '450') OR
(ENRT_REP.OBS_ALTTD_TO BETWEEN '70' AND '450'))
AND ENRT_REP.TURB_LEVL_CD IN ('LGTP', 'MOD', 'MODP', 'SEV', 'EXT')
AND ENRT_REP.REC_DEL_FLG = '0'
ORDER BY ENRT_REP.OBS_DATE DESC, ENRT_REP.OBS_TIME DESC;
ENRT_REP.DATA_INPT_DVS AS DATA_INPT_DVS,
ENRT_REP.ENRT_REP_SEQ_NO AS ENRT_REP_SEQ_NO,
ENRT_REP.FLT_ID_DATE AS FLT_ID_DATE,
ENRT_REP.FIS_FLT_ID_DATE AS FIS_FLT_ID_DATE
FROM VWV_TBT_ENRT_REP ENRT_REP,
(SELECT DISTINCT ENRT_GRID.ENRT_REP_OID as ENRT_REP_OID
FROM VWV_TBT_ENRT_GRID ENRT_GRID
WHERE ( ENRT_GRID.GRID_CD_FS ='PM95' AND ENRT_GRID.GRID_CD_SS ='SL' or
ENRT_GRID.GRID_CD_FS ='PM95' AND ENRT_GRID.GRID_CD_SS ='SM' or
ENRT_GRID.GRID_CD_FS ='QN02' AND ENRT_GRID.GRID_CD_SS ='XS' or
ENRT_GRID.GRID_CD_FS ='QN02' AND ENRT_GRID.GRID_CD_SS ='XT')
and ENRT_GRID.REC_DEL_FLG = '0'
) TMP_ENRT_GRID
WHERE ENRT_REP.OBS_DATE||ENRT_REP.OBS_TIME BETWEEN '200806261035' AND '200806261435'
and ((ENRT_REP.OBS_ALTTD_FROM between '70' and '450')
or (ENRT_REP.OBS_ALTTD_TO between '70' and '450'))
and ENRT_REP.TURB_LEVL_CD IN ('LGTP','MOD','MODP','SEV','EXT')
and ENRT_REP.REC_DEL_FLG = '0'
and ENRT_REP.ENRT_REP_OID = TMP_ENRT_GRID.ENRT_REP_OID
ORDER BY ENRT_REP.OBS_DATE desc, ENRT_REP.OBS_TIME desc ;
from
.....
ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SL' )
union all
select ....
from
.....
( ENRT_GRID.GRID_CD_FS in ('PM95')
AND ENRT_GRID.GRID_CD_SS = 'SM' )
union all
select ....
from
.....
( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XS' )
union all
select ....
from
.....
( ENRT_GRID.GRID_CD_FS in ('QN02')
AND ENRT_GRID.GRID_CD_SS = 'XT' ))
1.首先是一个LIST(该LIST里面存放的是由GRID_CD_FS跟GRID_CD_SS组合而成的每一条记录)
2.该LIST里面的每一条记录就是该SQL当中数据量很大的地方。
注:现在该LIST里面重复的数据已经被我过滤掉了。如果SQL文不用大改的话,不知道还有没有什么其他办法?
1.首先是一个LIST(该LIST里面存放的是由GRID_CD_FS跟GRID_CD_SS组合而成的每一条记录)
2.该LIST里面的每一条记录就是该SQL当中数据量很大的地方。
注:现在该LIST里面重复的数据已经被我过滤掉了。如果SQL文不用大改的话,不知道还有没有什么其他办法?
CREATE INDEX I_grid_x1 ON VWV_TBT_ENRT_GRID(GRID_CD_FS,GRID_CD_SS,REC_DEL_FLG,ENRT_REP_OID);
CREATE INDEX I_REP_x1 ON VWV_TBT_ENRT_REP(OBS_DATE||OBS_TIME);