Thread: For Loop using row_data to increase performance

Hi,

Here are sample stat:
 - Template_product has 1,033040 rows
 - template_all_in_one has 93,796,400 rows

I basically need to calculate the total for each article in
template_all_in_one and update it to Template_product.

What is the best way to improve the performance? I already have 7
indexes specify for the column.  My desktop has 4quad and 8 GB memory.
it only used up 1 GB memory. is it possible to increase the memory, so
the query will use more memory and end up faster performance?

===================================
CREATE OR REPLACE FUNCTION test_update_template_db()
  RETURNS integer AS
$BODY$
  DECLARE
     text_manipulation TEXT :='';
    row_data template_product%ROWTYPE;

  BEGIN

    FOR row_data IN SELECT * FROM template_product LOOP

       update template_product set total_all_in_one = (select count(*)
from template_all_in_one where template_article_name =
row_data.template_article_name)
       where template_product.id = row_data.id;

    END LOOP;

    RETURN 1;
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION test_update_template_db() OWNER TO postgres;
========================================

here is the update section which take very long to run. ==>328001.274
ms
"Index Scan using idx_template_product on template_product
(cost=2729612.82..2729621.18 rows=1 width=87) (actual
time=327643.885..327643.889 rows=1 loops=1)"
"  Index Cond: (id = 10566)"
"  InitPlan 1 (returns $0)"
"    ->  Aggregate  (cost=2729612.81..2729612.82 rows=1 width=0)
(actual time=327643.769..327643.769 rows=1 loops=1)"
"          ->  Bitmap Heap Scan on template_all_in_one
(cost=604031.87..2693572.99 rows=14415929 width=0) (actual
time=94229.880..324091.136 rows=14753612 loops=1)"
"                Recheck Cond: (template_article_name =
'aname'::text)"
"                ->  Bitmap Index Scan on
idx_template_article_name_all_in_one  (cost=0.00..600427.89
rows=14415929 width=0) (actual time=94209.983..94209.983 rows=14753617
loops=1)"
"                      Index Cond: (template_article_name =
'aname'::text)"
"Total runtime: 328001.274 ms"

On 5 Mar 2010, at 11:28, John777 wrote:

> Hi,
>
> Here are sample stat:
> - Template_product has 1,033040 rows
> - template_all_in_one has 93,796,400 rows
>
> I basically need to calculate the total for each article in
> template_all_in_one and update it to Template_product.
>
> What is the best way to improve the performance? I already have 7
> indexes specify for the column.  My desktop has 4quad and 8 GB memory.
> it only used up 1 GB memory. is it possible to increase the memory, so
> the query will use more memory and end up faster performance?
>
> ===================================
> CREATE OR REPLACE FUNCTION test_update_template_db()
>  RETURNS integer AS
> $BODY$
>  DECLARE
>     text_manipulation TEXT :='';
>    row_data template_product%ROWTYPE;
>
>  BEGIN
>
>    FOR row_data IN SELECT * FROM template_product LOOP
>
>       update template_product set total_all_in_one = (select count(*)
> from template_all_in_one where template_article_name =
> row_data.template_article_name)
>       where template_product.id = row_data.id;
>
>    END LOOP;
>
>    RETURN 1;
>  END;
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE
>  COST 100;
> ALTER FUNCTION test_update_template_db() OWNER TO postgres;
> ========================================


I'd say drop the stored procedure and use SQL, something like:

UPDATE template_product SET total_all_in_one = p.total
  FROM (
    SELECT template_article_name, COUNT(*) AS total
      FROM template_product
     GROUP BY template_article_name
  ) AS p
 WHERE template_product.template_article_name = p.template_article_name;

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b91809f296922908710608!