Re: Is there any good optimization solution to improve the query efficiency?

try this (there is some comment)

with t_res as (
select RSNO, KNO
from TBL_RES
where TBL_RES.CID >= to_date('2022/07/01', 'YYYY/MM/DD')
and TBL_RES.CID <= to_date('2022/07/31', 'YYYY/MM/DD')
and TBL_RES.COD >= to_date('2022/07/01', 'YYYY/MM/DD')
and TBL_RES.COD <= to_date('2022/07/31', 'YYYY/MM/DD')
), t_pov2 as (
select T_CUST.RSNO ,
T_CUST.KNO ,
MIN(T_CUST.GSTSEQ) GSTSEQ
from T_RES -- this is tbl_res already filter by date
inner join TBL_CUST T_CUST on T_RES.RSNO = T_CUST.RSNO
inner join TBL_POV T_POV on T_POV.CRSNO = T_RES.CRSNO -- why you use this
table? it doesn't seem to be used to extract data. Are you trying to
extract data from T_RES that have at least a record in T_POV? in this case
could work better move this join in the first with (using distinct or group
by to ensure there will be just a record for rsno and kno)
where T_CUST.STSFLG = 'T'
and T_CUST.DISPSEQ <> 9999
AND T_CUST.KFIX = '0'
group by T_CUST.RSNO , T_CUST.KNO
), t_pov3 as (
select T_CUST.RSNO RSNO2 ,
T_CUST.KNO ,
T_CUST.AGE ,
T_CUST.GST
from TBL_CUST T_CUST
inner join t_pov2 on T_POV2.RSNO = T_CUST.RSNO
and T_POV2.KNO = T_CUST.KNO
and T_POV2.GSTSEQ = T_CUST.GSTSEQ
)
select *
from TBL_RES
left outer join t_pov3 on TBL_RES.RSNO = T_POV3.RSNO2
and TBL_RES.KNO = T_POV3.KNO

Il giorno lun 5 giu 2023 alle ore 12:06 gzh <gzhcoder(at)126(dot)com> ha scritto:

