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.
