Re: Program seems slow

Bruce Bristol wrote:
>
> I have a COBOL program that simply reads a file and checks each record
> against our History Database to see if it's already been processed.
>
> The problem is that the file contains 5.5 million records and it's
> taking over 8 hours to process! Also, it's the only thing running on a
> SparcCenter 2000 machine.
>
> Are there any tuning parameters I can use to speed this up? I think it
> should be able to run much faster than it is.
>
> There are 8 tables and I choose the appropriate table to select from
> based on a number.
>
> Here's the SQL I'm using to check if a record has already been
> processed:
>
> EXEC SQL
> SELECT COUNT(*)
> INTO :H-ROW-COUNT
> FROM DUAL
> WHERE EXISTS ( SELECT NULL
> FROM HIS_TABLE_A
> WHERE NUMBER_1 = :H-NUMBER-1 AND
> NUMBER_2 = :H-NUMBER-2 AND
> NUMBER_3 = :H-NUMBER-3 AND
> NUMBER_4 = :H-NUMBER-4 AND
> NUMBER_5 = :H-NUMBER-5 AND
> NUMBER_6 = :H-NUMBER-6 AND
> NUMBER_7 = :H-NUMBER-7 )
> END-EXEC.
>
> Thank you!
>
> -Bruce

Ensure that you have a single concatenated index on (HIS_TABLE_A(NUMBER_1, NUMBER_2, NUMBER_3, ...,NUMBER_7) Separate single-column indexes will *not* be sufficient.

Ifyou have done this and the program is still too slow, realise that 5.5M index accesses (on a seven-column index!) is a very high load. In extremis, and if you expect the great majority of your input records to match you history file, consider doing a fast (direct) load of your file into a work table and then executing: select number_1, number_2, ... from WORK_TABLE MINUS
select number_1, number_2, ... from from HIS_TABLE_A

This will do serial scans of both tables and may well repay the time to load the work table.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"