Home » RDBMS Server » Server Administration » alternate use of NOT IN
alternate use of NOT IN [message #370217] Mon, 07 December 1998 11:22 Go to next message
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 #370218 is a reply to message #370217] Tue, 08 December 1998 11:24 Go to previous messageGo to next message
Sheon
Messages: 4
Registered: November 1998
Junior Member
Is making vat_code a unique key or creating a trigger on the exchange_rate table options you can try?
Re: alternate use of NOT IN [message #370222 is a reply to message #370217] Wed, 16 December 1998 09:08 Go to previous message
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
Previous Topic: Re: Updating of Record
Next Topic: Re: Default value
Goto Forum:
  


Current Time: Sun Oct 25 09:36:00 CDT 2020