Leyne, Sean skriver:

>
> Kjell,
>
> > 2 X time for natural scan would be fair enough. It took 1½ hour to
> execute an
> > update using execute block with for select .... do update....
> > where the select picked PK:s from a temporary table I filled with a
> select like
> > the above.
>
> <SL> This is the basic approach I was going to suggest.
>
> <SL> Where I think there might be some optimization possible is to use
> a 'simple' UPDATE statement with WHERE EXISTS references to the
> temporary table keys (forcing the UPDATE WHERE to use a natural scan
> of your "large table")
>
> <SL> I think that SELECT ... DO UPDATE approach is generating some
> overhead, which the simple UPDATE would not have since it would be
> doing a in place update of the row at the cursor position.
>
> <SL> Come to think of it, you could also try modify your current
> solution to use a CURSOR approach, since the UPDATE would also be
> in-place.
>

It seems that this solution works well (where I even delete from a
related table in one go):

set term #;
execute block
as
declare UH bigint;
begin
for select UH."ECO_ID"
from "Uppgiftshållare" UH
inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = UH."Hanterare"
where UH."Propertynamn" in ('Efternamn', 'Förnamn', 'Mobilnummer')
and Uhant."NamnPåUppgiftsbärarrelKYF" = 'Person'
into :UH
do
begin
delete from "Uppgift" where "Hållare" = :UH;
delete from "Uppgiftshållare" where "ECO_ID" = :UH;
end
end#
set term ;#

The real name of LargeTable is "Uppgift" in case you're wondering. It
seems to be more efficient than inserting into a temp table and doing a
delete from "Uppgift" where "ECO_ID" in (...).

The overhead of doing PK lookups in the large table is apparently less
than the overhead of natural scanning 150+ million more records than I
actually want to touch. An added bonus is that I can apply the delete on
both tables in the case above.

Execution time for these queries has been about 1 hour( give or take ½
hour). Nice!

Thanks all for your help (SET & SL)!
Kjell

--
------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64