Good day to all, please help optimize the query:

select N,Nz ,Round(hz, 2) as Thick ,Round(pf, 3) as Weight ,tz,ts ,Round((select avg(NV1L) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG1L ,Round((select avg(NV1R) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG1R ,Round((select avg(NV2L) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG2L ,Round((select avg(NV2R) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG2R ,Round((select avg(NV3L) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG3L ,Round((select avg(NV3R) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG3R ,Round((select avg(NV4L) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG4L ,Round((select avg(NV4R) from tab2 where DT between tab1.tz and tab1.ts), 3) as AVG4R from tab1 where ts between :DateFrom and :DateTo order by ts

This request is about 20 seconds, there are about 700 data. Thanks for the help.

    1 answer 1

    Remove the subqueries, cross-join both tables, group by fields the records of the first table and group by the records of the second. Type

     select t1.N , t1.Nz , Round(t1.hz, 2) as Thick , Round(t1.pf, 3) as Weight , t1.tz , t1.ts , avg(t2.NV1L) as AVG1L , avg(t2.NV1R) as AVG1R , avg(t2.NV2L) as AVG2L , avg(t2.NV2R) as AVG2R , avg(t2.NV3L) as AVG3L , avg(t2.NV3R) as AVG3R , avg(t2.NV4L) as AVG4L , avg(t2.NV4R) as AVG4R FROM tab1 t1 , tab2 t2 WHERE t2.DT between t1.tz and t1.ts AND t1.ts between :DateFrom and :DateTo GROUP BY t1.ts , t1.N , t1.Nz , Round(t1.hz, 2) as Thick , Round(t1.pf, 3) as Weight , t1.tz order by t1.ts
    • one
      I would first put t1.ts in group by, then the optimizer most likely will not need a separate sorting phase and the result of group by - Mike will be used
    • @Akina Thank you very much. The request takes 2 seconds to complete - Ethernets
    • one
      @Mike accepted, corrected. Although it will be strange if the optimizer cannot do it himself. On the other hand, the GROUP BY clause should be formed in such a way as to make the most efficient use of existing indexes for selecting and grouping, and sorting a compact enough result set is the tenth thing ... - Akina