| alternate use of NOT IN [message #370217] |
Mon, 07 December 1998 11:22  |
Dony
Messages: 3
Registered: August 1998
|
Junior Member |
|
|
Hi,
I've below mentioned query for inserting records from one table(new records) to another table, is there any faster way of accessing the select statement besides using the NOT IN operator and cursors, I heard that NOT IN works slower.
insert into exchange_rates
( vat_code
, begin_date
, rate
, end_date
)
as select tax_code_id
,tax_rate_effective_date
,tax_rate_percent
,tax_rate_expiry_date
from tax_rate
where tax_rate_status = '0'
and tax_code not in(select vat_code from exchange_rates);
Thanks
Kind regards
Dony
|
|
|
|
|
|
| Re: alternate use of NOT IN [message #370222 is a reply to message #370217] |
Wed, 16 December 1998 09:08  |
Sean Miller
Messages: 12
Registered: December 1998
|
Junior Member |
|
|
As long as the VAT_CODE column on the exchange_rates table is
indexed, the following will be much better...
insert into exchange_rates
( vat_code
, begin_date
, rate
, end_date
)
as select tax_code_id
,tax_rate_effective_date
,tax_rate_percent
,tax_rate_expiry_date
from tax_rate
where tax_rate_status = '0'
and NOT EXISTS
( select '1'
from exchange_rates
where vat_code = tax_code );
What this will do is for each row that it is thinking of inserting
it will fire a query on the exchange_rates table to check if it
already exists - as long as this goes in on an index, it should
be lightning-fast!
Your non-correlated sub-query means that for each row the
returned values of the sub-query would need to be scanned.
Once you end up with 150,000 rows in "exchange_rates" this
will
1) be very slow
2) possibly blow your temporary tablespace!!
Sean
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
