Leyne, Sean skriver:
>
> Kjell,
>
> > I have a table "LargeTable" with ~170 million records.
>
> That's a "reasonable" sized table! ;-)
>
Well, I guess so, but it's large enough to cause severe problems
regarding execution time in many cases.
> > I need to update a single column on a "small" subset (maybe a
> million records or so),
> > but the subset selection requires references to other tables.
>
> A difficult problem.
> How many relates tables are used?
> Would the related tables be first level or second level joins?
> How many entries are there in the tables?
> Are the references FK values?
> How many total unique FKs values would the criteria resolve to?
> Do you have indexes on the update table for the FKs?
>
I guess the best overall answer I can give you is the query and plan for
a select that picks up the PK of the table that I want to update:
select LrgTbl."ECO_ID"
from "LargeTable" LrgTbl
inner join "Uppgiftshållare" UH on UH."ECO_ID" = LrgTbl."Hållare"
inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = UH."Hanterare"
where LrgTbl."ÄrNull" = 0
and UH."Propertynamn" in ('This', 'That', 'Other', 'Extra')
and Uhant."ColX" = 'Whatever';
PLAN JOIN (UHANT INDEX (IX_ColX), UH INDEX (IX_UH_Uhant), LARGETABLE
INDEX (IX_LrgTbl_UH))
Selectivity of IX_ColX = 0.125000
Selectivity of IX_UH_Uhant = 0.000000
Selectivity of IX_LrgTbl_UH = 0.000000
There are no FK:s because the schema is generated by an O/R mapper that
allows circular refs, which would be problematic with FK:s in place. But
all "candidate FK:s" are indexed, as above.
UH contains around 150 million records and UHANT about 10-20 million I
think (I didn't check).
> > Unfortunately, Firebird does not support joins in update or delete
> queries,
> > resulting in natural scan of "LargeTable" and consequently 170 million
> > executions of each correlated subquery (required to form my desired
> > selection).
> >
> > So, is there a better way?
>
> Depends on how you define "better".
>
> At the moment, the best scenario I can see is that you still have a
> natural scan. But that the "cost" of the related tables references
> would be minimized.
>
> > Note: I tried a "straightforward" update with correlated subqueries.
> It took 17
> > hours. I think a natural scan takes about an hour...
>
> I would hope that the new approach would takes 2X natural scan.
>
> How would that performance be?
>
What new approach are you referring to?
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. It did 2522142 updates.
Regards,
Kjell
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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/
