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.
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) |