Hi TOM,
I have this simplified version of a query. I would just like to check if there are other ways to further optimize the query especially on the subqueries. I'm limited to SQL only.
Reason for me thinking that this can be further optimized is that there are 2 similar subqueries, which I think doubles the resource and time it takes to run.
select acc.accid from acc
where ( select count(distinct(trunc(datetime)))
from accact
join accupd on accupd.accupdid = accact.accactid
join disp on disp.dispid = accupd.dispid
where accact.accactid = acc.accid
and accupd.dictionary = 12345
and disp.flag = 'Y'
and trunc(accact.datetime) between (trunc(sysdate) - 3) and trunc(sysdate) - 1) >= 3
and ( select count(distinct(trunc(datetime)))
from accact
join accupd on accupd.accupdid = accact.accactid
join disp on disp.dispid = accupd.dispid
where accact.accactid = acc.accid
and accupd.dictionary = 12345
and disp.flag = 'N'
and trunc(accact.datetime) between (trunc(sysdate) - 3) and trunc(sysdate) - 1) = 0
;Thanks
Having to subqueries with the same joins won't necessarily double the runtime. It depends on what data they process, indexes available, etc.
But it's unlikely to perform better than if you can access the tables just once!
It looks to me like you can do this by:
- Joining all the tables together
- Grouping by the acc.accid and disp.flag
- Adding a having clause to filter whether the counts return the correct values based on the flag value
For example:
select acc.accid, disp.flag, count(distinct(trunc(datetime)))
from acc
join accact
on accact.accactid = acc.accid
join accupd on accupd.accupdid = accact.accactid
join disp on disp.dispid = accupd.dispid
where accupd.dictionary = 12345
and trunc(accact.datetime) between (trunc(sysdate) - 3) and trunc(sysdate) - 1
and disp.flag in ('Y', 'N')
group by acc.accid, disp.flag
having case when disp.flag = 'Y' then count(distinct(trunc(datetime))) end >= 3
and case when disp.flag = 'N' then count(distinct(trunc(datetime))) end = 0