Optimizing SQL Query speed-CodePudding

I am trying to optimize my SQL query below as I am using a very old RDMS called firebird. I tried rearranging the items in my where clause and removing the order by statement but the query still seems to take forever to run. Unfortunately firebird doesn't support Explain Execution Plan Functionalities and therefore I cannot identify the code that is holding up the query.

select T.veh_reg_no,T.CON_NO, sum(T.pos_gpsunlock) as SUM_GPS_UNLOCK,
count(T.pos_gpsunlock) as SUM_REPORTS, contract.con_name
from
(
    select veh_reg_no,CON_NO,
         case when pos_gpsunlock = upper('T') then 1 else 0 end as pos_gpsunlock 
    from vehpos
    where veh_reg_no in
        ( select regno 
          from fleetvehicle 
          where fleetno in (97)
        )        --DS5
        and pos_timestamp > '2022-07-01'
        and pos_timestamp < '2022-08-01'
) T
join contract  on T.con_no = contract.con_no
group by T.veh_reg_no, T.con_no,contract.con_name
order by SUM_GPS_UNLOCK desc;

If anyone can help it would be greatly appreciated.

CodePudding user response：

I'd either comment out some of the sub-queries or remove a join or aggregation and see if that improves it. Once you find the offending code maybe you can move it or re-write it. I know nothing of Firebird but I'd approach that query with the below code, wrapping the aggregation outside of the joins and removing the "Where in" clause.

If nothing works can you create an aggregation table or pre-filtered table and use that?

select
x.*
,sum(case when x.pos_gpsunlock = upper('T') then 1 else 0 end) as SUM_GPS_UNLOCK
,count(*) as SUM_REPORTS

FROM (
    select 
        a.veh_reg_no
        ,a.pos_gpsunlock
        ,a.CON_NO
        ,c.con_name
    FROM vehpos a
    JOIN fleetvehicle b on a.veg_reg_no = b.reg_no and b.fleetno = 97 and b.pos_timestamp between '222-07-01' and '2022-08-01'
    JOIN contract c on a.con_no = contract.con_no 
    ) x
Group By....

CodePudding user response：

This might help by converting subqueries to joins and reducing nesting. Also an = instead of IN() operation.

select vp.veh_reg_no,vp.con_no,c.con_name,
     count(*) as SUM_REPORTS,
     sum(case when pos_gpsunlock = upper('T') then 1 else 0 end) as SUM_GPS_UNLOCK
from vehpos vp
inner join fleetvehicle fv on fv.fleetno = 97 and fv.regno = vp.veh_reg_no
inner join contract c on  vp.con_no = c.con_no
where vp.pos_timestamp >= '2022-07-01'
    and vp.pos_timestamp < '2022-08-01'
group by vp.veh_reg_no, vp.con_no, c.con_name