Improving the performance of a long query
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
        )