SEO

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

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
[mailtogsql-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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

Dear Alvarao,
would you please so kind explaining me your opinion in details.

thanks,
-- Csaba


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org