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.ri...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