> Thank you very much for taking the time to reply to my question.
>
> I followed your suggestion and rewrote the SQL using Common Table
> Expression (CTE).
>
> Unfortunately, there was no significant improvement in performance.
>
>
>
> At 2023-06-05 17:47:25, "Lorusso Domenico" <domenico(dot)l76(at)gmail(dot)com> wrote:
>
> Hello,
> In many case a formal writing and usage of with statement could solve the
> issue.
> If you need join, use always join:
> where T_POV2.RSNO = T_CUST.RSNO
> and T_POV2.KNO = T_CUST.KNO
> and T_POV2.GSTSEQ = T_CUST.GSTSEQ)
> this is an inner join.
>
> I mean something like this
> with t_pov2 as (
> select T_CUST.RSNO ,
> T_CUST.KNO ,
> MIN(T_CUST.GSTSEQ) GSTSEQ
> from TBL_CUST T_CUST ,
> TBL_POV T_POV ,
> TBL_RES T_RES
> where T_CUST.STSFLG = 'T'
> and T_CUST.DISPSEQ <> 9999
> AND T_CUST.KFIX = '0'
> and T_POV.CRSNO = T_RES.CRSNO
> and T_RES.RSNO = T_CUST.RSNO
> group by T_CUST.RSNO , T_CUST.KNO
> ), t_pov3 as (
> select T_CUST.RSNO RSNO2 ,
> T_CUST.KNO ,
> T_CUST.AGE ,
> T_CUST.GST
> from TBL_CUST T_CUST
> inner join t_pov2 on T_POV2.RSNO = T_CUST.RSNO
> and T_POV2.KNO = T_CUST.KNO
> and T_POV2.GSTSEQ = T_CUST.GSTSEQ
> )
>
> select *
> from TBL_RES
> left outer join t_pov3 on TBL_RES.RSNO = T_POV3.RSNO2
> and TBL_RES.KNO = T_POV3.KNO
> where TBL_RES.CID >= to_date('2022/07/01', 'YYYY/MM/DD')
> and TBL_RES.CID <= to_date('2022/07/31', 'YYYY/MM/DD')
> and TBL_RES.COD >= to_date('2022/07/01', 'YYYY/MM/DD')
> and TBL_RES.COD <= to_date('2022/07/31', 'YYYY/MM/DD')
>
> but if tbl_res contain lessere record a good idea is start from this table
> and use in join with other
>
>
> Il giorno lun 5 giu 2023 alle ore 08:57 gzh <gzhcoder(at)126(dot)com> ha scritto:
>
>> Hi everyone,
>> I'm running into some performance issues with my SQL query.
>> The following SQL query is taking a long time to execute.
>>
>> Execution Plan：
>> explain analyse
>> select * from TBL_RES
>> left outer join(select T_CUST.RSNO RSNO2 ,
>> T_CUST.KNO ,
>> T_CUST.AGE ,
>> T_CUST.GST
>> from TBL_CUST T_CUST ,
>> (select T_CUST.RSNO ,
>> T_CUST.KNO ,
>> MIN(T_CUST.GSTSEQ) GSTSEQ
>> from TBL_CUST T_CUST ,
>> TBL_POV T_POV ,
>> TBL_RES T_RES
>> where T_CUST.STSFLG = 'T'
>> and T_CUST.DISPSEQ <> 9999
>> AND T_CUST.KFIX = '0'
>> and T_POV.CRSNO = T_RES.CRSNO
>> and T_RES.RSNO = T_CUST.RSNO
>> group by T_CUST.RSNO , T_CUST.KNO) T_POV2
>> where T_POV2.RSNO = T_CUST.RSNO
>> and T_POV2.KNO = T_CUST.KNO
>> and T_POV2.GSTSEQ = T_CUST.GSTSEQ) T_POV3 on TBL_RES.RSNO = T_POV3.RSNO2
>> and TBL_RES.KNO = T_POV3.KNO
>> where TBL_RES.CID >= to_date('2022/07/01', 'YYYY/MM/DD')
>> and TBL_RES.CID <= to_date('2022/07/31', 'YYYY/MM/DD')
>> and TBL_RES.COD >= to_date('2022/07/01', 'YYYY/MM/DD')
>> and TBL_RES.COD <= to_date('2022/07/31', 'YYYY/MM/DD')
>> ----- Execution Plan -----
>> Nested Loop Left Join (cost=254388.44..452544.70 rows=473 width=3545)
>> (actual time=3077.312..996048.714 rows=15123 loops=1)
>> Join Filter: ((TBL_RES.RSNO = T_CUST.RSNO) AND ((TBL_RES.KNO)::text =
>> (T_CUST.KNO)::text))
>> Rows Removed by Join Filter: 4992268642
>> -> Gather (cost=1000.00..58424.35 rows=473 width=3489) (actual
>> time=0.684..14.158 rows=15123 loops=1)
>> Workers Planned: 2
>> Workers Launched: 2
>> -> Parallel Seq Scan on TBL_RES (cost=0.00..57377.05 rows=197
>> width=3489) (actual time=0.096..279.504 rows=5041 loops=3)
>> Filter: ((CID >= to_date('2022/07/01'::text,
>> 'YYYY/MM/DD'::text)) AND (CID <= to_date('2022/07/31'::text,
>> 'YYYY/MM/DD'::text)) AND (COD >= to_date('2022/07/01'::text,
>> 'YYYY/MM/DD'::text)) AND (COD <= to_date('2022/07/31'::text,
>> 'YYYY/MM/DD'::text)))
>> Rows Removed by Filter: 161714
>> -> Materialize (cost=253388.44..394112.08 rows=1 width=56) (actual
>> time=0.081..26.426 rows=330111 loops=15123)
>> -> Hash Join (cost=253388.44..394112.07 rows=1 width=56)
>> (actual time=1197.484..2954.084 rows=330111 loops=1)
>> Hash Cond: ((T_CUST.RSNO = T_CUST_1.RSNO) AND
>> ((T_CUST.KNO)::text = (T_CUST_1.KNO)::text) AND (T_CUST.gstseq =
>> (min(T_CUST_1.gstseq))))
>> -> Seq Scan on TBL_CUST T_CUST (cost=0.00..79431.15
>> rows=2000315 width=61) (actual time=0.015..561.005 rows=2000752 loops=1)
>> -> Hash (cost=246230.90..246230.90 rows=262488 width=50)
>> (actual time=1197.025..1209.957 rows=330111 loops=1)
>> Buckets: 65536 Batches: 8 Memory Usage: 2773kB
>> -> Finalize GroupAggregate
>> (cost=205244.84..243606.02 rows=262488 width=50) (actual
>> time=788.552..1116.074 rows=330111 loops=1)
>> Group Key: T_CUST_1.RSNO, T_CUST_1.KNO
>> -> Gather Merge (cost=205244.84..238964.80
>> rows=268846 width=50) (actual time=788.547..982.479 rows=330111 loops=1)
>> Workers Planned: 2
>> Workers Launched: 1
>> -> Partial GroupAggregate
>> (cost=204244.81..206933.27 rows=134423 width=50) (actual
>> time=784.032..900.979 rows=165056 loops=2)
>> Group Key: T_CUST_1.RSNO,
>> T_CUST_1.KNO
>> -> Sort
>> (cost=204244.81..204580.87 rows=134423 width=23) (actual
>> time=784.019..833.791 rows=165061 loops=2)
>> Sort Key: T_CUST_1.RSNO,
>> T_CUST_1.KNO
>> Sort Method: external merge
>> Disk: 5480kB
>> Worker 0: Sort Method:
>> external merge Disk: 5520kB
>> -> Parallel Hash Join
>> (cost=111758.80..190036.38 rows=134423 width=23) (actual
>> time=645.302..716.247 rows=165061 loops=2)
>> Hash Cond:
>> (T_CUST_1.RSNO = T_RES.RSNO)
>> -> Parallel Seq Scan
>> on TBL_CUST T_CUST_1 (cost=0.00..74013.63 rows=204760 width=23) (actual
>> time=0.018..264.390 rows=165058 loops=2)
>> Filter: ((dispseq
>> <> '9999'::numeric) AND ((stsflg)::text = 'T'::text) AND ((KFIX)::text =
>> '0'::text))
>> Rows Removed by
>> Filter: 835318
>> -> Parallel Hash
>> (cost=109508.52..109508.52 rows=137142 width=8) (actual
>> time=343.593..343.896 rows=165058 loops=2)
>> Buckets: 131072
>> Batches: 8 Memory Usage: 3008kB
>> -> Parallel Hash
>> Join (cost=51834.70..109508.52 rows=137142 width=8) (actual
>> time=256.732..314.368 rows=165058 loops=2)
>> Hash Cond:
>> ((T_RES.crsno)::text = (T_POV.crsno)::text)
>> ->
>> Parallel Seq Scan on TBL_RES T_RES (cost=0.00..53199.02 rows=208902
>> width=17) (actual time=0.007..100.510 rows=250132 loops=2)
>> ->
>> Parallel Hash (cost=49450.42..49450.42 rows=137142 width=9) (actual
>> time=122.308..122.309 rows=165054 loops=2)
>>
>> Buckets: 131072 Batches: 8 Memory Usage: 2976kB
>> ->
>> Parallel Seq Scan on TBL_POV T_POV (cost=0.00..49450.42 rows=137142
>> width=9) (actual time=0.037..89.470 rows=165054 loops=2)
>> Planning Time: 1.064 ms
>> Execution Time: 996062.382 ms
>>
>> --------------------------------------------------------------------------------
>>
>> The amount of data in the table is as follows.
>> TBL_RES 500265
>> TBL_CUST 2000752
>> TBL_POV 330109
>>
>> Any suggestions for improving the performance of the query would be
>> greatly appreciated.
>>
>> Thanks in advance!
>>
>>
>
> --
> Domenico L.
>
> per stupire mezz'ora basta un libro di storia,
> io cercai di imparare la Treccani a memoria... [F.d.A.]
>
>

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]