Subject Performance diff between and for select ... do?
Author Kjell Rilbe

I'm writing a utility that will need to do two things for each record in
an external table and for this purpose I use a for select ... do
construct in an execute block. I do it this way because external tables
can't be indexed and I will scan the entire external table anyway.

The two operations are:
1. Update one existing record in the target table.
2. Insert new record in the same target table.

In "steady state" the target table will contain about 20 million records
and the external table will contain about 10 thousand records.

But the first time I run this, the target table will be empty and the
external table will contain about 18 million records. The update will
never find a record to update during this first execution.

Would I lose a lot of hours if I use the same execute block/for select
construct the first time? The alternative would be to do a regular
insert into target table select from externaltable the first time. My
guess is that the unecessary update will take increasingly long to
execute as the target table grows towards 18 milliion records, wasting a
lot of time for me, even if a suitable index is present.

Just to give a measure of the system's performance as it is, a simple
update on one column for 18 million records in the target table
currently seems to take about 6 hours. But I could drop a couple of
indices and perhaps I should bump up the page size from 4k to 8k or 16k.


Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Ulvsundavägen 106
168 67 Bromma <>
08-514 905 90

Fö <> - urval av företag
och kontaktinformation <> - urval av hushållsadresser