My Slow query.

["Jason" <jason.leach@xxxxxxxxx>]

Hi:

I have a query that uses one table with about 1 million rows. So far it
has been running for about 12h on a P4 3Ghz. The query/function does
this:

Esentially flattens some data. It looks through the values in a row,
does an if/else to categorize the value and update another table based
on the if/else.  I put it into a function. The function is at the end
of this msg.

So I have a talble that looks like:

SPECIES_ORDER   speciecCode  speciesPercent
1               PL           10
1               P            30
2               Sp           11

And I turn it into

speciesCode1   speciesPercent1  speciesCode2  speciesPercent2
PL             10               Sp            11
P              Sp               NULL          NULL

So the 1,2.. in the colum names comes from the ORDER_NUMBER.

I might be able to use the contributed crosstab function. Any ideas or
comments?

Thanks,
Jason.

DECLARE
    r RECORD;

BEGIN

FOR r IN SELECT * FROM RDD010 LOOP
  IF r."SPECIES_ORDER" = 1 THEN
    UPDATE public.RES_layers
    Set "speciesCode1" = r."speciesCode", "speciesPercent1" =
r."speciesPercent"
    where public.RES_layers."FOREST_COVER_LAYER_ID" =
r."FOREST_COVER_LAYER_ID";
  ELSIF r."SPECIES_ORDER" = 2 THEN
    UPDATE public.RES_layers
    Set "speciesCode2" = r."speciesCode", "speciesPercent2" =
r."speciesPercent",
     "averageAge_spp2" = r."averageAge", "averageHeight_spp2" =
r."averageHeight"
    where public.RES_layers."FOREST_COVER_LAYER_ID" =
r."FOREST_COVER_LAYER_ID";
  ELSIF r."SPECIES_ORDER" = 3 THEN
    UPDATE public.RES_layers
    Set "speciesCode3" = r."speciesCode", "speciesPercent3" =
r."speciesPercent"
    where public.RES_layers."FOREST_COVER_LAYER_ID" =
r."FOREST_COVER_LAYER_ID";
  ELSIF r."SPECIES_ORDER" = 4 THEN
    UPDATE public.RES_layers
    Set "speciesCode4" = r."speciesCode", "speciesPercent4" =
r."speciesPercent"
    where public.RES_layers."FOREST_COVER_LAYER_ID" =
r."FOREST_COVER_LAYER_ID";
  ELSIF r."SPECIES_ORDER" = 5 THEN
    UPDATE public.RES_layers
    Set "speciesCode5" = r."speciesCode", "speciesPercent5" =
r."speciesPercent"
    where public.RES_layers."FOREST_COVER_LAYER_ID" =
r."FOREST_COVER_LAYER_ID";
  END IF;
END LOOP;

RETURN 0;

end;


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org