Question: I have been trying to optimize performance to the following query. I request all experts in this field to give me a hand and suggestions.

I have app. 70k records and my requirement says to remove duplicates. I need to improve the performance of the below query.

```
select *
  from x.vw_records
 where id not in
       (select distinct id
          from x.vw_datarecords
         where effective_date >= trunc(sysdate - 30)
           and book in (select book_shortname from x.vw_datarecords))
union
select distinct id
  from x.vw_historyrecords
 where effective_date >= trunc(sysdate - 30)
   and book in (select book_shortname from x.vw_datarecords)
union
select distinct id
  from x.vw_transactiondata
 where effective_date >= trunc(sysdate - 30)
   and book in (select book_shortname from x.vw_datarecords);
union
  select distinct id
    from x.vw_cashdata
   where effective_date >= trunc(sysdate - 30)
     and book in (select book_shortname from x.vw_datarecords)

```

Currently It takes ten minutes to count no. of rows using count(\*). Suggest me any ideas to tune performance of this query.

Thanks in Advance.

Answer: 
I've always found better performance swapping out a NOT IN (query) with a left join + where iS NULL

example instead of:

```
select *
from x.vw_records
where id not in (
    select distinct id
    from x.vw_datarecords
    where effective_date >= trunc(sysdate - 30)
        and book in (
            select book_shortname from x.vw_datarecords
        )

```

use:

```
select *
from x.vw_records vr
left join vw_datarecords vdr on vr.id = vdr.id
    and effective_date >= trunc(sysdate - 30)
        and book in (
            select book_shortname from x.vw_datarecords
        )
where vdr.id IS NULL

```

additionally, you can sometimes get noticeably better performance by doing a group by rather than distinct.
If you could try to use exists/not exists clause in place of in/not in (<http://www.techonthenet.com/sql/exists.php>). That generally runs much faster.