Improving the performance of a long query

Question

I have a query that i have let run for a few hours. I am forced to kill the query because it is taking up 60% of the CPU. Is there anything i can do to improve its performance?

select distinct(random_selection.randnum), 
    random_selection.dropper_id, 
    random_selection.ozip3 
from random_selection 
where random_selection.dropper_id is not null 
and random_selection.quarter = 121
and (random_selection.dropper_id, random_selection.randnum, random_selection.quarter) in 
    (select forecast_entry.dropper, forecast_entry.rand_num, production_weeks.yyq 
    from forecast_entry, production_weeks 
    where forecast_entry.week = production_weeks.production_week 
    and production_weeks.project_cd = 'EXFC' 
    and production_weeks.yyq >= 121)

union 

select distinct(random_selection.randnum), 
    dropper_city_brk_2.dropper_id, 
    random_selection.ozip3 
from random_selection, dropper_city_brk_2, dropper 
where random_selection.ozip3 = dropper_city_brk_2.zip3 
and dropper.dropper_id = dropper_city_brk_2.dropper_id 
and dropper.active = 1 
and dropper_city_brk_2.dropper_id <> 10002 
and random_selection.quarter = 121
and random_selection.dropper_id is null 
and (random_selection.dropper_id, random_selection.randnum, random_selection.quarter) in 
    (select forecast_entry.dropper, forecast_entry.rand_num, production_weeks.yyq 
    from forecast_entry, production_weeks 
    where forecast_entry.week = production_weeks.production_week 
    and production_weeks.project_cd = 'EXFC' 
    and production_weeks.yyq >= 121)

Query explained:

the main objective is to get all of the randnum, dropper_id, and ozip3 from random_selection table that are not in the forecast_entry table and are in yyq 121 and have a project code of EXFC. yyq is retrieved from the production_weeks table by associating week and production_week. Some dropper_id are null so we need to pull that data from the dropper_city_brk_2 table by associating ozip3 and zip3. We dont want dropper_id that are inactive so they must have active equal 1, this is by associating the dropper table.

Answer 1

The performance bottleneck very likely arises from the use of the IN keyword. You should rewrite it with an EXISTS. See the accepted answer of my question on Stackoverflow for how to do it. I'm pasting the relevant part here (credit goes to Quassnoi and you have to adapt it to your SQL dialect if needed):

---

One of the most common things to do is replacing this query:

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

with this one:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )

Answer 2

Since the sub-selects are the same, you could consolidate that portion via WITH:

WITH exss AS
    ( SELECT forecast_entry.dropper,
            forecast_entry.rand_num,
            production_weeks.yyq
       FROM forecast_entry
       JOIN production_weeks
         ON ( forecast_entry.week = production_weeks.production_week )
      WHERE production_weeks.project_cd = 'EXFC'
            AND production_weeks.yyq >= 121
    )
SELECT  random_selection.randnum,
        random_selection.dropper_id,
        random_selection.ozip3
    FROM random_selection
    JOIN exss
        ON ( random_selection.dropper_id = exss.dropper
            AND random_selection.randnum = exss.rand_num
            AND random_selection.quarter = exss.yyq )
    WHERE random_selection.dropper_id IS NOT NULL
        AND random_selection.quarter = 121
UNION
SELECT  random_selection.randnum,
        dropper_city_brk_2.dropper_id,
        random_selection.ozip3
    FROM random_selection
    JOIN dropper_city_brk_2
        ON ( random_selection.ozip3 = dropper_city_brk_2.zip3 )
    JOIN dropper
        ON ( dropper.dropper_id = dropper_city_brk_2.dropper_id )
    JOIN exss
        ON ( random_selection.dropper_id = exss.dropper
            AND random_selection.randnum = exss.rand_num
            AND random_selection.quarter = exss.yyq )
    WHERE random_selection.dropper_id IS NOT NULL
        AND random_selection.quarter = 121
        AND dropper.active = 1
        AND dropper_city_brk_2.dropper_id <> 10002

You could maybe even take it further and include the random_selection table in the WITH block:

WITH exss AS
    ( SELECT a.randnum,
            a.dropper_id,
            a.ozip3
       FROM random_selection a
       JOIN
            ( SELECT forecast_entry.dropper,
                    forecast_entry.rand_num,
                    production_weeks.yyq
               FROM forecast_entry
               JOIN production_weeks
                 ON ( forecast_entry.week = production_weeks.production_week )
              WHERE production_weeks.project_cd = 'EXFC'
                    AND production_weeks.yyq >= 121 ) b
         ON ( a.dropper_id = b.dropper
                AND a.randnum = b.rand_num
                AND a.quarter = b.yyq )
      WHERE a.dropper_id IS NOT NULL
            AND a.quarter = 121
    )
SELECT exss.randnum,
    exss.dropper_id,
    exss.ozip3
FROM exss
UNION
SELECT exss.randnum,
    dropper_city_brk_2.dropper_id,
    exss.ozip3
FROM exss
JOIN dropper_city_brk_2
 ON ( exss.ozip3 = dropper_city_brk_2.zip3 )
JOIN dropper
 ON ( dropper.dropper_id = dropper_city_brk_2.dropper_id )
WHERE dropper.active = 1
    AND dropper_city_brk_2.dropper_id <> 10002

Avoid using DISTINCT in the query if at all possible.

Since I don't use the WITH construct on a frequent basis the syntax may be a bit off but it should at least get you close.

edit How large are these tables? Have you looked at the EXPLAIN PLAN for the query? Indices?