Обсуждение: 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.
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.
On Thu, Dec 23, 2004 at 10:51:46AM +0100, Együd Csaba (Freemail) wrote: 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. I wonder why you are creating a table at all, when you could probably use a SRF instead in the queries where you are using such table. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La tristeza es un muro entre dos jardines" (Khalil Gibran)
Dear Alvarao, would you please so kind explaining me your opinion in details. thanks, -- Csaba -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alvaro Herrera Sent: Thursday, December 23, 2004 3:58 PM To: Együd Csaba (Freemail) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Very slow stored proc On Thu, Dec 23, 2004 at 10:51:46AM +0100, Együd Csaba (Freemail) wrote: 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. I wonder why you are creating a table at all, when you could probably use a SRF instead in the queries where you are using such table. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La tristeza es un muro entre dos jardines" (Khalil Gibran) ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings -- No virus found in this incoming 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 incoming 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. -- 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.
On Thu, Dec 23, 2004 at 09:48:56PM +0100, Együd Csaba (Freemail) wrote: Hi, > Dear Alvarao, Wow, now that's a strange misspelling. I've seen several but this one is new to me :-D > would you please so kind explaining me your opinion in details. What do you want that table for? If you are using it as input for a query, it's possible that you can forget about refilling it every now and then, and instead using a set-returning function (SRF) in the FROM clause of said query. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "I dream about dreams about dreams", sang the nightingale under the pale moon (Sandman)
Sorry for misspelling your name. It is almost midnight here... So Dear Alvaro, [is it ok? :)] This table is updated (not inserted... just updated) by another server in every minutes. The procedure creates a few (configurable number of) empty minutes in advance as a placeholder for the coming measured values. If there are no empty minute records the value is lost. If the db server is off for a long time, the minutes must be created backwards too to avoid the holes. This situation is where many thousands of records must be inserted at once. I hope it was clear. Cheers -- Csaba -----Original Message----- From: Alvaro Herrera [mailto:alvherre@dcc.uchile.cl] Sent: Thursday, December 23, 2004 11:12 PM To: Együd Csaba (Freemail) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Very slow stored proc On Thu, Dec 23, 2004 at 09:48:56PM +0100, Együd Csaba (Freemail) wrote: Hi, > Dear Alvarao, Wow, now that's a strange misspelling. I've seen several but this one is new to me :-D > would you please so kind explaining me your opinion in details. What do you want that table for? If you are using it as input for a query, it's possible that you can forget about refilling it every now and then, and instead using a set-returning function (SRF) in the FROM clause of said query. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "I dream about dreams about dreams", sang the nightingale under the pale moon (Sandman) -- No virus found in this incoming 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 incoming 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. -- 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.
On Thu, Dec 23, 2004 at 11:23:30PM +0100, Együd Csaba (Freemail) wrote: Együd, > Sorry for misspelling your name. It is almost midnight here... Ah, never mind! I don't really care that much about it ... > So Dear Alvaro, [is it ok? :)] Well, not completely, because it's really Álvaro, though I don't use that form because of issues with email headers (maybe I should); but I don't think it's that important anyway. > This table is updated (not inserted... just updated) by another server in > every minutes. The procedure creates a few (configurable number of) empty > minutes in advance as a placeholder for the coming measured values. If there > are no empty minute records the value is lost. > If the db server is off for a long time, the minutes must be created > backwards too to avoid the holes. This situation is where many thousands of > records must be inserted at once. Oh, certainly you can't do that with an SRF. It seems a weird strategy to me anyway. Not sure why you need the placeholders instead of just inserting the measured values, but what do I know of your situation ... > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.298 / Virus Database: 265.6.4 - Release Date: 2004.12.22. Huh, are you aware that your mail server insert several of these little trailers to each outgoing message? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Hoy es el primer día del resto de mi vida"