Re: Very slow stored proc - Mailing list pgsql-general

Hi,
I've got it. Not the date handling is slow but the string handling.
Eliminating the huge string buffer and running all the inserts row by row,
the overall running time is 12 sec.
So as a conclusion never use large strings in plpgsql functions.

Bye,
  -- Csaba
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Együd Csaba
Sent: Thursday, December 23, 2004 8:01 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Very slow stored proc

Hi,
I have a stored proc which is for filling 2 tables with empty rows in every
minutes. If the server has been stopped for more then 3 hours the insertion
takes too much.

I switched off the insert execution, and debugged the proc and realized that
the loop increasing the timestamp takes so long. In case of a 10 hour off
the proc takes 34 minutes to construct the query buffer. Only the buffer (a
string) without executing it. Please see below the loop I use. Could anybody
suggest me something how I can make it faster? I suppose the timestamp
incrementation could be slow or the date_part(?), but I'm not sure how to do
it in an alternate way.

Thank you very much,
  -- Csaba


----------------------------------------------------------------------------
------
-- iterating the meters - each meter will have one recored for every minute
for R in execute 'select * from meters' loop -- count=47
  LoopTime := FirstMin;
  -- iterating the minutes
  while LoopTime <= LastMin loop -- count=~590 minutes
    q := q || 'insert into measured_1 (tstamp, meterid, status)
values('||quote_literal(LoopTime)||'::timestamp,'||quote_literal(R.meterid)|
|','||quote_literal('00000')||');\n';

    if date_part('minute',LoopTime) in (0,15,30,45) then
      q := q || 'insert into measured_15 (tstamp, meterid, status)
values('||quote_literal(LoopTime)||'::timestamp,'||quote_literal(R.meterid)|
|','||quote_literal('00000')||');\n';
    end if;

    LoopTime := LoopTime + interval '1 minute';
  end loop;
end loop; -- so ~ 27700 loops - it takes more then 34 minutes
----------------------------------------------------------------------------
------



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.3 - Release Date: 2004.12.21.



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.3 - Release Date: 2004.12.21.


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.4 - Release Date: 2004.12.22.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.4 - Release Date: 2004.12.22.