Very slow stored proc

From: Együd Csaba <csegyud(at)vnet(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: Very slow stored proc
Date: 2004-12-23 07:00:45
Message-ID: 0I9500JFUY56R7@mail.vnet.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nageshwar Rao 2004-12-23 07:11:29 bytea
Previous Message Jon Asher 2004-12-23 06:34:24 Basic problem installing TSearch2 (full text search)