Hello,
I've got to run some queries (with subqueries) on quite large tables (each
about 10 millions of rows).
The queries are in that pattern:
select col1,col2 from table 1
where id not in
(select ....)
and not in
(select ....)
and not in
(select ....)

There are proper indexes on tables, and statistics are correctly updated.
I'm looking for information how can I improve query performance.

Will creating temp tables for all subqueries be a good idea?
Or is there another way?

thanks
Marcin

marcin wrote:
> Hello,
> I've got to run some queries (with subqueries) on quite large tables (each
> about 10 millions of rows).
> The queries are in that pattern:
> select col1,col2 from table 1
> where id not in
> (select ....)
> and not in
> (select ....)
> and not in
> (select ....)
>
> There are proper indexes on tables, and statistics are correctly updated.
> I'm looking for information how can I improve query performance.
>
> Will creating temp tables for all subqueries be a good idea?
> Or is there another way?
>
> thanks
> Marcin
>
>

I would advise you not to use "in". But it really depends on what is the
" (select ....)"

Let's assume you just want to look for the id field in the tables used
in the subqueries. If that is the case an alternative might be create
just one temp table with that column and an index on it. So your query
would use only one sub-query.

Besides this, if you can avoid the "in" and use "EXISTS" it's usualy
better (make a co-related subquery, with index on what you're looking
for on the "interior" table.

Regards.