CODEWITHSUNDEEP
sqloracle-databasesubquery
user1466935
user1466935

Reputation: 7

Oracle sql with subqueries causing peformance issue

Writing oracle sql with 3 subqueries, however it is causing a timeout issue. Hoping for technical help on getting to the fix. Here is my query:

select   DISTINCT E_reg.applicant_id,
             E_REG.L_NAME, 
             E_REG.F_NAME, 
             E_REG.B_DATE,
            E_REG.REG_DATE,
 from     E_REG,TRANSACTION
where   E_REG.ID=TRANSACTION.PAR_ID
  and       TRANSACTION.BEGIN_DATE BETWEEN to_date ('01-APR-2012')AND to_date('30-JUN-               2012')
and  e_reg.applicant_id NOT IN
              (select applicant_id
                   from w_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date >='01-APR-2012'
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from t_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date>='01-APR-2012')
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from r_reg
                  where reg_date <'01-JUL-2012' 
                     and o_attend IS NOT NULL
                   and term_date is NULL or term_date >='01-APR-2012')

Basically we have 4 programs that you can be in ( e, w, t, and r). Those are all seperate tables that house basic participant information. You may be in all 4 programs at the same time, the applicant_id will be the key for everyone.

The transaction table house any service you may receive in that 3 or the 4 programs, not the r program, that has its own transaction table.

I want a listing of participants in e table that had a service in the timeframe but did not have any services in any of the other programs at the same time. They would be only serviced though the e program. It seemed to work this morning but then it starting timing out and not running at all. I figure it must be the subquery. Wondering if there is a better way to make this work.

Upvotes: 0

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I'm pretty sure the problem is parentheses in your where clause. You have or clauses that are undoing the join. If you always use ANSI join syntax, you greatly reduce the possibility of this occurring.

Here is the alternative version:

select   DISTINCT E_reg.applicant_id,
             E_REG.L_NAME, 
             E_REG.F_NAME, 
             E_REG.B_DATE,
            E_REG.REG_DATE,
 from    E_REG join
         TRANSACTION
         on E_REG.ID=TRANSACTION.PAR_ID
  where TRANSACTION.BEGIN_DATE BETWEEN to_date ('01-APR-2012')AND to_date('30-JUN-               2012')
and  (e_reg.applicant_id NOT IN
              (select applicant_id
                   from w_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date >='01-APR-2012'
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from t_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date>='01-APR-2012')
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from r_reg
                  where reg_date <'01-JUL-2012' 
                     and o_attend IS NOT NULL
                   and term_date is NULL or term_date >='01-APR-2012')
   )

Upvotes: 1

Related Questions