Re: For Loop using row_data to increase performance

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!