Improving the performance of a long query 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 &gt;= 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 &lt;&gt; 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 &gt;= 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.
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
        )