Re: subquery taking a VERY LONG TIME

Minus might be faster

 INSERT INTO new_app_results
(

    SELECT app_id FROM app_today
    MINUS
   SELECT app_id FROM app_yesterday
);

In your query, DISTINCT (do you need it? you mentioned that app_id is unique), NOT, IN all are usual tuning bottleneck points.

Usually you can use EXISTS instead of IN to make it faster.

    SELECT T.app_id
    FROM app_today T
    WHERE NOT EXISTS (SELECT 1 from app_yesterday WHERE app_id = T.app_id);

Good luck

• vsb http://vsbabu.org/

In article <36ee465c.0206121336.669f609a_at_posting.google.com>, "Sanjiv Singh" <ssingh1961_at_yahoo.com> wrote:

> Please help!
>
> I have a subquery that is taking too long (like 2 hours) to run with
> roughly 30,000 rows in each of the two query tables.
>
>
> problem context:
> ----------------
>
> There exists two tables APP_ID_TODAY and APP_ID_YESTERDAY; each table
> contains a unique key <app_id> and have the same type of data; except
> one table is from 'today' and the other table is from 'yesterday';
>
> The problem is to determine which <app_id> (denoting new applications)
> are in TODAY's table and not in YESTERDAY's table. For a variety of
> business (read political) reasons ... we don't have access to the OLTP
> database to write a trigger to do inserts when new applications
> resulting in a new app_id is generated.
>
> query:
> ------
> INSERT INTO new_app_results
> SELECT T.app_id
> FROM app_today T
> WHERE T.app_id NOT IN (SELECT DISTINCT app_id from app_yesterday);
>
> app_id is indexed on both TODAY and YESTERDAY table.
>
> thanks for your help!
>
> ssingh
Received on Wed Jun 12 2002 - 18:14:35 CDT