CODEWITHSUNDEEP
sqloracle-database
technocrat
technocrat

Reputation: 725

SQL subquery alternative

I have a simple select sub query which is taking lot of time to execute.

SELECT EMPID, UPDATETIME 
FROM TIME_DETAILS T1
WHERE T1.EVENTNAME = 'ENROLL' 
  AND T1.STATUS = 'BEGIN'
  AND (SELECT COUNT(1) FROM TIME_DETAILS T2 
       WHERE T1.EMPID = T2.EMPID 
         AND T2.EVENTNAME = 'ENROLL'
         AND T2.STATUS = 'END') = 0;

Kindly suggest possible modifications which can improve the performance or any way to skip the sub query

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

First change the query to NOT EXISTS:

SELECT EMPID, UPDATETIME
FROM TIME_DETAILS T1
WHERE T1.EVENTNAME = 'ENROLL' AND
      T1.STATUS = 'BEGIN' AND 
      NOT EXISTS (SELECT 1
                  FROM TIME_DETAILS T2
                  WHERE T2.EMPID = T1.EMPID AND
                        T2.EVENTNAME = 'ENROLL' AND
                        T2.STATUS = 'END'
                 );

Then you want indexes on TIME_DETAILS(EVENTNAME, STATUS) AND TIME_DETAILS(EMPID, EVENTNAME, STATUS).

You might also find that it is faster to simply use window functions:

SELECT EMPID, UPDATETIME
FROM (SELECT td.*,
             SUM(CASE WHEN STATUS = 'END' THEN 1 ELSE 0 END) OVER (PARTITION BY EMPID, EVENTNAME) as cnt_end 
      FROM TIME_DETAILS td
     ) td
WHERE STATUS = 'BEGIN' AND cnt_end = 0;

For this, you only want an index on (EMPID, EVENTNAME, STATUS).

Or aggregation:

select empid, max(case when status = 'BEGIN' then updatetime end)
from time_details td
where eventname = 'ENROLL' and
      status in ('BEGIN', 'END')
group by empid
having max(status) = 'BEGIN'

Upvotes: 4

Related Questions