CODEWITHSUNDEEP
sqloracle-databaseoracle11gquery-optimization
shaair
shaair

Reputation: 985

OR clause with subquery taking too much time

Date Range query taking too much time. Just i removed the one condition then it working fine taking 2 second. If adding then 30 seconds.

SELECT UserName,COUNT ('t') TOTAL
FROM TRANSACTIONS E1
WHERE E1.START_DATE BETWEEN TO_DATE ('20130101', 'YYYYMMDD') AND TO_DATE ('20140101', 'YYYYMMDD')
AND 
( 
    TO_CHAR (E1.START_DATE, 'D') in ( 7) 
    OR Exists(SELECT 1 FROM HOLIDAYS TT
    WHERE E1.START_DATE BETWEEN TT.DATE_FROM AND TT.DATE_TO )
)
AND EXISTS (SELECT 't' FROM TRANSACTIONS_ORG E2 WHERE E1.TRANTYPE = E2.tran_type)
GROUP BY UserName;

HOLIDAYS table

Id  FromDate    ToDate  Description
1   1-Feb-11    3-Feb-11    Maintance
3   2-Sep-09    5-Sep-09    Eid Holiday Fine Block
4   3-Dec-09    4-Dec-09    Due to System Problem
5   4-Dec-07    04-Dec-07   National Day

EIDTED I figured out that the issue is not in the date range. but the OR clause in the

TO_CHAR (E1.START_DATE, 'D') in ( 5,6) 
OR 
Exists(SELECT 1 FROM HOLIDAYS TT
WHERE E1.START_DATE BETWEEN TT.DATE_FROM AND TT.DATE_TO )

if removed OR and put AND then fine and if shuffle conditions with OR still same issue.

Upvotes: 0

Views: 136

Answers (1)

Ronnis
Ronnis

Reputation: 12843

The problem is likely with the OR <subquery> construct. If there can only be one holiday for a particular date, then you could use the following:

select username
      ,count(*)
  from transactions  e1
  left join holidays tt on(e1.start_date between tt.date_from and tt.date_to)
 where e1.start_date between date '2017-02-01' and date '2018-02-01'
   and (   to_char(e1.start_date, 'D') in(5, 6) 
        or tt.date_from is not null)
       )
   and exists(
      select * 
        from transactions_org e2 
       where e1.trantype = e2.tran_type
   )
 group 
    by username;

This entire category of problems can be solved by implementing a Calendar table. If you had such a table with one record per date, you could easily add columns indicating day of week and holiday flags and such. If your calendar table looked something like this: 

DAY        DAYNAME   IS_WEEKEND IS_WEEKDAY HOLINAME
---------- --------- ---------- ---------- ------------
2017-02-01 WEDNESDAY          0          1
2017-02-02 THURSDAY           0          1
2017-02-03 FRIDAY             0          1 Some holiday
2017-02-04 SATURDAY           1          0
2017-02-05 SUNDAY             1          0
2017-02-06 MONDAY             0          1
2017-02-07 TUESDAY            0          1
2017-02-08 WEDNESDAY          0          1

Your query could be rewritten as:

  from transactions  e1
  join calendar      c on(c.day = trunc(e1.start_date, 'DD')) -- Remove hours, minutes
 where e1.start_date between date '2017-02-01' and date '2018-02-01'
   and (   c.weekday in('THURSDAY', 'FRIDAY') -- Either specific weekdays
        or c.holiname is not null             -- or there is a holiday 
       )
   and exists(
      select * 
        from transactions_org e2 
       where e1.trantype = e2.tran_type
   )

Upvotes: 3

Related Questions