Re: [SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

2007-09-05 Thread Marc Mamin 
Hello Dirk,


I have to disagree.

Your first update query is very low. It probably implies to run the sub
select statement for each row to be updated.

Following update statement is already much faster: (using UPDATE FROM)

   update test_table
  set mygroup= t.mygroup
   from test_table as t
   where t.family = test_table.family
   and t.rang = 1
   and table.rang=0
   -- perform the updte only when required
   and mygroup  t.mygroup;

But when you are dealing with  parent - child relations within a
single table as in my case, 
a single table scan with SELECT DISTINCT ON  and a row by row comparison
on the result set appears to be faster.

I tested both approaches on tables with ca. 14'000'000 rows where 25% of
them needed to be updated.

The above update statement run in 5H30' where my function did the job in
2H.
(as my tables are very large, much time is lost in i/o wait)



Cheers,

Marc



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

2007-09-04 Thread Dirk Jagdmann 
Hello Marc,

at first I tried to solve your update of the tables. The example you
gave should be done with an update statement like the following:

update test_table
   set mygroup=(select t.mygroup
  from test_table as t
 where t.family = test_table.family
   and t.rang = test_table.rang+1)
 where rang=0;

If you have to write a function which receives the tablename as an
argument it would look like:

CREATE OR REPLACE FUNCTION test_function(tablename text)
RETURNS integer AS $BODY$
 BEGIN
  EXECUTE 'update ' || tablename || '
   set mygroup=(select t.mygroup
  from ' || tablename || ' as t
 where t.family = test_table.family
   and t.rang = test_table.rang+1)
 where rang=0;'
  RETURN 0;
 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Generally you should avoid using explicit for/loop constructs in your
stored procedures if the action can be solved by a single SQL
statement, because the optimizer can make a better execution plan.

-- 
--- Dirk Jagdmann
 http://cubic.org/~doj
- http://llg.cubic.org

---(end of broadcast)---
TIP 6: explain analyze is your friend