Re: SQL Question

"Sergey Mesyatz" <mes_at_astral.carrier.kiev.ua> wrote:

>>In article <jalford.389.00082ACC_at_immcms1.redstone.army.mil>,
>>jalford_at_immcms1.redstone.army.mil says...
>>
>>I'm trying to count the differences of one column in
>>two identical tables (except for the data) ...
>>I've tried
>>
>> select count(barcode_number) from property_temp
>> where
>> barcode_number not in (select barcode_number from property)
>>
>>but it takes about 15 minutes for this to run ....
>>
>>any suggestions ??
>>
>>
>>REPLY:
>>Have you tried an index on barcode_number? If not, the DBMS is forced
>>to perform an table scan, row by row. If your tables are large, this
>>WILL be time consuming. Your subquery is also a somewhat slow operation
>>for any DBMS!
 

>You should reorganize query to avoid use NOT IN
 

> select count(a.barcode_number) from property_temp a
> where not exist
> (select barcode_number from property where barcode_number = a.barcode_number)

You need the index if you don't have it, but I usually find minus to be better than either not in or not exists for this kind of operation.

Select barcode_number from property_temp minus
select barcode_number from property;

If you need just the count either do a select count(*) from property_temp where barcode_number in (...); or use a view.

Craig Received on Mon Oct 02 1995 - 00:00:00 CET